Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Perl Misc (http://www.velocityreviews.com/forums/f67-perl-misc.html)
-   -   Is prepare statement enough for SQL injection? (http://www.velocityreviews.com/forums/t898356-is-prepare-statement-enough-for-sql-injection.html)

xhoster@gmail.com 06-05-2006 03:52 PM

Re: Is prepare statement enough for SQL injection?
 
"howa" <howachen@gmail.com> wrote:
> If I use prepare statement in every dbh call (mysql), will all chances
> of SQL injection can be prevented?


No, prepared statements are not magical (in fact they don't even really
exist for mysql, they are emulated by the DBI/DBD modules). They do make
it easier to write safer code, because they facilitate use of bind
variables. But you can write unsafe code with prepare just as easily as
with selectall_arrayref or whatever.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

howa 06-05-2006 03:56 PM

Is prepare statement enough for SQL injection?
 
If I use prepare statement in every dbh call (mysql), will all chances
of SQL injection can be prevented?

thanks...

howa


howa 06-05-2006 04:11 PM

Re: Is prepare statement enough for SQL injection?
 
any examples of SQL injection even if I use the prepare statement?

thanks....

xhoster@gmail.com 寫道:

> "howa" <howachen@gmail.com> wrote:
> > If I use prepare statement in every dbh call (mysql), will all chances
> > of SQL injection can be prevented?

>
> No, prepared statements are not magical (in fact they don't even really
> exist for mysql, they are emulated by the DBI/DBD modules). They do make
> it easier to write safer code, because they facilitate use of bind
> variables. But you can write unsafe code with prepare just as easily as
> with selectall_arrayref or whatever.
>
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service $9.95/Month 30GB



xhoster@gmail.com 06-05-2006 04:16 PM

Re: Is prepare statement enough for SQL injection?
 
"howa" <howachen@gmail.com> wrote:
> any examples of SQL injection even if I use the prepare statement?


Sure. Please give me an example of SQL injection without using prepare.
I'll translate it to prepare in a way that doesn't solve the injection
problem.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

Tsz Ming Wong 06-05-2006 04:58 PM

Re: Is prepare statement enough for SQL injection?
 
okay, such as :

my $username = "tom' ; DELETE FROM users";

my $sqlstr = "SELECT * FROM users WHERE name = '$username'";

How to re-produce this hack using prepare statement?

thanks...


xhoster@gmail.com wrote:
> "howa" <howachen@gmail.com> wrote:
> > any examples of SQL injection even if I use the prepare statement?

>
> Sure. Please give me an example of SQL injection without using prepare.
> I'll translate it to prepare in a way that doesn't solve the injection
> problem.
>
> Xho
>
> --
> -------------------- http://NewsReader.Com/ --------------------
> Usenet Newsgroup Service $9.95/Month 30GB



krakle@visto.com 06-05-2006 05:20 PM

Re: Is prepare statement enough for SQL injection?
 
Couldn't you just use "quote" to backslash all illegal characters in a
variable for mySQL to prevent such a thing..

my $safe_var = $dbh->quote($my_var);
my $sqlstr = qq{SELECT * FROM users WHERE name = $safe_var;};
$sth = $dbh->prepare($sqlstr);

Tsz Ming Wong wrote:
> okay, such as :
>
> my $username = "tom' ; DELETE FROM users";
>
> my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
>
> How to re-produce this hack using prepare statement?
>
> thanks...
>
>
> xhoster@gmail.com wrote:
> > "howa" <howachen@gmail.com> wrote:
> > > any examples of SQL injection even if I use the prepare statement?

> >
> > Sure. Please give me an example of SQL injection without using prepare.
> > I'll translate it to prepare in a way that doesn't solve the injection
> > problem.
> >
> > Xho
> >
> > --
> > -------------------- http://NewsReader.Com/ --------------------
> > Usenet Newsgroup Service $9.95/Month 30GB



howa 06-05-2006 05:33 PM

Re: Is prepare statement enough for SQL injection?
 
there is no need to...

my $name = "tom";
my $sqlstr = "SELECT * FROM users WHERE name = ? ";
my $sth = $dbh->prepare($sqlstr);
my $rc = $sth->execute($name);

i just wonder how to hack the above prepare codes using SQL injection?


krakle@visto.com 寫道:

> Couldn't you just use "quote" to backslash all illegal characters in a
> variable for mySQL to prevent such a thing..
>
> my $safe_var = $dbh->quote($my_var);
> my $sqlstr = qq{SELECT * FROM users WHERE name = $safe_var;};
> $sth = $dbh->prepare($sqlstr);
>
> wrote:
> > okay, such as :
> >
> > my $username = "tom' ; DELETE FROM users";
> >
> > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
> >
> > How to re-produce this hack using prepare statement?
> >
> > thanks...
> >
> >
> > xhoster@gmail.com wrote:
> > > "howa" <howachen@gmail.com> wrote:
> > > > any examples of SQL injection even if I use the prepare statement?
> > >
> > > Sure. Please give me an example of SQL injection without using prepare.
> > > I'll translate it to prepare in a way that doesn't solve the injection
> > > problem.
> > >
> > > Xho
> > >
> > > --
> > > -------------------- http://NewsReader.Com/ --------------------
> > > Usenet Newsgroup Service $9.95/Month 30GB



Ben Morrow 06-05-2006 05:50 PM

Re: Is prepare statement enough for SQL injection?
 
[please learn to quote properly. Now.]

Quoth "howa" <howachen@gmail.com>:
> krakle@visto.com:
> > <howachen@gmail.com> wrote:
> > > xhoster@gmail.com wrote:
> > > > "howa" <howachen@gmail.com> wrote:
> > > > >
> > > > > any examples of SQL injection even if I use the prepare statement?
> > > >
> > > > Sure. Please give me an example of SQL injection without using
> > > > prepare. I'll translate it to prepare in a way that doesn't
> > > > solve the injection problem.
> > >
> > > okay, such as :
> > >
> > > my $username = "tom' ; DELETE FROM users";
> > >
> > > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
> > >
> > > How to re-produce this hack using prepare statement?

> >
> > Couldn't you just use "quote" to backslash all illegal characters in a
> > variable for mySQL to prevent such a thing..

>
> there is no need to...
>
> my $name = "tom";
> my $sqlstr = "SELECT * FROM users WHERE name = ? ";
> my $sth = $dbh->prepare($sqlstr);
> my $rc = $sth->execute($name);
>
> i just wonder how to hack the above prepare codes using SQL injection?


Xho's point is that it is not the prepare that gives the safety here, it
is the use of bind variables (the '?'). IIRC, you can use bind variables
without prepare; certainly, you can create unsafe statements *with*
prepare by interpolating variables straight into the prepared statement
as your original example did.

Ben

--
If you put all the prophets, | You'd have so much more reason
Mystics and saints | Than ever was born
In one room together, | Out of all of the conflicts of time.
benmorrow@tiscali.co.uk The Levellers, 'Believers'

Paul Lalli 06-05-2006 06:08 PM

Re: Is prepare statement enough for SQL injection?
 
Ben Morrow wrote:
> [please learn to quote properly. Now.]
>
> Quoth "howa" <howachen@gmail.com>:
> > krakle@visto.com:
> > > <howachen@gmail.com> wrote:
> > > > xhoster@gmail.com wrote:
> > > > > "howa" <howachen@gmail.com> wrote:
> > > > > >
> > > > > > any examples of SQL injection even if I use the prepare statement?
> > > > >
> > > > > Sure. Please give me an example of SQL injection without using
> > > > > prepare. I'll translate it to prepare in a way that doesn't
> > > > > solve the injection problem.
> > > >
> > > > okay, such as :
> > > >
> > > > my $username = "tom' ; DELETE FROM users";
> > > >
> > > > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
> > > >
> > > > How to re-produce this hack using prepare statement?
> > >
> > > Couldn't you just use "quote" to backslash all illegal characters in a
> > > variable for mySQL to prevent such a thing..

> >
> > there is no need to...
> >
> > my $name = "tom";
> > my $sqlstr = "SELECT * FROM users WHERE name = ? ";
> > my $sth = $dbh->prepare($sqlstr);
> > my $rc = $sth->execute($name);
> >
> > i just wonder how to hack the above prepare codes using SQL injection?

>
> Xho's point is that it is not the prepare that gives the safety here, it
> is the use of bind variables (the '?'). IIRC, you can use bind variables
> without prepare; certainly, you can create unsafe statements *with*
> prepare by interpolating variables straight into the prepared statement
> as your original example did.


Er, you can? When I try to run such code, I get the error:
DBD::Informix::db prepare failed: SQL: -555: Cannot use a select or any
of the database statements in a multi-query prepare.

Prepared statements must be *one* statement each.

Of course, I get the same message if I try to use the same kind of
query with a do() instead of a prepare()/execute(), so this is still
not an example of prepare() saving you from anything.

The kind of SQL injection attack that will not be prevented by
prepare() (or do()) but will be prevented by bind variables is a
scenario like this:
my $user = "john' or '1' = '1";
my $sql = "SELECT * from account_info WHERE username = '$user'";
my $sth = $dbh->prepare($sql);
$sth->execute();

As opposed to:
my $user = "john' or '1' = '1";
my $sql = "SELECT * from account_info WHERE username = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($user);

Paul Lalli


howa 06-05-2006 06:08 PM

Re: Is prepare statement enough for SQL injection?
 
how to use bind variables without prepare ? i would like to know...

thanks...

Ben Morrow 寫道:

> [please learn to quote properly. Now.]
>
> Quoth "howa" <howachen@gmail.com>:
> > krakle@visto.com:
> > > <howachen@gmail.com> wrote:
> > > > xhoster@gmail.com wrote:
> > > > > "howa" <howachen@gmail.com> wrote:
> > > > > >
> > > > > > any examples of SQL injection even if I use the prepare statement?
> > > > >
> > > > > Sure. Please give me an example of SQL injection without using
> > > > > prepare. I'll translate it to prepare in a way that doesn't
> > > > > solve the injection problem.
> > > >
> > > > okay, such as :
> > > >
> > > > my $username = "tom' ; DELETE FROM users";
> > > >
> > > > my $sqlstr = "SELECT * FROM users WHERE name = '$username'";
> > > >
> > > > How to re-produce this hack using prepare statement?
> > >
> > > Couldn't you just use "quote" to backslash all illegal characters in a
> > > variable for mySQL to prevent such a thing..

> >
> > there is no need to...
> >
> > my $name = "tom";
> > my $sqlstr = "SELECT * FROM users WHERE name = ? ";
> > my $sth = $dbh->prepare($sqlstr);
> > my $rc = $sth->execute($name);
> >
> > i just wonder how to hack the above prepare codes using SQL injection?

>
> Xho's point is that it is not the prepare that gives the safety here, it
> is the use of bind variables (the '?'). IIRC, you can use bind variables
> without prepare; certainly, you can create unsafe statements *with*
> prepare by interpolating variables straight into the prepared statement
> as your original example did.
>
> Ben
>
> --
> If you put all the prophets, | You'd have so much more reason
> Mystics and saints | Than ever was born
> In one room together, | Out of all of the conflicts of time.
> benmorrow@tiscali.co.uk The Levellers, 'Believers'




All times are GMT. The time now is 10:28 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.