PHP/MySQL - Best use and practice of escaping strings

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: '**!"#!#^!"#!"*#!"#^''''

`mysql_*` funtions are about to be deprecated. Use [PDO](http://php.net/manual/en/book.pdo.php) or [MySqli](http://php.net/manual/en/book.mysqli.php). — Lion, Jul 13 '12 at 21:34

score 7 · Accepted Answer · answered Jul 13 '12 at 21:35

7

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.

answered Jul 13 '12 at 21:35

Kylotan

18,290
7
46
74

And how do you make a parameterized query? – Oskar Persson Jul 13 '12 at 21:38
Parameterized query using [PDO](http://php.net/manual/en/book.pdo.php). – Lion Jul 13 '12 at 21:39
Look at the connections section of the link Lion posted. – Kylotan Jul 13 '12 at 21:49

Mark Byers · Answer 2 · 2012-07-13T21:40:55.523

6

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.

edited Jul 13 '12 at 21:40

answered Jul 13 '12 at 21:35

Mark Byers

811,555
193
1,581
1,452

`table` was actually ok as it represents the table name... (`username` is the field name) – Shomz Jul 13 '12 at 21:37
Thanks for the comment. I've added that to the list of things that need to be fixed in that line of code. – Mark Byers Jul 13 '12 at 21:45
Nice work, can't give you another +1 :) – Shomz Jul 13 '12 at 21:54
Check edited first post. – Oskar Persson Jul 13 '12 at 22:04

PHP/MySQL - Best use and practice of escaping strings

2 Answers2