Re: Bind Variables and Quoting / Dequoting Input

--- Michael Fuhr <mike(at)fuhr(dot)org> wrote:

> 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

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