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!

I had a situation where I needed to validate an email address that included an apostrophe. It is not widely known that the apostrophe (and a bunch of other symbols for that matter) are valid characters in the official RFC2822 specification for email address formats.

Anyway, I kept getting an error when I tried to add the apostrophe to my character classes in my regex. It gave me a strange error referencing REG_ERANGE. After some googling, I came across this blog post which led me to the answer. The problem is related to the placement of the dash (”-”) character in the regex.

Example 1:

if (ereg("[^a-zA-Z0-9_-.]", $userid)) {
    echo 'bad';
}
else {
    echo 'good';
}

The problem? The dash, or hyphen, being before the period. It thinks it’s a range, like you see in a-z. This may not be a bug, per se, but it’s certainly not smart enough for me.

The solution? Simply put the dash at the end of the regex.

Example 2:

if (ereg("[^a-zA-Z0-9_.-]", $userid)) {
    echo 'bad';
}
else {
    echo 'good';
}