Saturday, November 04, 2006

Beware of SQL Injection

SQL injection is one of the simple but very powerful security threat, that is also very common in various sites.

Let us say we have a website, and we want to let only the registered users to login by asking for username and password. Let us say we store the username and password in the database.

To check whether the user is valid or not, we use the following query.


select * from users where username='abcd' and password='xyz'


If the result contains atleast 1 row, we say the user name password matches.

A typical Java code will be,


String username = "abcd"; // or get from the user.
String password = "xyz"; // or get from the user.

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"select * from users
where username='"+username+"'"
and password='"+password+"'"
);
if(rs.next()){
// user is logged in.
}else{
// login failed.
}


As we are simply concatenating the string, we don't have a clue what the user will type for the username and password fields.

What happens if the user types ' or ''=' including the quotes?
Then, the SQL query will be

select * from users where username='abcd' and password='' or ''=''

This will succeed irrespective of the username or password the user types. This will clearly be a security threat.

How to fix this?
Should we check for the input string whether it contains any single quote and ignore if any? That will be very difficult to check for each and every field.
A simple technique is, use PreparedStatement.

That is the code will then be,

String username = "abcd"; // or get from the user.
String password = "xyz"; // or get from the user.

PreparedStatement stmt = con.prepareStatement("select * from users
where username='?'
and password='?'"
);
stmt.setString(1,username);
stmt.setString(2,password);

ResultSet rs = stmt.executeQuery();

if(rs.next()){
// user is logged in.
}else{
// login failed.
}

When the user types the password as before, it wont allow the user to login as the prepared statement takes care of escaping the characters, there by guaranteeing the expected behavior.

Conclusion:
Always try to use PreparedStatement instead of String concatenations in all places.

7 Comments:

Anonymous Anonymous said...

i dunno about java, but with the python db api, you could do something like
cursor.execute("select * from tbl where name=%s and passwd=%s", (name,pass))

where the second argument is a tuple, substituted for the place holders. and the api takes care of the escaping accoring to the database spec, that sql injection seems impossible.

also, most web programmers might be using some framework, in which case, the framework would provide some customizable authentication mechanism (possibly written by a smarter person) which would invalidate such attacks.

10:38 AM  
Blogger Jayaprabhakar said...

Thanks for visiting my Blog, Venkat.
The SQL Injection problem exists in almost all languages where String concatenation is present.

Eg: Even in Python
//
cursor.execute("select * from tbl where name="+name+" and passwd="+pass)
//
(I am not sure about the syntax for string concate in Phython :) )
Because of carelessness, even experienced programmers do this mistake, which is very simple to avoid.

5:09 AM  
Blogger annacoder said...

This comment has been removed by the author.

12:16 PM  
Blogger annacoder said...

JP, just saw your response :)... but, usually, the method i have said is only followed... yes, i agree, the fundamental rule is to allow the underlying SQL api to do the string escaping...

cross side scripting is one more (also well known) interesting attack

12:18 PM  
Blogger Krishnakanth Soni said...

Great Blog !!! Found very interesting !!!

7:56 AM  
Blogger ペンギン said...

http://www.morikacopy.com/ ブランドコピー
http://www.morikacopy.com/louisvuitton/ ルイヴィトンコピー
http://www.morikacopy.com/chanel/ シャネルコピー

5:59 PM  
Blogger New Moon said...

عروض عقارات تركيا 2018: شاهد أجمل عقارات للبيع في تركيا مع شركة داماس العقارية الرائدة في العقار في تركيا وبوابتك لمعرفة عروض العقارات في تركيا
http://www.nikelebronxlow.com/2017/11/apartment-for-sale-turky.html
http://damasturk.postagon.com/8mj9mx88p
http://www.streamlinepic.com/2017/11/propery-for-sale-in-istanbul.html
http://www.babalaqarat.com/2017/11/damasturkcom.html
https://aqarat.livejournal.com/1153.html
https://aqarat.blog/post/apartment-for-sale-turkey/
https://aqarat.blog/post/apartment-for-sale-turkey/
، نوفر لعملائنا الكرام أفضل صفقة شراء عقار في تركيا، وأسعار العقارات في تركيا لدينا هي الأفضل والأنسب.

8:45 AM  

Post a Comment

<< Home