Proof Your Site Against SQL Injection
Posted by Prathap Sethu on Friday, October 23rd, 2009Web applications often accept input data from users and pass it to database systems. Problems happen when such data contain characters that have special meaning to the database. For example, the single quote (‘) is used by most database systems to terminate a string. Different databases have different such meta-characters. Hackers skilfully exploit the use of meta-characters to run SQL commands in the database.
Consider this code fragment that accepts an employee id from a web form and fetches employee details from the Employee table:
$empId = _GET["employee_id"];
$query = ”SELECT * FROM Employee WHERE emp_id = ” . $empld
If a user fills in the employee_id field with
1 ; UPDATE Employee SET salary = 5000
that would result in the following queries to execute:
SELECT * FROM Employee WHERE emp_id = 1 ; UPDATE Employee SET salary = 5000
Many database systems like Postgre allow multiple queries to execute in a single command. It’s anybody’s guess as to what would happen if the above queries execute. The hacker is exploiting the semi-colon (;) meta-character, which is used as a statement separator in many database systems.
The above is only one example; there are in fact hundreds of ways in which other meta-characters can be exploited to execute a variety of harmful commands.
So how do you protect your application against SQL injection? Some steps are outlined below.
- Escape the special characters before you pass it on to the database. You need to figure out all the meta-characters used by your database and escape them, so they are interpreted as regular data and not as control characters.
- Avoid dynamic SQL generation in your code. Use prepared statements instead. When prepared statements are used, the database will not confuse data for control characters.
- Stored Procedures offer some degree of protection, but are not injection-proof either. They are vulnerable if additional parsing is performed on input parameters.
- The application should have only minimal rights to perform operations in the database. It need not have DROP access if your application doesn’t have to drop a table.
- Validate your input. Do this on the server-side as client-side validation cannot be trusted. Write per-field validations using regular expressions that allow only whitelisted characters. Limiting the allowable range of characters and length of each field will reduce the chance of injection.
- I have mentioned escaping data before passing it on to the database. However, many developers perform input escaping. This kind of blanket escaping is generally effective. However, it could create problems if you need to pass data to other subsystems (example: sending an email), which may have different escaping needs. The ideal solution is to perform output escaping before passing data to another subsystem.
- Catch database exceptions and show a custom error page to the user. Show minimal information about the error to the user. Database exceptions carry more information than what the user needs to see – it may contain table and column names. So catch these exceptions in your application and redirect the user to an error page that does not give out much information.
This article gave some tips on how to avoid falling prey to SQL injection attacks. Only the application developer can prevent this type of attack. It cannot be avoided by installing firewalls or SSL certificates. Developers need to think about how to prevent injection attacks when they write code and not as an after thought.
Post a Comment
-
Powered by Sociable!
