PHP / MySQL - Best Use and Practice of String Escaping

Question

Possible duplicate:
Best way to prevent SQL injection in PHP

What is the best way to avoid strings when creating a query? mysql_real_escape_string () seems nice, but I don’t know exactly how to use it correctly.

Does this code work correctly?

<?php /* Let say that the user types "'#""#''"\{(})#&/\€ in a textfield */ $newStr = mysql_real_escape_string($str); $query = "INSERT INTO table username VALUES ($str)"; mysql_query($query); ?> 

EDIT:

Now I have this code:

  $email = $_POST['email']; $displayName = $_POST['displayName']; $pass = $_POST['pass1']; $email = mysqli_real_escape_string($link, $email); $displayName = mysqli_real_escape_string($link, $displayName); $pass = mysqli_real_escape_string($link, $pass); $insert = "INSERT INTO profiles (email, displayName, password) VALUES ('$email', '$displayName', md5('$pass'))"; mysqli_query($link, $insert) or die(mysqli_error($link)); 

But I get this error: You have an error in the SQL syntax; check the manual for your version of MySQL server for the correct syntax to use next to "!" #! # ^! "#!" #! "# ^ '' '' '' on line 1

If the user enters: '**! "#! # ^!" #! "* #!" # ^ '' ''

Answer 1

The best way is not to avoid the string at all, but instead use a parameterized query that does this behind the scenes.

Answer 2

Using mysql_real_escape_string how this will work, but you need:

• Add quotation marks around the value.
• Use the result of $newStr , not the original value of $str .
• Change table_name to a name that is not a reserved keyword.
• Add parentheses around the column list.

Try the following:

 $query = "INSERT INTO yourtable (username) VALUES ('$newStr')"; 

I also suggest checking the result of mysql_query($query) , and if there is an error, you can check the error message:

 if (!mysql_query($query)) { trigger_error(mysql_error()); } 

You should also consider using one of the new MySQL interfaces. Old mysql_* functions are deprecated and should not be used in new code.