PHP: sprintf and %
sprintf is very often used in conjunction with mysql_real_escape_string to create secure MySQL queries with user embedded user input. I myself use this technique all the time and have found a very good and comfortable solution to work with.
The other day I stumbled across an interesting problem. Every now and then you tend to write MySQL queries with the ‘LIKE’ command:
mysql_query("SELECT field FROM table WHERE field LIKE '%keyword%'");
With the sprintf this would translate into:
$sKeyWord = 'keyword';
mysql_query(sprintf("SELECT field FROM table WHERE field LIKE '%%s%'", array($sKeyWord));
The code written above wouldn’t work. The ‘%’ is sprintf’s indicator that ‘here comes something that I should replace with something else’. It makes sense that my piece of code above would fail since I have 3x% and only one literal to describe the type of input.
I’d never run into this before so I opened the PHP manual to look for the proper syntax to tell sprintf that I actually want the ‘%’ to be interpreted as a ‘%’ and nothing else. Would you believe I didn’t find an answer? Well, I didn’t.
When PHP.net failed me I went to Google and came up empty once again. I figured it was a bit weird but I began wokring on the last resort for developers: trial & error.
After a lot of frustration and soul searching I found the answer to my question and, of course, it was so simple that it took me some time to think of it:
$sKeyWord = 'keyword';
mysql_query(sprintf("SELECT field FROM table WHERE field LIKE '%%%s%%'", array($sKeyWord));
The solution is to type the ‘%’ twice such as ‘%%’ so the snippet above did the trick. Hopefully this post might save someone else some time, or maybe I’m just that stupid, I don’t know.
PS. The answer to the problem can be found on PHP.net but only if you look hard enough and know what you’re looking for, I didn’t so I missed it.
