A Guide to Combatting SQL Injection Attacks

SQL injection attacks are among the oldest exploits against web applications, dating back more than a decade. Sadly, despite the fact that it is fairly easy to defend against these injection attacks, they remain one of the most common categories of vulnerability found in web applications today. The Open Web Application Security Project maintains a listing of the top 10 web application vulnerabilities. In its 2017 ranking, this classic attack still tops the chart as the greatest security risk to modern web applications.

MORE FROM BIZTECH: Don't let these cybersecurity myths stifle business growth.

What Is a SQL Injection Attack?

SQL injection attacks occur when an attacker sends maliciously structured commands to a database-driven web application in an attempt to get the application to pass them on to the database for execution.

For example, consider a web application that asks a user for a search string and then uses that user-provided input to perform a lookup against a product catalog. The web application might insert the user’s search query into a SQL command that looks something like this:

SELECT *

FROM products

WHERE product_name = 'user_search_string'

If the user provides a regular search query, like “cat book”, the query would be processed as:

SELECT *

FROM products

WHERE product_name = 'cat book'

However, if the user provides a more malicious input, such as:

cat book'; DROP TABLE products;--

that may also be plugged into the template, resulting in this query:

SELECT *

FROM products

WHERE product_name = 'cat book'; DROP TABLE products;--'

After a little reformatting, we can see that this is actually two queries: one to retrieve information from the database and a second to destroy the product catalog. 

SELECT *

FROM products

WHERE product_name = 'cat book'; 


DROP TABLE products;

--'

If the database and application aren’t properly secured, this SQL injection example could have devastating effects.

The SQL Injection Cheat Sheet: Preventing an Attack

There are three main ways that organizations can protect themselves against SQL injection attacks: input validation, parameterized queries and access controls. Let’s explore each one of these.

1. Input validation is the primary defense at the application layer. When a web application requests user input, it should carefully examine that input to ensure that it matches the expected data type and length. For example, a field that requests the age of a person should generally expect an integer answer ranging from 0 to 120 or so. Any other input type should be rejected. 

In addition to performing this type of bounds checking, input validation should also watch for the tell-tale signs of injection attacks, such as the use of a single apostrophe in an input string. As you design your input validation routines, you should also take care to note that there are many other variants of SQL injection attacks other than the few that we’ve discussed in this article. You may wish to explore this SQL injection cheat sheet for more examples.

2. Parameterized queries also help protect against SQL injection attacks by adding a control at the database level. Instead of allowing applications to perform arbitrary queries against a database, the database provides a function template that the application may access by filling in predefined details. For example, the product query above might be handled by a database stored procedure that looks something like this:

sp_productQuery('product_name')

The stored procedure then prevents any other command from executing, strictly limiting the application’s ability to access the database.

3. Database administrators, finally, should design access controls that enforce the principle of least privilege. Accounts used to support application queries should have as small a set of database permissions as necessary to carry out their intended functions. For example, the account supporting the product catalog application described above should have read-only access to the product table. This would prevent the application from passing along a query that exceeds its authorized access, such as the DROP TABLE command.

SQL injection attacks have plagued web applications for more than a decade. By taking these three precautions, database administrations and application developers can prevent their environment from becoming the next victim.