ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sven.Boden" <list...@pandora.be>
Subject Re: [HELP] Whether or not iBatis support SQL Injection?
Date Wed, 06 Jul 2005 08:43:45 GMT

Pham,

I'll chip in... more information on http://en.wikipedia.org/wiki/Sql_injection 

Rewording Larry's answer...
The problem with SQL injection occurs when arguments to an SQL statement are done by actually
changing the SQL statement before execution. E.g. you add an additional "and user = " + userid
+ ";" to your SQL statement in Java by appending to an SQL string. If a user can enter arbitrary
data he could end the intended sql statement and have the engine execute something extra (the
drop in the Larry's example).

When using parameter markers/prepared statements SQL injection can not occur: you don't change
the SQL statement anymore, you just supply arguments. In the '1;drop table foo;--' case when
the parameter would be varchar e.g. it would just execute the select with as value for the
binded parameter '1;drop table foo;--', which will probably not return much but cannot not
do harm.
So the type of the parameter does not matter at all, when using only ?'s for arguments and
not changing the query itself via user input your 100% safe.

Personally I only use $$ to replace tables (which cannot be binded via parameter markers)
and still only in very limited cases, it's very bad for performance as you will get e.g. "cache
blow-out" in Oracle if you use it much.

Regards,
Sven Boden



>----- Oorspronkelijk bericht -----
>Van: Pham Anh Tuan [mailto:anhtuan@ichi-corp.jp]
>Verzonden: woensdag, juli 6, 2005 09:16 AM
>Aan: user-java@ibatis.apache.org, lmeadors@apache.org
>Onderwerp: Re: [HELP] Whether or not iBatis support SQL Injection?
>
>Oh, wait a minute, Larry!
>[
>if the parameter is '1;drop table foo;--', then the
>query will fail, because it is not an integer
>]
>
>As I guess, may be there's will be comparation between data type of the 
>column name Id with the data type of parameter which user inputted.
>
>If so, in another case, if another column named Name, data type is 
>Varchar(or String), we have sql like below:
>
>select * from user where name = ?
>
>and ? has value is 'bowl;drop table foo;--' ... what will happen, Larry ?
>
>
>
>----- Original Message ----- 
>From: "Larry Meadors" <larry.meadors@gmail.com>
>To: <user-java@ibatis.apache.org>
>Sent: Wednesday, July 06, 2005 1:48 PM
>Subject: Re: [HELP] Whether or not iBatis support SQL Injection?
>
>
>> The difference is that the driver is responsible for escaping the
>> parameters, not your application. What that means in more proactical
>> terms is that if the parameter is '1;drop table foo;--', then the
>> query will fail, because it is not an integer.
>>
>> So instead of dropping the table, a fairly harmless SQLException is 
>> thrown.
>>
>> Larry
>>
>>
>> On 7/6/05, Pham Anh Tuan <anhtuan@ichi-corp.jp> wrote:
>>> [
>>> select * from foo where id = ?
>>>
>>> ...then a second parameter is sent to the driver to tell it that the
>>> value of the ? placeholder is 1. The parameter 1 is not used to modify
>>> the SQL.
>>> ]
>>>
>>> why does the solution above can protect us from SQL Injection problems?
>>> because, I see that finally value of ? still be integer 1.
>>>
>>> Is there any magic when  "...then a second parameter is sent to the 
>>> driver
>>> to tell it that the
>>> value of the ? placeholder is 1"
>>>
>>> I don't understand :(
>>> ----- Original Message -----
>>> From: "Larry Meadors" <larry.meadors@gmail.com>
>>> To: <user-java@ibatis.apache.org>
>>> Sent: Wednesday, July 06, 2005 10:25 AM
>>> Subject: Re: [HELP] Whether or not iBatis support SQL Injection?
>>>
>>>
>>> > When you use this:
>>> >
>>> > <select id="good" resultMap="myResultMap">
>>> > select * from foo where id = #value#
>>> > </select>
>>> >
>>> > ...and call it like this:
>>> >
>>> > MyBean b = (MyBean)sqlMap.queryForObject("good", new Integer(1));
>>> >
>>> > ...iBATIS creates a prepared statement, so the SQL that goes to the
>>> > database is:
>>> >
>>> > select * from foo where id = ?
>>> >
>>> > ...then a second parameter is sent to the driver to tell it that the
>>> > value of the ? placeholder is 1. The parameter 1 is not used to modify
>>> > the SQL.
>>> >
>>> > However, when you use this:
>>> >
>>> > <select id="bad" resultMap="myResultMap">
>>> > select * from foo where id = $value$
>>> > </select>
>>> >
>>> > ...and call it like this:
>>> >
>>> > MyBean b = (MyBean)sqlMap.queryForObject("bad", new Integer(1));
>>> >
>>> > ...iBATIS creates a prepared statement, but the SQL that goes to the
>>> > database is:
>>> >
>>> > select * from foo where id = 1
>>> >
>>> > ...so the object passed in (the Integer in this case) is used to
>>> > modify the SQL that is executed. This is where the danger is.
>>> >
>>> > Let's say instead of an integer, a String was passed in from a web
>>> > page and the input was not checked. If the string was "1", that would
>>> > be just fine. However, a user could send a string like this: "1;drop
>>> > table foo;--", and instead of the query above, you would get this:
>>> >
>>> > select * from foo where id = 1;drop table foo;--
>>> >
>>> > Oops! what happened to the foo table?
>>> >
>>> > If you can use the ## syntax, do.
>>> >
>>> > Larry
>>> >
>>> >
>>> > On 7/5/05, Pham Anh Tuan <anhtuan@ichi-corp.jp> wrote:
>>> >> oh, thanks all you :)
>>> >>
>>> >> but I don't understand clearly why when we use ## is more safe than

>>> >> using
>>> >> $$.
>>> >>
>>> >> Is there any special things in using ## ???
>>> >>
>>> >> help me!
>>> >> ----- Original Message -----
>>> >> From: "Brandon Goodin" <brandon.goodin@gmail.com>
>>> >> To: <user-java@ibatis.apache.org>
>>> >> Sent: Tuesday, July 05, 2005 8:54 PM
>>> >> Subject: Re: [HELP] Whether or not iBatis support SQL Injection?
>>> >>
>>> >>
>>> >> > If you are using the #myProperty# delimiters you need not worry

>>> >> > about
>>> >> > sql injection. If you use the $myProperty$ literals you would need

>>> >> > to
>>> >> > guard against sql injection on your own.
>>> >> >
>>> >> > Brandon.
>>> >> >
>>> >> > On 7/5/05, Pham Anh Tuan <anhtuan@ichi-corp.jp> wrote:
>>> >> >>
>>> >> >> Hi all,
>>> >> >>
>>> >> >> I don't know whether or not iBatis support checking SQL Injection

>>> >> >> or
>>> >> >> not
>>> >> >> ?
>>> >> >>
>>> >> >> plz help me :)
>>> >> >>
>>> >> >> Pham
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>>
>>
>> 
>
>
>
>



Mime
View raw message