10

I have a SQL statement similar to the one shown below in Perl:

my $sql="abc..TableName '$a','$b' ";

The $a is free text which can contain anything including single quotes, double quotes, back- and front-slash characters, etc.

How can these characters be escaped to make the SQL statement work?

Thanks.

brian d foy
  • 129,424
  • 31
  • 207
  • 592
Sam
  • 111
  • 1
  • 1
  • 4
  • 3
    `$a` and `$b` have special meaning related to the `sort` function. It's generally not a good idea to use them. Except as short variable names in a code sample. – daotoad Jan 05 '10 at 03:27
  • Other than the [perl] tag, dup of [Best way to stop SQL Injection in PHP](http://stackoverflow.com/q/60174/90527). The only practical difference lies in the code samples. Also, dup of [What is the best way to avoid SQL injection attacks?](http://stackoverflow.com/q/1973/). – outis Apr 27 '12 at 22:36

4 Answers4

22

You can either use the ->quote method (assuming you're using DBI):

my $oldValue = $dbh->quote('oldValue');
my $newValue = $dbh->quote('newValue');
$dbh->do("UPDATE myTable SET myValue=$newValue where myValue=$oldValue");

Better still, the best practice is to use bind values:

my $sth = $dbh->prepare('UPDATE myTable SET myValue=? WHERE myValue=?');

$sth->execute('newValue','oldValue');

This should also work for stored procedure calls, assuming the statement once the strings have been expanded is valid SQL. This may be driver/DB specific so YMMV.

my $sth = $dbh->prepare("DBName..ProcName ?,? ");
$sth->execute($a, $b);
mopoke
  • 10,555
  • 1
  • 31
  • 31
  • I am sorry for not being clear. I am making a call to a stored procedure. The example should have been something like below: my $sql="DBName..ProcName '$a','$b' "; – Sam Jan 05 '10 at 01:39
  • 5
    Using bind parameters should still work for stored procedure calls. – mopoke Jan 05 '10 at 01:40
  • And in case it's not obvious, if you've been using bind parameters AND you've been manually escaping single quotes on your values before executing the insert, then the column value will end up with two sequential single quotes for every single quote. – RTF Sep 21 '15 at 13:29
9

Use a prepared statement. Replace the variable with a ?. To crib an example from DBI manpages:

$sql = 'SELECT * FROM people WHERE lastname = ?';
$sth = $dbh->prepare($sql);
$sth->execute($user_input_here);

Interpolating user input into your SQL is asking for security holes.

  • I am sorry for not being clear. I am making a call to a stored procedure. The example should have been something like below: my $sql="DBName..ProcName '$a','$b' "; – Sam Jan 05 '10 at 01:38
6

If you use query parameter placeholders, you don't have to escape the content of the strings.

my $sql="DBName..ProcName ?, ?";
$sth = $dbh->prepare($sql);
$sth->execute($a, $b);

If the DBI is using true query parameters, it sends the parameter values to the RDBMS separately from the SQL statement. The values are never combined with the SQL statement string, therefore the values never have an opportunity to cause SQL injection.

If the DBI is "emulating" prepared statements by interpolating the variables into the query string, then DBI should handle the correct escaping logic so you don't have to. Let the experts (those who write and test DBI) worry about how to do it.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
4

If you don't want to use ->quote (for some reason, this function doesn't run on my version of DBI) then try this:

$query=~s/\"/\\\"/g;

I tend to do the same with single quotes and commas too just to be safe.

Seems to work fine for me...!

Chris Denman
  • 1,187
  • 3
  • 9
  • 15