PHP/MySQL - Best use and practice of escaping strings [duplicate]

Question

Possible Duplicate:
Best way to prevent SQL Injection in PHP

What is the best way to escape strings when making a query? mysql_real_escape_string() seems good but I do not exactly know how to use it in properly.

Does this code do the job properly?

<?php
   /* Let's 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 your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '!"#!#^!"#!"#!"#^'''''' at line 1

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

Answer 1

The best way is not to escape the string at all, but instead use a parameterized query, which does it for you behind the scenes.

Answer 2

Using mysql_real_escape_string like that will work, but you need to:

• Add quotes around the value.
• Use the result $newStr, not the original value $str.
• Change the tablename to a name that isn't a reserved keyword.
• Add parentheses around the column list.

Try this:

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

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

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

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