sprintf and % signs in text

Question

A problem I recently ran into was that when trying to update a field in my database using this code would not work. I traced it back to having a % sign in the text being updated ($note, then $note_escaped)... Inserting it with sprintf worked fine though.

Should I not be using sprintf for updates, or should it be formed differently?

I did some searching but couldn't come up with anything.

$id = mysql_real_escape_string($id);
$note_escaped = mysql_real_escape_string($note);
$editedby = mysql_real_escape_string($author);
$editdate = mysql_real_escape_string($date);
//insert info from form into database
$query= sprintf("UPDATE notes_$suffix SET note='$note_escaped', editedby='$editedby', editdate='$editdate' WHERE id='$id' LIMIT 1");

Thanks much!

Answer 1

You can escape the % in the source text by replacing it with \% in mysql.

Answer 2

You are using sprintf totally wrong. Removing the function call in your code would still do the same thing. It should be:

sprintf("UPDATE notes_%s SET note='%s', editedby='%s', editdate='%s' WHERE id=%d LIMIT 1", $suffix, $note_escaped, $editedby, $editdate, $id);

You should read the manual.

Answer 3

From http://php.net/manual/en/function.mysql-real-escape-string.php:

Note: mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

You need to manually escape the % and _ if any with \% and _. I don't recommend using sprintf, but just improving your escape function.

Answer 4

sprintf() is not used much in PHP, unless you need to format data somehow. These two statements work identically in PHP:

$num = 42;
$char = 'q';

$text = sprintf('The number is %d and the character is %s', $num, $char);
$text = "The number is $num and the character is $char";

sprintf's used more in C for "printing" variable data into a string. But PHP can already do that with double-quoted strings, so unless you need to use sprintf's special formatting functions (e.g. %0.2f for a 2-decimal-place float), it's easier to use the regular string method.

Answer 5

first of all you should be using prepared statements instead of a sprintf-call

but if you absolutely have to do it this way you have to use:

$id = mysql_real_escape_string($id);
$note_escaped = mysql_real_escape_string($note);
$editedby = mysql_real_escape_string($author);
$editdate = mysql_real_escape_string($date);
//insert info from form into database
$query= sprintf("
  UPDATE notes_%s /* this is still open for injection, and cannot be properly escaped with mysql_real_escape_string */
  SET note='%s', 
  editedby='%s', 
  editdate='%s' 
  WHERE id='%d'
  LIMIT 1",
$suffix,
$note_escaped, $editedby, $editdate, $id);