Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
2  
mysql_* funtions are about to be deprecated. Use PDO or MySqli. – Lion Jul 13 '12 at 21:34

marked as duplicate by Conrad Frix, Evan Mulawski, GDP, Ben, Jason Sturges Jul 16 '12 at 2:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
And how do you make a parameterized query? – Oskwish Jul 13 '12 at 21:38
Parameterized query using PDO. – Lion Jul 13 '12 at 21:39
...using mySQL. – Oskwish Jul 13 '12 at 21:44
Look at the connections section of the link Lion posted. – Kylotan Jul 13 '12 at 21:49
up vote 3 down vote

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.

share|improve this answer
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. – Oskwish Jul 13 '12 at 22:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.