You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Gilles Schlienger <s_...@yahoo.com> on 2008/02/21 09:14:15 UTC
Re : OT: Preventing sql injection attack
Hi all,
Aren't PreparedStatements supposed to take care of ovoiding SQL Injection already ?
I thought so. Maybe not all cases ??
Gilles
----- Message d'origine ----
De : Larry Meadors <la...@gmail.com>
À : user-java@ibatis.apache.org
Envoyé le : Jeudi, 21 Février 2008, 6h51mn 53s
Objet : Re: OT: Preventing sql injection attack
OK,
then
another
option...add
the
%
to
the
user
provided
input.
Larry
On
Wed,
Feb
20,
2008
at
10:23
PM,
Zoran
Avtarovski
<zo...@sparecreative.com>
wrote:
>
Thanks
Larry,
>
>
But
no
joy.
The
db
is
MySQL
5.
To
provide
more
details
we
are
already
>
escaping
single
quotes
with
two
single
quotes
in
the
business
logic
ie
>
stringSql.replaceAll("'",
"''")
>
>
Bit
I
was
hoping
there
was
a
more
elegant
solution,
like
the
one
you
>
suggested
-
which
is
not
working
for
me.
>
>
Z.
>
>
>
>
>
This
should
work:
>
>
>
>
select
*
from
table
where
column
LIKE
#value#
||
'%'
>
>
>
>
Larry
>
>
>
>
On
Wed,
Feb
20,
2008
at
9:40
PM,
Zoran
Avtarovski
>
>
<zo...@sparecreative.com>
wrote:
>
>>
We
have
a
web
application
with
an
ajax
autocomplete
text
box.
The
problem
is
>
>>
that
currently
the
query
statement
for
the
ajax
query
is
:
>
>>
>
>>
Select
*
from
table
where
column
LIKE
'$value$%'
>
>>
>
>>
Which
is
susceptible
to
sql
injection
attacks.
>
>>
>
>>
One
solution
is
to
have
a
separate
connection
pool
with
read-only
>
>>
privileges,
but
this
seems
blunt
and
doesn't
prevent
malicious
access
to
>
>>
sensitive
data.
>
>>
>
>>
>
>>
Is
there
a
better
way
of
doing
this?
>
>>
>
>>
>
>>
Z.
>
>>
>
>>
>
>>
>
>
>
_____________________________________________________________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail http://mail.yahoo.fr
Re: Re : OT: Preventing sql injection attack
Posted by Zoran Avtarovski <zo...@sparecreative.com>.
That's right. But in the case of an auto complete query you want to search
words starting with your argument which needs to look like: select from
table where login = 'fre%'
By escaping any single quotes in java covers most attacks but it's a shame
there's no SQL function for STARTS_WITH(#value#).
Z.
> They are, but only if you use the #blah# notation (in iBATIS) and not $blah$.
>
> The difference is how the variables are added to the SQL.
>
> In the first case, it does this:
>
> select * from employee where login = #blah# -> select * from table
> where field = ?
>
> In the second case, if the variable blah is 'fred', it does this:
>
> select * from employee where login = $blah$ -> select * from employee
> where login = 'fred'
>
> No parameter there, just straight SQL...so if blah = "'fred'; drop
> table employee;--", you're screwed:
>
> select * from employee where login = 'fred'; drop table employee;--
>
> That will do the select, drop the table, then the "--" (as a comment)
> makes sure the remainder of the SQL on the line doesn't make the parse
> fail.
>
> Larry
>
>
> On Thu, Feb 21, 2008 at 1:14 AM, Gilles Schlienger <s_...@yahoo.com> wrote:
Re: Re : OT: Preventing sql injection attack
Posted by Larry Meadors <la...@gmail.com>.
They are, but only if you use the #blah# notation (in iBATIS) and not $blah$.
The difference is how the variables are added to the SQL.
In the first case, it does this:
select * from employee where login = #blah# -> select * from table
where field = ?
In the second case, if the variable blah is 'fred', it does this:
select * from employee where login = $blah$ -> select * from employee
where login = 'fred'
No parameter there, just straight SQL...so if blah = "'fred'; drop
table employee;--", you're screwed:
select * from employee where login = 'fred'; drop table employee;--
That will do the select, drop the table, then the "--" (as a comment)
makes sure the remainder of the SQL on the line doesn't make the parse
fail.
Larry
On Thu, Feb 21, 2008 at 1:14 AM, Gilles Schlienger <s_...@yahoo.com> wrote:
> Hi all,
>
> Aren't PreparedStatements supposed to take care of ovoiding SQL Injection already ?
>
> I thought so. Maybe not all cases ??
>
> Gilles
>
> ----- Message d'origine ----
> De : Larry Meadors <la...@gmail.com>
> À : user-java@ibatis.apache.org
> Envoyé le : Jeudi, 21 Février 2008, 6h51mn 53s
> Objet : Re: OT: Preventing sql injection attack
>
>
>
> OK,
> then
> another
> option...add
> the
> %
> to
> the
> user
> provided
> input.
>
> Larry
>
>
> On
> Wed,
> Feb
> 20,
> 2008
> at
> 10:23
> PM,
> Zoran
> Avtarovski
> <zo...@sparecreative.com>
> wrote:
> >
> Thanks
> Larry,
> >
> >
> But
> no
> joy.
> The
> db
> is
> MySQL
> 5.
> To
> provide
> more
> details
> we
> are
> already
> >
> escaping
> single
> quotes
> with
> two
> single
> quotes
> in
> the
> business
> logic
> ie
> >
> stringSql.replaceAll("'",
> "''")
> >
> >
> Bit
> I
> was
> hoping
> there
> was
> a
> more
> elegant
> solution,
> like
> the
> one
> you
> >
> suggested
> -
> which
> is
> not
> working
> for
> me.
> >
> >
> Z.
> >
> >
> >
> >
> >
> This
> should
> work:
> >
> >
> >
> >
> select
> *
> from
> table
> where
> column
> LIKE
> #value#
> ||
> '%'
> >
> >
> >
> >
> Larry
> >
> >
> >
> >
> On
> Wed,
> Feb
> 20,
> 2008
> at
> 9:40
> PM,
> Zoran
> Avtarovski
> >
> >
> <zo...@sparecreative.com>
> wrote:
> >
> >>
> We
> have
> a
> web
> application
> with
> an
> ajax
> autocomplete
> text
> box.
> The
> problem
> is
> >
> >>
> that
> currently
> the
> query
> statement
> for
> the
> ajax
> query
> is
> :
> >
> >>
> >
> >>
> Select
> *
> from
> table
> where
> column
> LIKE
> '$value$%'
> >
> >>
> >
> >>
> Which
> is
> susceptible
> to
> sql
> injection
> attacks.
> >
> >>
> >
> >>
> One
> solution
> is
> to
> have
> a
> separate
> connection
> pool
> with
> read-only
> >
> >>
> privileges,
> but
> this
> seems
> blunt
> and
> doesn't
> prevent
> malicious
> access
> to
> >
> >>
> sensitive
> data.
> >
> >>
> >
> >>
> >
> >>
> Is
> there
> a
> better
> way
> of
> doing
> this?
> >
> >>
> >
> >>
> >
> >>
> Z.
> >
> >>
> >
> >>
> >
> >>
> >
> >
> >
>
>
>
>
>
>
> _____________________________________________________________________________
> Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail http://mail.yahoo.fr
>