Make this query safe? [duplicate]

This question already has answers here: Closed 12 years ago.

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 ass开发者_开发百科ume 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());
}

---

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.

---

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.

---

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.

---

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.