Make this query safe?

Question

Possible Duplicate:
Best way to stop SQL Injection in PHP

I have seen some of examples that use something called a PDO to make a query safe from sql-infection, or others that use real_escape, but they all seem to be incomplete or assume some knowledge. So I ask, take this simple update query and make it safe from sql-injection.

function updateUserName($id,$first,$last)
{
    $qry = 'UPDATE user SET first = "'.$first.'", last = "'.$last.'" WHERE id = '.$id;
    mysql_query($qry) or die(mysql_error());
}

Answer 1

You could wrap all your variables in mysql_real_escape_string(). PDO (PHP Data Objects) is a better solution though, if it's available in your environment. You can find these docs here.

PDO will make your code more Object-Oriented and automate some of these tasks for you. Some good sample code of PDO preparing statements can be found deeper into the docs here.

Answer 2

This is the better query:

$qry = 'UPDATE user SET first = "'.mysql_real_escape_string($first).'", last = "'.mysql_real_escape_string($last).'" WHERE id = '.intval($id);

Use mysql_real_escape_string for strings and intval for numbers in your queries to make them safer.

Answer 3

Basically, you have to :

• escape string, with mysql_real_escape_string
• make sure integer really are integers ; for instance, using intval.

Which, in your specific case, would give something like this :

$qry = 'UPDATE user SET first = "'
    . mysql_real_escape_string($first)
    . ' ", last = "'
    . mysql_real_escape_string($last)
    . '" WHERE id = '
    . intval($id);

Of course, this is considering that last and first are varchar, and that id is an integer.

As a sidenote : when an SQL error (this is also true for whatever kind of error you can thing about) occurs, you should not display a technical error message and just let the script die.

Your users will not understand that technical error message -- they won't know what to do with it, and it's not their problem.

Instead, you should log (to a file, for instance) that technical error message, for your own usage ; and display a nice "oops an error occured" page to the user.

Answer 4

mysql_real_escape_string + sprintf

$qry = sprintf('UPDATE user SET first = '%s', last = '%s' WHERE id = %d', mysql_real_escape_string($first), mysql_real_escape_string($last), $id);

I like it that way.