Problem with mysql_query quoting of parameter values

Date: Sun Dec 28 2008
I had some code which wasn't working:

mysql_query("UPDATE pracs SET name='{$name}', email='{$email}', webaddr='{$webaddr}', phone='{$phone}', level='{$level}', lineages='{$lineages}', description='{$desc}', password='{$password}' WHERE id={$id}");

In debugging the code I found that the correct values were reaching this statement, but the database wasn't being updated with the new values. Huh?

Well, the official documentation at says

// Formulate Query
// This is the best way to perform a SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("SELECT firstname, lastname, address, age FROM friends WHERE firstname='%s' AND lastname='%s'",

// Perform Query
$result = mysql_query($query);

Weeelll... in double quoted strings the first is a way to cause values to be substituted in a string.

It's not entirely clear to me why it would not work however there is a danger which could occur. In SQL there are some tricky rules for quoting values in a command like "UPDATE table SET column='quoted value'". The SQL spec has specific rules about the presence of quoting in the quoted value and obviously mysql_real_escape_string will do the right thing, whereas simple string substitution like I did in the first command would not be correct.

Unfortunately I like the straightforwardness of the first version. But I like working code much better.

Another point to consider is, uh, SQL-injection attacks possible in the first version...