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 Zoran Avtarovski <zo...@sparecreative.com> on 2008/02/21 05:40:54 UTC
OT: Preventing sql injection attack
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.
Re: OT: Preventing sql injection attack
Posted by Nathan Maves <na...@gmail.com>.
Again I think it has been said but what about post-pending the % in this
case on the java side;
String param = theString + "%";
then using the prepared statement parameter by using
#value#
On Fri, Feb 22, 2008 at 2:42 PM, Koka Kiknadze <22...@gmail.com> wrote:
> Think you can limit how many symbols the user can enter to some reasonable
> value. If you can limit it, say to 20, you can use something like
>
> Select * from ((((((((((((((((((((
> Select * from table where column LIKE '$value$%'
> ))))))))))))))))))))
>
> i.e. malicious user will have to use 20 closing parenthesis in the value -
> no room left for extra SQL
>
>
>
Re: OT: Preventing sql injection attack
Posted by Koka Kiknadze <22...@gmail.com>.
Think you can limit how many symbols the user can enter to some reasonable
value. If you can limit it, say to 20, you can use something like
Select * from ((((((((((((((((((((
Select * from table where column LIKE '$value$%'
))))))))))))))))))))
i.e. malicious user will have to use 20 closing parenthesis in the value -
no room left for extra SQL
Re: OT: Preventing sql injection attack
Posted by Zoran Avtarovski <zo...@sparecreative.com>.
Good point. I might have a look at the Prepared statement source and use
that as a guide for implementing a utlity class to cover my ass.
Z.
>> I haven't implemented it yet, but I can't see a reason why it wouldn't
> work.
>
> I can not see either, but, to speed up such searches one usually indexes
> search column. While index will work well for Column LIKE 'bla%', I doubt it
> will be of any use with ANY_FUNCTION(Column...)...
Re: OT: Preventing sql injection attack
Posted by Koka Kiknadze <22...@gmail.com>.
> I haven't implemented it yet, but I can't see a reason why it wouldn't
work.
I can not see either, but, to speed up such searches one usually indexes
search column. While index will work well for Column LIKE 'bla%', I doubt it
will be of any use with ANY_FUNCTION(Column...)...
Re: OT: Preventing sql injection attack
Posted by Zoran Avtarovski <zo...@sparecreative.com>.
The solution was staring me in the face the whole time.
I just do a substring query to the length of the letters typed already:
Select column from table where SUBSTRING(column, 1, #term_length#) = #term#
I haven't implemented it yet, but I can't see a reason why it wouldn't work.
Z.
> 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.
>>>>
>>>>
>>>>
>>
>>
>>
Re: OT: Preventing sql injection attack
Posted by Larry Meadors <la...@gmail.com>.
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.
> >>
> >>
> >>
>
>
>
Re: OT: Preventing sql injection attack
Posted by Zoran Avtarovski <zo...@sparecreative.com>.
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.
>>
>>
>>
Re: OT: Preventing sql injection attack
Posted by Larry Meadors <la...@gmail.com>.
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.
>
>
>