4

I know the right way to sanitize sql strings in Perl is to use a prepared statement, however, this particular Perl script is generating statements to be executed later in a different environment, not Perl. It has no database to connect to.

How can I safely escape a string for insertion into a MySQL query. The solution doesn't have to be portable.

Community
  • 1
  • 1
spraff
  • 32,570
  • 22
  • 121
  • 229
  • 6
    Don't escape SQL. Use place holders and param binding, even if you're executing it later. – Brian Roach Feb 14 '12 at 16:04
  • 3
    Just make sure none of your strings contain the word "Bobby tables" – DVK Feb 14 '12 at 16:05
  • What environment will they be executed in? – Ilion Feb 14 '12 at 16:46
  • 2
    @BrianRoach is right. Prepare using place holders and store for later, along with the bound params. If you are serializing in some manner for shipping to some other system, the bound param list will save just as well as if it's part of the query. – kbenson Feb 14 '12 at 17:41

2 Answers2

2

Unfortunately the quoting function used by DBD::mysql, and the MySQL client library in general, requires an active database handle. According to the documentation, "this is needed because the escaping depends on the character set in use by the server".

I can think of a few hacky solutions, but none of them are really satisfying, so let's work with this from the docs:

Characters encoded are “\”, “'”, “"”, NUL (ASCII 0), “\n”, “\r”, and Control+Z. Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped.

This suggests that you can probably get away with a quoting function that does either

s/([\\"'])/\\$1/g;

or

s/([\\"'\0\n\r\cZ])/\\$1/g;

although I would still be wary.

hobbs
  • 223,387
  • 19
  • 210
  • 288
-4

You could just check for special chars in the variables you add to your query string that are required to do an SQL-Injection such as ";" or brackets and replace them or throw them out?!?

Sebastian
  • 429
  • 2
  • 8
  • 17
  • 4
    Nice sentence, but it needs 'and end on the daily wtf' at the end. – Konerak Feb 14 '12 at 16:05
  • Maybe you could be a bit more precise what's your problem Konerak? – Sebastian Feb 14 '12 at 16:12
  • 4
    Unless you **really** know what you are doing, or have a nicely restricted set of allowed characters, writing your own MySQL escape function is really recommended against. You'll forget something, there will be a control character that allows an attacked to bypass your quote, and you'll end up on the aforementioned site. This is bad advise, when carelessly given like that. (the `?!?` at the end don't do much good either, but I tried focussing on content, not form)... – Konerak Feb 14 '12 at 16:15
  • Rather than checking for special characters to throw out, you could check for acceptable characters to keep. E.g. a-zA-Z0-9. Depends on the data, of course. – runrig Feb 14 '12 at 23:09
  • It doesn't happen often that the whitelisting is acceptable, the very problematic quote character (') is part of many people name for example. – Bruno Rohée Jun 16 '14 at 14:25