It is good practice (common sense?) to filter your SQL queries.  One way to accomplish this in PHP is to utilize a function like sprintf() which will format a given string and integrate values into the string using conversion specifications which are passed in as arguments to the function.  In plain english, that means you can call the function, pass in a value and require that value to be a integer, for example.  If the value you passed in is a string, roughly speaking, it will sanitize your output.

An example in a MySQL query would be this.

<?php
// build our sql string.
$sql = "SELECT * FROM table WHERE field=%d";
$sqlf = sprintf( $sql, $somevalue );
$db->query($sqlf);
?>

As you can see, you can designate where the substitution will take place in the $sql string. That’s easy. But what happens if you need to use MySQL’s DATE_FORMAT() function? It requires that you pass in arguments to define its output (ie. Day as a word, day as a date, month as a number, etc).

<?php
// build our sql string.
$sql = "SELECT DATE_FORMAT( %b %M %d %Y, some_date_field ) as myDate FROM table WHERE field=%d";
$sqlf = sprintf( $sql, $somevalue );
$db->query($sqlf);
?>

This will fail. sprintf() will complain because you haven’t passed in enough arguments. It is expecting 5 values as part of the call, instead of just the one that you are trying to replace (in the SQL WHERE clause).

So what’s the solution? You have to “comment-out” the % that aren’t part of your sprintf() substitution. You can do this by putting another % in front of the ‘%’ symbols in the DATE_FORMAT() function. This deems them as a literal percent-sign instead of the start of another sprintf() “variable”.

<?php
// build our sql string.
$sql = "SELECT DATE_FORMAT( %%b %%M %%d %%Y, some_date_field ) as myDate FROM table WHERE field=%d";
$sqlf = sprintf( $sql, $somevalue );
$db->query($sqlf);

Hope that helps!