I was going to do the whole Valley Girl theme for this post, but decided that was lame.

So while converting an internal application query into a user-input web query when I stumbled across a problem. My prepared statement was not throwing an error and not giving me results. It took me a while to find the answer, so I thought I’d reiterate it here and provide a demonstration using PHP PDO.

Here is the original unsecure query (works fine):

$query = "SELECT city,zipcode
        FROM zipcodes
        WHERE zipcode
        LIKE '%".$_GET['query']."%'"
        ."
        ORDER BY city ASC
        LIMIT 1000";

Here is the error prepared statement:

$query = "SELECT city,zipcode
        FROM zipcodes
        WHERE zipcode"
        ." LIKE '%?%' "
        ."
        ORDER BY city ASC
        LIMIT 1000";
$sth = $dbh->prepare($query);
$sth->execute(array($_GET['query']));
$result = $sth->fetchAll();

Seems reasonable enough, yet alas… no results or errors or exceptions. What happened? Using the mysql cli, I did some debugging:

mysql> PREPARE STMT FROM
    -> "SELECT city,zipcode
    ">         FROM zipcodes
    ">         WHERE zipcode LIKE '%?%'
    ">         ORDER BY city ASC
    ">         LIMIT 1000"
    -> ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @param1 := '9212';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt USING @param1;
ERROR 1210 (HY000): Incorrect arguments to EXECUTE

Huh, that sucked. So my brain said “let me google that for you” and I acquiesced. I found the answer on a postgres forum from this post.

Here is the correct prepared statement:

$query = "SELECT city,zipcode
        FROM zipcodes
        WHERE zipcode"
        ." LIKE ?"
        ."
        ORDER BY city ASC
        LIMIT 1000";
$sth = $dbh->prepare($query);
$sth->execute(array("%".$_GET['query']."%"));
$result = $sth->fetchAll();

Notice that the key is to use a plain ? in the query body for the prepared statement, then pass your parameter wrapped in ‘% %’.