Problem solve Get help with specific problems with your technologies, process and projects.

How to prevent SQL injection attacks by validating user input

Expert Michael Cobb discusses how to prevent SQL injection attacks by validating user input and utilizing parameterized stored procedures.

My company is concerned about SQL injection attacks through the user input fields on our website. Can you suggest...

any defense methods?

Ask the Expert expert Michael Cobb is standing by to answer your questions about enterprise application security and platform security. Submit your question via email. (All questions are anonymous.)

SQL injection is an attack technique that takes advantage of Web applications that don't correctly validate user-supplied requests before passing them to the associated back-end database. Using normal request channels such as form data, cookies, scripts and URLs, hackers can pass malicious SQL queries and commands to a database if they are not thoroughly checked first. Masquerading as legitimate application requests, they can bypass traditional perimeter security defenses and dupe the database system into running code that outputs sensitive information or otherwise compromises the server.

In terms of how to prevent SQL injection attacks, Web applications must be protected by a variety of defenses, including parameterized stored procedures. Instead of constructing SQL commands on the fly with values a user supplies directly, requests to the database are made using type-safe parameters and defined subroutines. Also, if user access to the database is only ever permitted via stored procedures, permission for users to directly access data doesn't need to be explicitly granted on any database table.

Even when using parameterized stored procedures, Web applications should still validate and sanitize all data inputs, whether users or authenticated customers supply them or they are read from a cookie. This means checking to ensure the data is of the correct type, length and format within an expected range. User input validation should take place on a trusted server, not on the client. Hackers can bypass blacklisting filters for known unsafe characters -- like < and / -- by using methods such as character encoding, so utilize whitelists to only accept known safe input. Only once validation has occurred should inputs be processed or passed on to the database.

There are a variety of tools available to test for SQL injection vulnerabilities in a Web application. Microsoft's Source Code Analyzer for SQL Injection is ideal for ASP-based applications, while the free edition of Acunetix's Web Vulnerability Scanner checks Web applications for various vulnerabilities, including SQL Injection and cross-site scripting (XSS). Security Compass has developed a suite of Firefox add-ons for testing Web application security, including SQL Inject-Me for SQL Injection vulnerabilities. Though they aren't automated tools, these Firefox add-ons are easy to install and use and appear in the Firefox Tools menu. Google offers a Web application security reconnaissance tool called skipfish, which generates an interactive site map annotated with the output from a number of active security checks for vulnerabilities, including SQL injection. This provides a useful review of all points that allow a user to connect to an application. Organizations should ensure that developers are given the necessary time to run such tests and implement fixes.

SQL injection attacks usually involve a lot of trial and error as evidenced in database and network logs. Thus, routine log monitoring is another essential security control. For example, SQL statements that are far larger than normal are a probable sign of an attempted SQL injection attack. Database servers should be hardened with all unnecessary services removed; always adhere to the principle of least privilege for database accounts. Depending on your security budget, deploy a database firewall that features SQL grammar analysis. Once all of these measures have been taken, perform a penetration test against your entire application and its defenses to see if the live application is still vulnerable to SQL injection and other attacks.

This was last published in April 2013

Dig Deeper on Application attacks (buffer overflows, cross-site scripting)