2010-10-20

Escaping square brackets in SQL Server queries from PHP

I have a PHP web app that pulls data from an SQL Server database using the ODBC functions. Earlier I come up with a PHP function to escape single quotes in user input by adding a single quote to each single quote (a single quote is the character you use to escape a single quote) as defense against (inadvertent) SQL injection (the web app is behind a firewall).

The other day I accidentally discovered that including text inside a pair of square brackets [like this] in user input that was added to a LIKE clause of a WHERE clause resulted in a huge data dump being returned by SQL Server. I did some Googling and discovered that characters enclosed in square brackets have some special meaning in SQL Server (I don't remember what it was). I first tried updating my PHP function to escape square brackets with single quotes, but that didn't work for some reason. Then I did some more research and discovered that the way to escape a square bracket in SQL Server is to enclose it in square brackets like this [[]. So I updated my PHP function to do this on user input and it worked to stop the data dumps when a user included something like [fred] in their input.

Writing the PHP to do this was tricky because if you just do a straight str_replace on each square bracket the second replace replaces some of the square brackets you added with the first replace and messes it all up. The way I solved this was to write my function to:

  • first replace the left square bracket with an arbitrary three character string that is unlikely to be in user input,
  • then replace on the right square bracket with []],
  • then do a third replace of my arbitrary three character string with [[].
And yes, I know I should be using stored procedures etc, and hackers can get past any escaping routine, etc, but this app is behind a firewall and I am only concerned about accidental SQL injection.