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.