How should I write PHP $_POST vars in a mysql_query function?

Question

In accessing my database, I have the user fill out a form, and in the target page, the posted values are used in the resulting MySQL query.

$query = mysql_query("SELECT pass FROM database WHERE user='$_POST[user]'");

However, for some reason or another, MySQL doesn't like my using a $_POST variable in the command, and it only works if I define (for example) $user = $_POST['user'];, and then put $user directly in the SQL command.

On the other hand, I can use $_POST values in INSERT statements where specific column names are not required:

$query = mysql_query("INSERT INTO database VALUES ('foo', 'bar', '$_POST[user]'");

If I try an INSERT statement where attributes are defined (e.g. user='foo'), then the same problem appears.

What am I doing wrong in my SQL query that causes the command to error out when run, but works with the specific method of formatting an INSERT command?

Hopefully, it's not "tough luck, looks like you have to assign all of your posted values". Heh.

Solution 1

First of, watch out for SQL Injections!

Now, to answer your question try doing this instead:

$query = mysql_query("SELECT `pass` FROM `database` WHERE `user` LIKE '" . mysql_escape_string($_POST['user']) . "';");

You were doing a couple of things wrong:

• using the = operator instead of LIKE operator
• not enclosing the value in the SQL query with '
• not enclosing the user index in the $_POST array with '

PS: You should use mysql_real_escape_string() instead of mysql_escape_string()!

Solution 2

You're simply inserting a variable into a string, so it shouldn't matter which command you're putting it into.

There are a few issues to point out.

One, you might want to use the {} format for array variables. You don't use quotes around the arrray key names in this format.

$query = mysql_query("SELECT pass FROM database WHERE user='{$_POST[user]}'")

Two, you'd never want to make a query like that because you are open to sql injection holes. Consider, what if $_POST['user'] was "cow';drop table database;--"?

You must either run mysql_real_escape_string on the POST input before putting it into your query, or check out using PHP PDO with prepared statements.

One way to do format your string which provides a bit of structure is to use sprintf.

$query=mysql_query(sprintf("SELECT pass FROM database WHERE user='%s'",mysql_real_escape_string($_POST['user'])));

Solution 3

1. Use PDO - it provides much better API to communicate with DB.
2. If you're using mysql_*() functions always remember to filter (mysql_real_escape_string()) any data that comes from untrusted source (like user)

3. Pay more attention to how your code looks like. Just compare the following listings:

   $query = mysql_query("INSERT INTO database VALUES ('foo', 'bar', " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ")");
   
   $query = sprinf('INSERT INTO database VALUES ("foo", "bar", "%s", "%s", "%s")',
   mysql_real_escape(...), ...);
   

   Do I have to explain which one is better to read, modify or understand?

Solution 4

Why not check and see what mysql_error() has to say about it? If your query is invalid, mysql_error() will return a nice blob of text telling you exactly what went wrong.

As for MySQL not liking the POST var if you insert it directly for some runs, but not others, then you should make sure you're using consistent data and setups for each test. If some test are done using a GET, then your POST vars will be empty. If you're using different user names for each test, then see if what's consistent between the ones that fail.

And as mentioned above, read up about SQL injection and how your query is just begging to be subverted by a malicious user.

View more solutions