Introduction
The problem with databases is that they do what we tell them to. Really, that’s so annoying sometimes!
Does that sound like an irrational statement? Unfortunately, it’s not, and that’s because there are malicious folks in the world who would like nothing better than to either steal your data or ruin it. And it’s all because databases will happily execute any legal command you give them — even it the command’s not something you, the developer, intended.
What do I mean?
Let me introduce you to SQL Injection.
What’s SQL Injection?
The Open Web Application Security Project (OWASP) maintains a list of the most pervasive/popular vulnerabilities that afflict applications. Sitting at the very top in the number 1 position is Injection. There are different kinds of injection, and this post concentrates on SQL Injection. We’ll talk about other types later.
What’s SQL Injection?
Let’s say we have a database, and in that database, we have a table called asfg_pi_input02_works. That table stores information about DVDs and Blu Ray disks. If I wanted to list all of the contents in that table, I could use a query like:
SELECT * FROM asfg_pi_input02_works
The title is in a column named api02_name_of_work. If I wanted to show the table row or rows whose title is “Attack on Titan’ (yeah, I like Anime!), I could use a query like:
SELECT * FROM asfg_pi_input02_works WHERE api02_name_of_work = 'Attack on Titan'
So far, so good, right? No nefarious code here! But let’s say that we put a form on the web that prompts a user to enter the title. The web form has a field called search_title. In my code, let’s further say that I take the user’s input and build the query using string concatenation, like this:
String strTitle = request.getParameter("search_title");
String strSQL = "SELECT * FROM asfg_pi_input02_works WHERE api02_name_of_work = '" + strTitle + "' ";
If I enter just “Attack on Titan”, the program will build a query that looks just like the previous query:
SELECT * FROM asfg_pi_input02_works WHERE api02_name_of_work = 'Attack on Titan'
But, what if I’m evil? What if I want to list everything? In other words, what if I enter this string?
Attack on Titan' OR 1 = 1
This is what the query will look like when my innocent and unprotected program uses simple string concatenation to build it:
SELECT * FROM asfg_pi_input02_works WHERE api02_name_of_work = 'Attack on Titan' OR 1 = 1
This query, instead of showing just “Attack on Titan”, will show all of the rows in the table. Why? Because being evil, I intentionally fooled the program by closing the string with a single quote, then adding “OR 1 = 1”. Since 1 always equals 1, that condition is always true. In effect, I’ve told the query engine to show me all of the rows.
This is a table containing entertainment records, so who cares? You should. Because I didn’t have to stop at just “OR 1 = 1”. I would have done something more nefarious, like:
Attack on Titan' ; SHOW TABLES;
Maybe the output of “SHOW TABLES” wouldn’t necessarily get to the browser’s display; or maybe you’re using reflection to automatically interpret the results and write them to the screen? There’s a reason security professionals cringe at the implications of Java’s reflection…
Or what if I start experimenting with Data Description Language (DDL)? Like creating or dropping tables?
Or, what if I attack your login screen with “OR 1 = 1”. If you’re not protecting yourself, that attack would let anyone log in without having an ID.
That could be a huge problem.
Fortunately, protecting your Java application from SQL Injection is really easy.
Parameterized Queries FTW!
The problem goes back to databases doing whatever we tell them to do. So, we have to come up with a safe way to ask them for data. Java provides the perfect mechanism: Parameterized Queries.
Let’s look at an example that runs within a Tomcat application. This example creates a connection to a data source defined in Tomcat’s server.xml:
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/asfg_ro");
Connection conn = ds.getConnection();
Here’s where things start to get interesting. Look at how we define the query:
String strSQL = "SELECT * FROM asfg_pi_input02_works WHERE api02_name_of_work = ? ";
The question mark represents where we’ll put the String that contains the title. Before we do that, we have to prepare the query to recognize the parameter, which is what we call the question mark:
PreparedStatement pstmt = conn.prepareStatement(strSQL);
The code hasn’t executed the query yet. It’s just told the database to expect the query, which has a single parameter. If we assume that the variable strTitle contains the title, this is how we’d associate the String with the parameter:
try
{
pstmt.setString(1, strTitle);
...
Now, we execute the query and walk through the results:
rs = pstmt.executeQuery();
while (rs.next()) {
...
In this scenario, the nasty tricks I tried earlier won’t work. The reason is that we have already “registered” the query with the database’s query engine, so it knows the query’s skeleton. When we pass in the value of a parameter, the query engine doesn’t rebuild the whole query. It just passes in the parameter value for the question mark representing the specific column. The query engine evaluates the string against the specified column, and only that column, so the injection attack won’t work.
Some Implications
Parameterized Queries are a powerful addition to your security toolbox (they can also boost batch INSERT statements if the column list is static). However, they aren’t the only protections, and you should consider using multiple layers of defense to implement security in depth.
For example, consider using white lists for input sanitization. If possible, you could only allow letters and numbers, which would eliminate most of the raw material an attacker would need. This would make entering a string like “‘ OR 1 = 1” impossible, since it could exclude single quotes.
Also, consider limiting the power of the ID you use to query the database. In the example above, you’ll see that I used a JDBC resource named jdbc/asfg_ro. That refers to a database connection defined in Tomcat’s server.xml file. That connection relies on a read-only ID. That’s intentional. Even though I extensively use Parameterized Queries in my applications, there’s a chance that a given version of Tomcat’s implementation might have a flaw. That flaw might allow a SQL Injection attack. I can limit the damage, especially from DDL commands, by using a low-power ID.
The example may have been a Tomcat application, but the concept applies to any Java application using JDBC — even Plain Old Java Objects (POJOs).
Does this make sense? Do you think it’ll be easy to implement in your applications? Let me know why or why not in the comments!
by Terrance A. Crow
Terrance has been writing professionally since the late 1990s — yes, he’s been writing since the last century! Though he started writing about programming techniques and security for Lotus Notes Domino, he went on to write about Microsoft technologies like SQL Server, ActiveX Data Objects, and C#. He now focuses on application security for professional developers because… Well, you’ve watched the news. You know why!
