Re: Bind Variables and Quoting / Dequoting Input

--- Michael Fuhr <mike@fuhr.org> wrote:

> On Fri, Dec 09, 2005 at 01:54:13PM -0800,
> operationsengineer1@yahoo.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

Mike, thanks.  i was getting quotes inside the
database "cells", which is why i had to figure out
what was going on.  the data is inserted correctly
now, i just want to make sure the process is also a
safe process.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com