Error message

  • User warning: The following module is missing from the file system: module_missing_message_fixer. For information about how to fix this, see the documentation page. in _drupal_trigger_error_with_delayed_logging() (line 1172 of /var/www/davidherron.com/includes/bootstrap.inc).
  • Deprecated function: Methods with the same name as their class will not be constructors in a future version of PHP; GeSHi has a deprecated constructor in require_once() (line 915 of /var/www/davidherron.com/sites/all/modules/libraries/libraries.module).

Problem with mysql_query quoting of parameter values

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 http://www.php.net/manual/en/function.mysql-query.php 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'",
    mysql_real_escape_string($firstname),
    mysql_real_escape_string($lastname));

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

Weeelll... in double quoted strings http://www.php.net/manual/en/language.types.string.php 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... http://www.php.net/manual/en/function.mysql-real-escape-string.php