SQL injection attack (SQLIA) is one the typical, easiest and one of the highest rated vulnerabilities present in websites. Despite they being easy to stop and being repeated again and again, unsuspecting webmasters easily fall prey to these attacks due to their ignorance or non-vigilant behavior against possible areas of attacks. Consider the following trivial SQL statement in java:
String sqlStmnt=”Select * from UserList where user_id=’” + user_id + “‘;”;
Let’s say, the user_id comes from the users as input. What happens when a malicious user or a hacker passes the following user_id:
1' or '1'='1
The final statement becomes:
Select * from UserList where user_id='1' or '1'='1';
What happened? We unknowingly dumped the entire table for the hacker to see. A better example of the possibilities is user_id being:
1';drop table UserList;select '1
Now our statement becomes:
Select * from UserList where user_id='1';drop table UserList;select '1';
The hacker is simply able to drop the entire table. The possibilities of exploiting this simple, yet avoidable attack, are limited only by imagination of the attacker and his/her knowledge of sql and application. This is a classic example of exploiting web applications using SQLIA. A knowledgeable hacker may be able to use multiple ways of attacking a websites (beyond SQL) and combine them with SQLIA to cause further harm to the application.
How to protect again such attacks?:
- Always use parameterized queries. While using parameterized queries, the application takes care of properly replacing the parameters such that they may not harm the system.
- Using ORM libraries.
- Escaping values like single quotes etc. to make the attacks harder. Php function mysql_real_escape_string() does this.
- Allowing only specific values, if possible, in the forms while interacting with the user. A user_id form field may not require special characters as inpur.
- Controlling user privileges. It is always a good idea to not allow access to the users more than they need. For example, there is no need of web application user of mysql to have access to drop the table. Similarly, a logging user should not have any access other than inserting records in database.
- Limiting the length of user input is also often an effective method to prevent the attacks as attackers can then only fire queries within limited size.
- Information which is not required should not be kept on networked system and better be sent to physical backup drives like hard disk etc.