Обсуждение: Bind Variables and Quoting / Dequoting Input
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. problem is, i'm not sure. any guidance is appreciated, of course. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
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
On Fri, Dec 09, 2005 at 06:22:29PM -0700, Michael Fuhr 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. I might have misunderstood what you meant by "bind variables." Could you explain exactly what you're doing? -- Michael Fuhr
--- Michael Fuhr <mike@fuhr.org> wrote: > On Fri, Dec 09, 2005 at 06:22:29PM -0700, Michael > Fuhr 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. > > I might have misunderstood what you meant by "bind > variables." > Could you explain exactly what you're doing? yes... this is an adodb code snippet: > $sql_insert = <<<_EOSQL > INSERT INTO t_customer (customer_id, customer_name, > customer_entry_date) > VALUES (?,?,?) > _EOSQL; > > $result = $db->Execute($sql_insert, > array($customer_id, $customer_name, $db->DBDate(time()))); __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--- 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
On Mon, Dec 12, 2005 at 09:08:32AM -0800, operationsengineer1@yahoo.com wrote:
> 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.
Using placeholders is supposed to be safe -- that's part of the
rationale for using them -- but you'd have to examine the implementation
to be sure it doesn't have any vulnerabilities.
I see the following in the ADOdb documentation:
Currently Oracle, Interbase and ODBC supports variable binding.
Interbase/ODBC style ? binding is emulated in databases that
do not support binding. Note that you do not have to quote
strings if you use binding.
If this documentation is up to date then apparently the PostgreSQL
driver does emulation. Recent versions of PostgreSQL (7.4 and
later) support separation of SQL and parameters at the protocol
layer but you'd have to dig into ADOdb to see if it uses that
capability.
--
Michael Fuhr
--- Michael Fuhr <mike@fuhr.org> wrote: > On Mon, Dec 12, 2005 at 09:08:32AM -0800, > operationsengineer1@yahoo.com wrote: > > 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. > > Using placeholders is supposed to be safe -- that's > part of the > rationale for using them -- but you'd have to > examine the implementation > to be sure it doesn't have any vulnerabilities. > > I see the following in the ADOdb documentation: > > Currently Oracle, Interbase and ODBC supports > variable binding. > Interbase/ODBC style ? binding is emulated in > databases that > do not support binding. Note that you do not > have to quote > strings if you use binding. > > If this documentation is up to date then apparently > the PostgreSQL > driver does emulation. Recent versions of > PostgreSQL (7.4 and > later) support separation of SQL and parameters at > the protocol > layer but you'd have to dig into ADOdb to see if it > uses that > capability. fyi, john's answer from his forum... Yes, in adodb 4.68, if you are running php5, native variable binding is used. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com