Discussing SQL Injection Attacks

Introduction

‘);

These three characters have caused much pain, both emotional and economical, to technical and business professionals alike. If you have been anywhere near a database of any kind, you have probably heard of a technical vulnerability known as a SQL Injection attack. This type of attack preys upon the lack of security measures in an application. In other words, it exploits bad security and programming practices in database programming.

Despite the hopes of many, SQL Injections are not a thing of the past. They still are a major security risk for applications connected to a database. Luckily for developers, there are some easy things we can implement to counter these kinds of attacks! When considering the importance of cyber security, conducting safe database transactions is paramount.

unsplash-logoMarkus Spiske

The Problem

Before we look at how to prevent them, we should look at a bad example of code that would allow a SQL Injection attack. Furthermore, we can examine how a hacker could exploit its vulnerabilities.

String queryDB = “SELECT price FROM products WHERE product_name = “ + request.getParameter(“productName”);  
try {  
    Statement stmt = connection.createStatement( … );
    ResultSet results = stmt.executeQuery(queryDB);
}

The main issue here is that the variable productName is never validated before being added to the queryDB string. Because of this, a hacker could insert malicious SQL code at their will.

A common way that a hacker could exploit this is using the three characters I mentioned earlier. An excellent StackOverflow post summarizes this in detail. Essentially, if I were to continue the previous example, consider what would happen to the database if the variable productName differed from “predicted” user input.

If a hacker were able to make productName equal to this string: ‘; DROP TABLE products; --

Then the statement sent to the database would be as follows:
SELECT price FROM products WHERE product_name = ’; DROP TABLE products; --

Let’s just say that you don’t want to be the DBA for the company with this database configuration. You would probably be receiving a phone call in the middle of the night! The business, security, and technical ramifications of SQL Injections cannot be understated—this kind of oversight in database security is dangerous. In this example along, the hacker only chose to delete the products table.

Essentially, this is what is going on in that modified productName string:

  • – this makes the preceding SQL statement a malformed query, rending it unexecutable
  • ; -- this ends the preceding SQL statement, as in the rest of the string would be considered a new SQL query
  • DROP TABLE products; -- this new, added SQL statement basically deletes the entire products table from the database
  • -- -- this makes any possible portions after the variable of the statement that are in the back-end code (such as Java or PHP) unexecutable, as they would be turned into a SQL comment

Solutions & Discussion

Thankfully, there are ways to defend your database against these attacks. As a developer or database administrator of an application, it is imperative that some of these techniques be used.

Prepared Statements & Parameterized Queries

One method that should always be used when forming a SQL query in code is to bind variables into parameterized statements. Essentially, these type of statements make it harder for attackers to change what your query is trying to do, even if the attacker attempts to insert SQL statements. A traditional example in Java is as follows:

String name = request.getParameter(“username”);  
String query = “SELECT hashed_password FROM Users WHERE username = ? “;  
PreparedStatement p = connection.preparestatement(query);  
p.setString(1, name);  
ResultSet results = p.executeQuery();  

In this case, if the hacker were to enter in for the field username the following string: “dbadmin’ or ‘1’=’1”, this query would look for a username matching the entire string of “dbadmin’ or ‘1’=’1”. Prepared statements were one of the more effective solutions to SQL injections and thus should always be used. They can be found in nearly all programming languages’ SQL support and ORM (object-relational mapping) libraries. This method is the absolute start for combating SQL Injection attacks!

Stored Procedures

Many stored procedures for popular Database Management Systems provide similar protection offered by Parameterized Statements. All developers need to do is provide the parameters in code and call the stored procedure. The parameters are automatically inserted into the SQL statements almost the same way—the only difference being that the SQL statements are stored in the database. Provided below is a Java example of calling a stored procedure with emphasis on parameterization:

String name = request.getParameter(“username”);  
Try {  
    CallableStatement cs = connection.prepareCall(“{call calculatePrice(?)}”;
    cs.setString(1, name);
    ResultSet results = cs.executeQuery();
} catch (SQLException e) {}

Obviously there are some risks and performance considerations involved with stored procedures, but that is beyond the scope of this post.

Input Validation

Never, ever, ever take in user input into a query field without validating it! There are many ways to do this and they range from simple to incredibly thorough algorithms. At the end of the day, you want to ensure that the user’s input can never be interpreted as self-executable SQL code and that all the input is checked before hitting the database. This can be done by forming business rules and logic in your back-end code. For example, below is some code that forces predictable SQL behavior in a query:

boolean sortOrder = validateMethod()

String query = "SELECT * FROM Employees Order by Salary " + (sortOrder ? "ASC" : "DESC");  

This is merely an example of validating input. In any system, it is a best practice to apply effective input validation in conjunction to parameterized queries, character escaping, and a slew of other methods you can find online.

Conclusion

SQL Injections are so notorious and well-known that there is a plethora of information about them online. The Open Web Application Security Project (OWASP), a reputable source on best practices for cyber security, maintains a useful cheat sheet on tips to prevent them. There is even a website that keeps track of reported and documented SQL Injection attacks, and the Wikipedia entry on SQL Injection has a list of some well-known attacks. SQL Injection, and cyber security as whole, is a much wider topic that I will be discussing in future posts, so stay tuned!