Re: R: [HELP] Whether or not iBatis support SQL Injection?
Hi Nathan, Given: SELECT * FROM ACCOUNT WHERE USERNAME = ? and PASSWORD = ? Consider that without prepared statements it would likely be implemented like this: String username = cbegin; String password = barnacle String sql = SELECT * FROM ACCOUNT WHERE USERNAME = ' + username + ' AND PASSWORD = ' + password + '; Now consider what would happen if the password was this: String password = x' OR USERNAME = 'cbegin; You'd end up with the SQL statement: SELECT * FROM ACCOUNT WHERE USERNAME = 'cbegin' AND PASSWORD = 'x' OR USERNAME = 'cbegin' In Microsoft SQL Server, this would load the ACCOUNT record without the password. iBATIS protects against this by always using PreparedStatement, but if you use $substitutions$ in you statements, be very careful! Cheers, Clinton On 7/9/05, Nathan Maves [EMAIL PROTECTED] wrote: I was asked the question What is SQL injection and how can I avoid it?I understand it to a point but an example would be great.NathanOn Jul 5, 2005, at 7:01 AM, Larry Meadors wrote: Yes, it does pass the SQL directly to the driver, but unless you use the $$ syntax for parameters, you should be safe with iBATIS. The $$ syntax is the only part of iBATIS that allows string concatenation, which is the biggest source of SQL injection attacks. If you are using a really crappy jdbc driver, you could have issues with it somehow botching things in it's implementation of prepared statements, but I have not heard of a single case of that happening. Larry On 7/5/05, Fabrizio Gianneschi [EMAIL PROTECTED] wrote: Since iBatis uses PreparedStatements a lot, it's safer than old school JDBC code, even if it's still vulnerable because it passes the SQL directly to the driver without checking, afaik. You can always use some good tricks to increase the robustness of your SQL, but... ...this type of checking is not responsibility of a SQL mapper layer like iBATIS. I think you should check your user input in higher server side layers, such as the presentation one; Struts Actions and/or ActionForms, for example. FabDa: Pham Anh Tuan [mailto:[EMAIL PROTECTED]] Inviato: martedì 5 luglio 2005 12.16 A: iBatis Oggetto: [HELP] Whether or not iBatis support SQL Injection? Hi all, I don't know whether or not iBatis support checking SQL Injection or not ? plz help me :) Pham
Re: [HELP] Whether or not iBatis support SQL Injection?
[ 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Hi all, I don't know whether or not iBatis support checking SQL Injection or not ? plz help me :) Pham
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Hi all, I don't know whether or not iBatis support checking SQL Injection or not ? plz help me :) Pham
Re: [HELP] Whether or not iBatis support SQL Injection?
hey :) I must read more about that :) If you have any document about 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, plz show me uh :) Larry, thank you very much :) - Original Message - From: Larry Meadors [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Hi all, I don't know whether or not iBatis support checking SQL Injection or not ? plz help me :) Pham
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Hi all, I don't know whether or not iBatis support checking SQL Injection or not ? plz help me :) Pham
Re: [HELP] Whether or not iBatis support SQL Injection?
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 ? The query would be considered as select * from user where name = 'bowl;drop table foo;--' (I guess it would just return 0 rows) I prepared a long, detailed and complex explanation but then found this short description.. I hope it's enough to clear the doubts.. :-) For instance, in Java, a secure way to build SQL statements is to construct all queries with PreparedStatement instead of Statement () When PreparedStatement is used, most JDBC drivers will prepare a statement with the server, and then supply the parameters separately. In either case, after the initial parsing, there is a clear distinction between the SQL statement and the variable. The variables are encapsulated and special characters within them are automatically escaped in a manner suited to the target database. Consequently, it is impossible for a hacker to pass malicious input and have it treated as if it were the actual SQL statement— which is necessary if the hacker is going to succeed with SQL injection attacks. ( quoted from http://www.devx.com/security/Article/20678/0/page/2 )
Re: [HELP] Whether or not iBatis support SQL Injection?
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:[EMAIL PROTECTED] Verzonden: woensdag, juli 6, 2005 09:16 AM Aan: user-java@ibatis.apache.org, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
Re: R: [HELP] Whether or not iBatis support SQL Injection?
Yes, it does pass the SQL directly to the driver, but unless you use the $$ syntax for parameters, you should be safe with iBATIS. The $$ syntax is the only part of iBATIS that allows string concatenation, which is the biggest source of SQL injection attacks. If you are using a really crappy jdbc driver, you could have issues with it somehow botching things in it's implementation of prepared statements, but I have not heard of a single case of that happening. Larry On 7/5/05, Fabrizio Gianneschi [EMAIL PROTECTED] wrote: Since iBatis uses PreparedStatements a lot, it's safer than old school JDBC code, even if it's still vulnerable because it passes the SQL directly to the driver without checking, afaik. You can always use some good tricks to increase the robustness of your SQL, but... ...this type of checking is not responsibility of a SQL mapper layer like iBATIS. I think you should check your user input in higher server side layers, such as the presentation one; Struts Actions and/or ActionForms, for example. Fab Da: Pham Anh Tuan [mailto:[EMAIL PROTECTED] Inviato: martedì 5 luglio 2005 12.16 A: iBatis Oggetto: [HELP] Whether or not iBatis support SQL Injection? Hi all, I don't know whether or not iBatis support checking SQL Injection or not ? plz help me :) Pham
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 [EMAIL PROTECTED] wrote: Hi all, I don't know whether or not iBatis support checking SQL Injection or not ? plz help me :) Pham
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: Hi all, I don't know whether or not iBatis support checking SQL Injection or not ? plz help me :) Pham
