Is prepare statement enough for SQL injection?

X

xhoster

howa said:
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
 
H

howa

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

thanks...

howa
 
H

howa

any examples of SQL injection even if I use the prepare statement?

thanks....

(e-mail address removed) 寫é“:
 
X

xhoster

howa said:
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
 
T

Tsz Ming Wong

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...
 
K

krakle

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);
 
H

howa

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?


(e-mail address removed) 寫é“:
 
B

Ben Morrow

[please learn to quote properly. Now.]

Quoth "howa said:
(e-mail address removed):

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
 
P

Paul Lalli

Ben said:
[please learn to quote properly. Now.]

Quoth "howa said:
(e-mail address removed):

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
 
H

howa

how to use bind variables without prepare ? i would like to know...

thanks...

Ben Morrow 寫é“:
[please learn to quote properly. Now.]

Quoth "howa said:
(e-mail address removed):

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.
(e-mail address removed) The Levellers, 'Believers'
 
P

Paul Lalli

howa said:
Ben Morrow 寫é“:
how to use bind variables without prepare ? i would like to know...

First, please start quoting properly. This is not the first time
you've been asked. Post your reply *below* what you are replying to.

Second, read the DBI module's documentation. Take a look at, for
example, the do() method:

my $sql = "DELETE FROM users WHERE user_name = ?";
$dbh->do($sql, undef, 'Mr. Itty');

Paul Lalli
 
H

howa

Paul Lalli 寫é“:
First, please start quoting properly. This is not the first time
you've been asked. Post your reply *below* what you are replying to.

Second, read the DBI module's documentation. Take a look at, for
example, the do() method:

my $sql = "DELETE FROM users WHERE user_name = ?";
$dbh->do($sql, undef, 'Mr. Itty');

Paul Lalli

Thanks...
 
X

xhoster

Tsz Ming Wong said:
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?

There is no hack to reproduce. There is no DBI, not statement (prepared
or otherwise) and no execution. There is no hack. All you have is a
string. A string is not a hack.

Furthermore, if I try to guess what you mean by filling in the missing
execution code, it is still isn't a hack because I get an SQL syntax error.

my $db = DBI->connect("DBI:mysql::xxxxx","x", "xxxx",
{ RaiseError => 1 }) ;
$db->do( "use xxx" );
my $username = "tom' ;
DELETE FROM users"; my $sqlstr = "SELECT * FROM users WHERE name =
'$username'";
$db->do($sqlstr); ##This is
line 6

DBD::mysql::db do failed: You have an error in your SQL syntax near ';
DELETE FROM users'' at line 1 at test_dbi.pl line 6

Xho
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top