SQL Injection

SQL injection attacks are extremely simple to defend against, but many applications are still vulnerable. Consider the
following SQL statement:

--------------------------------------------------------------------------------
$sql = "INSERT
INTO users (reg_username,
reg_password,
reg_email)
VALUES ('{$_POST['reg_username']}',
'$reg_password',
'{$_POST['reg_email']}')";
-------------------------------------------------------------------------------

This query is constructed with $_POST, which should immediately look suspicious.
Assume that this query is creating a new account. The user provides a desired username and an email address. The registration application generates a temporary password and emails it to the user to verify the email address. Imagine that
the user enters the following as a username:

bad_guy', 'mypass', ''), ('good_guy

This certainly doesn't look like a valid username, but with no data filtering in place, the application can't tell. If a valid email address is given (shiflett@php.net, for example), and 1234 is what the application generates for the password, the SQL statement becomes the following:

--------------------------------------------------------------------------------
$sql = "INSERT
INTO users (reg_username,
reg_password,
reg_email)
VALUES ('bad_guy', 'mypass', ''), ('good_guy',
'1234',
'shiflett@php.net')";
?>
--------------------------------------------------------------------------------

Rather than the intended action of creating a single account (good_guy) with a valid email address, the application has been tricked into creating two accounts, and the user supplied every detail of the bad_guy account. While this particular example might not seem so harmful, it should be clear that worse things could happen once an attacker can make modifications to your SQL statements.

For example, depending on the database you are using, it might be possible to send multiple queries to the database server in a single call. Thus, a user can potentially terminate the existing query with a semicolon and follow this with a query of the user's choosing.

MySQL, until recently, does not allow multiple queries, so this particular risk is mitigated. Newer versions of MySQL allow multiple queries, but the corresponding PHP extension (ext/mysqli) requires that you use a separate function if you want
to send multiple queries (mysqli_multi_query() instead of mysqli_query()). Only allowing a single query is safer, because it limits what an attacker can potentially do.


Filter your data.
This cannot be overstressed. With good data filtering in place, most security concerns are mitigated, and some are
practically eliminated.

Quote your data.
If your database allows it (MySQL does), put single quotes around all values in your SQL statements, regardless of
the data type.

Escape your data.
Sometimes valid data can unintentionally interfere with the format of the SQL statement itself. Use
mysql_escape_string() or an escaping function native to your particular database. If there isn't a specific one,
addslashes() is a good last resort.