PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

Escaping MySQL strings with no connection available

Note: This article was originally published at Planet PHP on 19 April 2011.
Planet PHP

We're all being drilled over and over again to always use mysqli::escape_string, PDO::quote, or preferably prepared statements when escaping user-supplied strings for use in MySQL queries.

The downside to these methods is that they only work when there's an open connection to a server. So what if there's no connection available? In traditional Unix philosophy I'm writing an export script that doesn't execute SQL statements right to a server, but sends them to stdout. Forcing people to make a connection seems like a major inconvenience.

So what's left? Manual escaping I suppose.. The manual page for mysqli::escape_string mentions: Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z..

  1. function dontHateMe($unescaped) {
  2. $replacements = array(
  3. "\x00"='\x00',
  4. "\n"='\n',
  5. "\r"='\r',
  6. "\\"='\\\\',
  7. "'"="\'",
  8. '"'='\"',
  9. "\x1a"='\x1a'
  10. );
  11. return strtr($unescaped,$replacements);
  12. }

There's a risk though.. Certain multi-byte character sets (such as BIG5 and GBK) may still allow for a security hole. You *should* be fine with UTF-8, so make sure you start your file with:

  1. SET CHARACTER SET utf8;

Still no guarantee from my side though. Tread carefully and avoid this if you can. If you have a better idea, or you feel like shouting at me for this.. let's hear it in the comments.

Special thanks to Spudley for providing me with a reasonable answer to this question.