How to find and prevent SQL injection attack vulnerabilities

If your site uses a SQL server, then it is probably vulnerable to some form of SQL injection. Expert Richard Brain explains how to strengthen database defenses.

How do I know if my site is open to SQL injection attacks?

If your site uses a SQL server, then it is probably vulnerable to some form of SQL injection attack.

In the late 1990s, when Web-connected sites were starting to be tied into corporate databases, implementations were enacted with little regard to security, and managers were probably more than happy if the system just worked. Basic security mistakes were made: A site's input data was often not checked for unexpected characters, and verbose (and occasionally revealing) error messages were displayed to end users.

This allowed the classic error page-based SQL injection attack, which consists of injecting apostrophes (') or double minus (--) characters to insert additional SQL code, modify existing SQL queries within the code and possibly gain access to data and resources.

Modern SQL-based Web-connected sites are more securely designed (though you do come across some evolutionary throwbacks), and they filter for dangerous characters like apostrophes and no longer display error messages that confirm a successful attack. However, as the security of websites has improved over time, so have the attack techniques.

There are various "blind" SQL attack methods, which no longer rely on error page responses to work. Instead, they try to delay the processing of certain SQL statements. Some logical SQL commands, when processed, create perceptible delays. There is for instance a "waitfor delay x" command which will delay the response by x seconds. If xp_cmdshell is enabled, then delays can be created many different ways -- for instance pinging a remote host a number of times.

The attackers' approach is to keep trying until they succeed. If the "waitfor delay 10" command is used, for example, and the injection is unsuccessful, the error page (if used) is immediately displayed. If successful, the error page is displayed after a 10 second wait, showing the attack worked.

It is even possible to get responses from servers using 'out of band' SQL injections, where a database server connects with a client using HTTP or other network function. The response is, for instance, sent to an external DNS server by getting the SQL server to perform a host lookup.

SQL injection attacks can be prevented by not displaying error pages; redirect to the home page instead. Also, only accept certain characters in input areas, and limit the length of those fields. So for the username and password, for example, only accept letters for the alphabet and numbers, and limit the field to 15 characters if possible.

Read more on Application security and coding requirements