This article will describe how to protect your J2EE application from injection (SQL and others) attacks using ESAPI. As with all of the detail articles in this series, if you need a refresher on OWASP or ESAPI, please see the intro article The OWASP Top Ten and ESAPI.
Now to discuss injection. Again, let’s begin the the definition of injection from OWASP:
“Injection flaws, particularly SQL injection, are common in web applications. Injection occurs when user-supplied data is sent to an interpreter as part of a command or query. The attacker’s hostile data tricks the interpreter into executing unintended commands or changing data.”
Here I should make a quick note about “input”. While the vast majority of attacks are usually due to malicious user-supplied input, often directly through the browser, this is not necessary for the attack to work. Dangerous (whether malicious or not) could come from a variety of sources, like the filesystem, or the database, or even other applications. The rule is, if you take in input, always understand it could be dangerous. OK, back to injection …
If you look at injection alongside XSS, you’ll notice that they are very similar types of vulnerabilities in spirit. Consider this working definition: “The application takes in input and without proper data validation or encoding, outputs that data to a destination where certain values or entities have special meaning”. This could be describing XSS or Injection. In the case of XSS, our output destination (we’ll use OWASP’s “interpreter” term from here on out) happens to be a browser. For injection, it could be a database, LDAP server, operating system or any other number of interpreters. Each of the interpreters has its’ own semantics and processing rules for what it understands and what symbols and characters have semantic meaning as input.
For the browser, for instance, the bold tag has semantic meaning, which results in making certain text bold-faced.
As for a SQL compliant database, the keyword SELECT has semantic meaning in that it performs a read of some portion of the database.
SELECT first_name, last_name FROM employees;
As we saw previously in the XSS article, it is possible for a user to insert (inject) data that will cause issues if the input is not properly validated and/or encoded (my strong suggestion is to do both). The same thing is true with injection attacks. If the input is not valid for the interpreter, or more specifically contains some commands that will be processed by the given interpreter, then the user can cause the interpreter to perform functions on the user’s behalf that were not intended to be allowed by the application.
Let’s take a classic SQL injection attack as an example to clarify the issue. Here’s some simple Java code creating a SQL string to perform an authentication check so that a user can login. Let’s assume the Java code gets the results back and tests to see if there are any results, and if so, allows the user to be authenticated.
String sqlString = "SELECT * FROM users WHERE fullname = '" + form.getFullName() + "' AND password = '" + form.getPassword() + "'";
So, let’s assume the user is presented with a simple login screen, and let’s also assume the user is benevolent. The user might enter something like John Melton for the username and 123pass for the password. That would create the following SQL string to be executed against the database after it is processed by Java (ie. this is what you would see as the SQL string if you watched it through the debugger, or printed it out after the values have been populated at runtime).
SELECT * FROM users WHERE username = 'John Melton' AND password = '123pass'
OK, so far, everything is great right? Sure, for those specific inputs no problem. Now, to pay homage to a good friend of mine, I’ll include his oft-quoted refrain – “The web wasn’t made for Irishmen”.
Let me explain that. For those who are unaware, many Irishmen have names like O’Brien or O’Connor. Note the apostrophe in the last name. Now, let’s show what the SQL string looks like after being processed now.
SELECT * FROM users WHERE username = 'Paul O'Malley' AND password = '789pass'
Are we still good? Nope, this time, we’re not so lucky. What will happen here is a SQLException will be thrown because the SQL cannot be processed by the database interpreter. The issue is in the extra apostrophe in the name. The apostrophe is used in SQL, among other reasons, to open and close a string representation. So, what the SQL interpreter sees in this case is that the username string is populated with Paul O and then closed, and the actual SQL string starts again, only <b> is not valid SQL syntax, so processing stops and an exception is thrown.
(Note: I’m using interpreter generically. In the cases I’m describing, it actually would get booted by the parser, but that’s quibbling :>. )
All right, now that we’ve covered the basic “it works” case and also seen there is a way to break it, how can we break it to our advantage if we wanted to attack the site? Let’s say an attacker sends these two crafted inputs.
Full Name: blah blah Password: ' OR '1' = '1
Update: modified code above according to Ben’s comment below.
Now that’s a funny looking string to be entered in the password field – let’s see what the SQL string produced looks like.
SELECT * FROM users WHERE username = 'blah blah' AND password = ' ' OR '1' = '1'
What the above SQL actually does is returns all records from the users table that use the given username and password OR where 1 = 1, which is … EVERY RECORD!!!. This grants the attacker access to the application as an authenticated user. This was an actual issue in a real (fairly well-known at the time) web application many years ago. It would check the results that were returned and take the first one as the user to log in. In the case where they were being attacked and all records were returned, it took the first result, which like most other systems first user, was the admin account! Most decent sites have closed this issue on the login screen thankfully, but it is a very prevalent issue in systems across the world.
The above is a simple example of what can happen with SQL injection. The vast majority of reported issues with injection have to do with SQL injection. The others are not as popular, probably because they’re less common types of systems (b/c almost every app uses a DB) and SQL injection is so easy and prevalent, there’s no real driver for attackers to do anything else. Additionally, most of the sensitive data is likely stored in the DB anyhow, so we might as well attack it directly if possible.
So, now that we see the problem, how do we fix it? Well there are essentially 2 reasonably reliable techniques as I see it. The first option has to do with using PreparedStatements with bound parameters, and the second option uses the ESAPI framework.
Update: From Jeff’s (the chair of OWASP) comment below, it should be clear that PreparedStatements and the ESAPI output encoding mechanisms are not mutually exclusive. They can be complementary and used together for increased assurance. It is a task left up to the development organization securing their app to make a decision over which option (or both) to choose. As with most choices, each option (PS, ESAPI, or both) has its’ advantages and disadvantages.
Note: with either technique, white-list input validation should be used since it’s relatively easy to do, and helps avoid so many types of problems. Additionally, standard security measures like least privilege for the user account connecting to the DB for your application should also be utilized.
Before getting into the detail of the 2 options, I want to point out that the ESAPI folks have put together another article called the SQL Injection Prevention Cheat Sheet and it is a great resource about this topic, specifically SQL injection.
PreparedStatements (with bound parameters)
Those familiar with DB programming in java will recognize the 3 statement types in Java: Statement, PreparedStatement and CallableStatement. These 3 types represent varying levels of security. The standard Statement class is not a good idea – there are no security measures built-in and you are on your own if you try and use this class. As for the CallableStatement, it is used with stored procedures and functions. *Typically* stored procs are secure. However, it is very possible to make them insecure by not validating the input properly or even constructing dynamic sql within the procedure for execution. Finally, there is the PreparedStatement. When used properly (with a safe driver and bound variables), these are typically viewed as the most secure option.
There are 2 important things to remember about PreparedStatements when it comes to security.
1. SQL executed with PreparedStatements is only as safe as the drivers that implement the classes. The actual runtime class that implements the PreparedStatement interface is part of a 3rd party package. There are some out there that are generally thought to be safe, and some that aren’t. The most popular ones expectedly tend to be more respected (not necessarily earned, but true nonetheless). The only suggestion I can give here is that you can look at the code of popular open source drivers yourself and make up your mind about their safety. If you use a commercial driver, understand the support structure in case there is a security issue that must be resolved.
2. The code leveraging the PreparedStatements must use them properly. This involves the use of strongly typed bound parameters. This means that if you concatenate your SQL string together using dynamic input, the PreparedStatement does you no good. The safety comes into play when using the setXXX methods on the PreparedStatement class. The snippet below shows how to properly use dynamic input (which should already be validated) as input to the SQL string by doing parameter substitution. The setInt method below (if the driver is coded properly) should be DB specific and will escape any necessary characters for that DB to prevent breaking out of the interpreter and allowing the user to specify code that will be run by the DB.
myPrepStmt = conn.prepareStatement("SELECT name FROM users WHERE id = ?"); myPrepStmt.setInt(1, validatedUserId); ... //execute statement and get results
and a query with multiple input parameters looks very similar …
myPrepStmt = conn.prepareStatement ("SELECT name FROM users WHERE id = ? AND date_created >= ?"); myPrepStmt.setInt(1, validatedUserId); //first param myPrepStmt.setDate(2, validatedStartDate); //second param ... //execute statement and get results
As you can see, using the PreparedStatement can solve this issue, but only when used properly. One very important thing to note here is about ORM frameworks. Most of these frameworks *can* be used securely, but you have to know what you’re doing. Hibernate, for instance, can be used securely or insecurely depending on which classes you choose. Be sure to do your homework, read solid tutorials, pick good frameworks, and above all understand what you’re using when you let a framework do some of the work for you. OK, now on to our other option …
ESAPI takes a similar approach to solving this issue, but the code will look slightly different. The approach is very simple, and boils down to escaping/encoding all dynamic input to the SQL statement using an appropriate encoder. For this section of the article, I relied heavily on the ESAPI section of the SQL Injection Prevention Cheat Sheet at OWASP, so it’s a worthwhile time to go read it if you haven’t already. Let’s look at one quick example to show how it works. We’ll modify the second query above to use ESAPI instead of PreparedStatements.
//ESAPI version of query Codec ORACLE_CODEC = new OracleCodec(); //we're using oracle String query = "SELECT name FROM users WHERE id = " + ESAPI.encoder().encodeForSQL( ORACLE_CODEC, validatedUserId) + " AND date_created >= '" + ESAPI.encoder().encodeForSQL( ORACLE_CODEC, validatedStartDate) +"'"; myStmt = conn.createStatement(query); ... //execute statement and get results
As you can see above, all that we did was encoded the input data (already validated) for SQL by using the OracleCodec, since we are using the Oracle database for our application. This has some obvious drawbacks if you are using multiple databases, and can use the ORM features of cross-database query generation, but that is rare for many apps. Most applications know the 1 database they are going to be running on, and I would argue that’s a good thing from a security perspective because then you can better understand the security ins and outs of that one DB.
Looking at the code, it’s fairly simple to encode most anything – you just have to be diligent to do so. As for the other types of injection, you may have to get your hands dirty and write an encoder for your specific application, or you may not. You’ll just have to check what the ESAPI team has already done. Additionally, it might be possible to get on the mailing list and request a new codec. Certainly, if you have any that you’ve written, I’m sure the dev team would love to at least see them, and possibly include them – you could be helping others out!.
Clearly, using the ESAPI framework, it’s possible to safely and securely prevent injection from the DB as well as other types of interpreters if you use the framework properly.
In summary, I’ll say that while I think either of these techniques work from a purely technical perspective, there are a couple of things you should consider before implementing protection. First, PreparedStatements are far more prevalent in industry. They are a solid solution to the SQL injection problem, and are widely understood and available. They’ve also been very widely tested both for performance and security. Essentially, they are a known entity. However, they do not solve any of the other types of injection. The equivalent to PreparedStatements do not exist for XML or XPath injection or others. You’d have to handle them differently. As for ESAPI, it will also work well, and has been tested for security and performance, but not nearly as much. Additionally, it does not have the industry reach that PreparedStatements do. However, ESAPI has a solution for the other types of injection as well, and a clear model for implementing new encoding mechanisms for new interpreter types. You can even write your own custom encoders if you choose. No matter which you choose (or both), my hope is that you’ve seen there is a need to be responsible and protect resources from injection. These solutions make it fairly trivial to implement solid protection for applications against injection.
Other articles in this series:
Part 0: The OWASP Top Ten and ESAPI
Part 1: The OWASP Top Ten and ESAPI – Part 1 – Cross Site Scripting (XSS)
Part 2: The OWASP Top Ten and ESAPI – Part 2 – Injection Flaws
Part 3: The OWASP Top Ten and ESAPI – Part 3 – Malicious File Execution
Part 4: The OWASP Top Ten and ESAPI – Part 4 – Insecure Direct Object Reference
Part 5: The OWASP Top Ten and ESAPI – Part 5 – Cross Site Request Forgery (CSRF)
Part 6: The OWASP Top Ten and ESAPI – Part 6 – Information Leakage and Improper Error Handling
Part 7: The OWASP Top Ten and ESAPI – Part 7 – Broken Authentication and Session Management
Part 8: The OWASP Top Ten and ESAPI – Part 8 – Insecure Cryptographic Storage
Part 9: The OWASP Top Ten and ESAPI – Part 9 – Insecure Communications
Part 10: The OWASP Top Ten and ESAPI – Part 10 – Failure to Restrict URL Access
Update: Added inline comments referencing changes made regarding Jeff and Ben’s comments below.