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.)
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.