An SQL injection can occur within any portion of any SQL Query.
Inserts, Deletes, Updates, and Selects any query that contains user-input is susceptible to SQL Injection.
Even undefined variables in some cases where Register Globals is enabled on the server.
There’s a couple of important aspects that one must consider when writing SQL interfaces in PHP, and when done properly in every query, will guarantee your safety against SQL Injection, which as we’ve seen, is the most common and easiest attack that can be performed on a given site.
First, when grabbing user input, always check to ensure the variable is set before proceeding...
PHP Code:
$variable = (isset($_POST['variable'])) ? $_POST['variable'] : '';
Whereas using
PHP Code:
$variable = $_POST['variable'];
will throw a PHP Warning of undefined index if the variable is unset. -- This is just good practice and does not necessarily improve security unless display errors is turned on in your php.ini file.
If the variable is not set and you are using it in a where clause or inserting it into the database, you might want to consider bypassing the query if the variable is empty.
For example:
PHP Code:
function post_var($value, $default = '')
{
return isset($_POST[$value]) ? $_POST[$value] : $default;
}
function sql_clean($value)
{
return mysql_real_escape_string((string) $value);
}
$value1 = sql_clean(post_var('value1'));
$value2 = sql_clean(post_var('value2'));
$value3 = intval(post_var('value3'));
$sql = "SELECT *
FROM my_table
WHERE field1 = '$value1' AND field2 = '$value2' AND field3 = $value3";
$result = mysql_query($sql);
If we just run that query, we may be querying blank values. Now although the above is protected from SQL Injection by using mysql_real_escape_string in the string input variables, single quotes on string input, and intval type-casting in the user-input integers, we will have an SQL error, which in some cases, may display to the user.
Never do you want to display these errors to the user -- unless you are specifically debugging. Hackers typically look for invalid inputs, header 500 errors, SQL errors and others to determine where vulnerabilities lie.
Therefore it is good practice to ensure the the data is present before running the query
PHP Code:
if (isset($_POST['submit']) && $value1 && $value2 && $value3)
{
$sql = // now run the query...
}
On a side note, single quotes is preferred to double quotes.
Single quotes do not process variables within the quotes and therefore have a 46% speed performance advantage over using double quotes.
The exception, of course, is when you want to use double quotes to process variables within the string as demonstrated above.
One other note I would like to point out is that using is_numeric() as a method of sanitisation is not a sufficient check in many cases.
for example, will pass as a valid is_numeric variable.
However, this is not an SQL Injection concern, as it would be impossible to pass malicious code, but you will end up with an SQL Error if your database field is expecting an integer. (non WHERE clause)
In summary, use mysql_real_escape_string() on user input strings along with ensuring you use single quotes (back-ticks are not useful in these cases), and forcing integer or float/double on numeric inputs.
Always double check to ensure you are sanitising your variables properly and that even when invalid user-input is entered.