Re: Bind Variables and Quoting / Dequoting Input

On Fri, Dec 09, 2005 at 01:54:13PM -0800, operationsengineer1(at)yahoo(dot)com wrote:
> do i need to quote input even though i'm using bind
> variables in my queries?
>
> i seem to think that quoting on entry and unquoting on
> return was a method for fighting sql injection, but
> i'm also thinking that bind variables may make that
> step meaningless.

Using placeholders should eliminate the need to quote, either by
quoting for you or by using the underlying protocol's mechanism for
parameterized queries. If you quote the data then you'll probably
see extra quotes in the inserted data, as in this example:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $data = "abc'def";
my $dbh = DBI->connect("dbi:Pg:dbname=test", "", "", {RaiseError => 1});
my $sth = $dbh->prepare("INSERT INTO foo VALUES (?)");
$sth->execute($data);
$sth->execute($dbh->quote($data));
$dbh->disconnect;

After running this script the table contains the following data:

test=> SELECT * FROM foo;
data
------------
abc'def
'abc''def'
(2 rows)

The first row is what we want; the second row is over-quoted. Check
your client interface's documentation or run tests to be sure it
works this way, but this example shows what's supposed to happen.

--
Michael Fuhr