Avoiding SQL injection in a user-generated SQL-regex

Question

Hi. I'm creating a site where the user unfortunately has to provide a regex to be used in a MySQL WHERE clause. And of course I have to validate the user input to prevent SQL injection. The site is made in PHP, and I use the following regex to check my regex:

/^([^\\\\\']|\\\.)*$/

This is double-escaped because of PHP's way of handling regexes. The way it's supposed to work is to only match safe regexps, without unescaped single quotes. But being mostly self-taught, i'd like to know if this is a safe way of doing it.

Answer 1

If it is anly for the purposes of display this reg expression then most programs simply Html Encode the value and store in the DB and then the Decode on the way out. Again only for Display purposes though, if you need to use the reg exp that is submitted this won't work.

Also know there is a method where the person intent on injecting writes out there SQL, Converts it to varbinary and submits the exec command with the base 64 representation of the query which I have been hit with in the past.

Answer 2

You should just pass the string through mysql_escape_string or mysql_real_escape_string.

I'd be wary of accepting any old regex though - some of them can run for a long time and will tie up your DB server.

From Pattern Syntax:

Beware of patterns that contain nested indefinite repeats. These can take a long time to run when applied to a string that does not match. Consider the pattern fragment (a+)*

This can match "aaaa" in 33 different ways, and this number increases very rapidly as the string gets longer.

Answer 3

If you use prepared statements, SQL injection will be impossible. You should always use prepared statements.

Roborg makes an excellent point though about expensive regexes.