Escaping string to be quoted in Perl

Question

I'm writing a quick Perl script which takes user input and uses it as a MySQL regex. I'd like to ensure that I can pass it without any of the Bobby tables nonsense. In particular, I want to allow all kinds of special characters, while rejecting anything that could break out of the quote itself.

The code is something like this:

my $myRegex = join(' ', @ARGV);
# ...other code...
sendToSQL("select blah from foo where bar regexp '$myRegex'");

In particular, I need constructs like \(...\) and \. to remain -- I can't change them to \\\(...\\\) and \\.. It feels like this is simple enough it should exist already -- I'm trying to not reinvent wheels here.

The script receives input only from trusted users (one of three, including myself) over SSH, so 'in principle' I could just pass the string along unchecked. But I'd feel much better if it caught honest mistakes rather than inadvertently leaking partial commands to SQL with weird results. (This is not a security issue per se: the users who have access to the script can already run mySQL directly.)

Edit: The proposed duplicate is only slightly related to my issue and certainly not a duplicate of it. I'm not using DBI and my concern is not injection attacks -- the concern is that a user not inadvertently cause database side effects, not that a hostile party runs malicious code on it. (In order to use the script you need to already have access to MySQL directly, in which case you could make arbitrary changes to the db already.)

Answer 1

my $sth = $dbh->prepare("select blah from foo where bar regexp ".$dbh->quote($myRegex));
$sth->execute();

or

my $sth = $dbh->prepare("select blah from foo where bar regexp ?");
$sth->execute($myRegex);

Note that regular expressions are expensive, so this can still have an adverse effect on the server.

Answer 2

If using a prepared statement with bind placeholder isn't an option, then you certainly do need to "escape" potentially unsafe values supplied in the SQL text.

The only characters that need to be escaped are the backslash character, and the quote character used to enclose the string.

You're using a single quote to enclose the value. The backslash characters within the regular expression are going to need to be preceded by another backslash character. And since you are using single quotes to enclose the string, any single quotes within the value will need to be preceded by a backslash character.

You could escape additional characters, like a double quote, but that's not strictly necessary in your particular case. But it wouldn't hurt anything to escape the double quote character.

Whoever wrote the sendToSQL function should have also provided a corresponding escapeForSQL function as a wrapper to the DBI "quote" function:

sendToSQL("select blah from foo where bar regexp '" . escapeForSQL($myRegex) . "'");

Answer 3

The right approach is to follow the advice on bobby-tables.com and use placeholders in your queries.