SQL Injections: How Not To Get Stuck
May 07, 2007This guest post was written by Thomas Mueller, the author of the open source H2 relational database, an earlier database Hypersonic SQL, and Pointbase Micro.
SQL injection is one of the most common security vulnerabilities for web applications today. Recently a test (see How Prevalent Are SQL Injection Vulnerabilities?) showed how widespread the problem is. This article shows ways to solve the problem, including a new technique called "Disabling Literals", and lists new attacks.
SQL Injection Example
What is SQL injection? You can skip this section if you already know it. We are using Java code and the JDBC API here, but SQL injection is possible in all programming languages, even when using O/R mapping tools. Here is some code from "Insecure Systems Inc.":
stat.executeQuery("SELECT * FROM USERS WHERE " +
"NAME='" + name + "' AND PASSWORD='" + password + "'");
An attacker could use the following password:
' OR ''='
In this case the SQL statement becomes
SELECT * FROM USERS WHERE
NAME='admin' AND PASSWORD='' OR ''=''
This condition is true in every case. If this was the password checking logic, the attacker just broke in.
Prevention with Stored Procedures and Views
One suggestion is to stored procedures to prevent SQL injection. Here we replace the SELECT statement with a stored procedure: GET_USER(userName, password) returning a result set. Now the application may call:
stat.executeQuery(
"CALL GET_USER('" + name + "', '" + password + "')");
Of course one stored procedure is not enough for an application. For changing the password of an existing user there might be another stored procedure: CHANGE_PASSWORD(userName, password), returning the new password. To break in, the attacker only needs to providing the following password:
'||CHANGE_PASSWORD('admin','123')||'
So using stored procedures and/or views by themselves does not guarantee safety from SQL injections. Stored procedures and views have other benefits, for example the ability to change the behavior while the application is running, but they don't solve the problem.
Using Access Rights
Let's say we don't do anything to prevent SQL injection, but use very strict access rights, combined with views and/or stored procedures. The idea is to limit the effects of SQL injections. In the extreme case, it is possible to create a "sandbox" so a potential hacker can do no harm. Creating such a sandbox may have high costs:
-
Setting the correct access rights for tables/columns is not easy.
-
Row based access rights is even harder.
-
Each session needs its own database connection.
-
You need to write most of the business logic in SQL.
-
When using dynamic queries, SQL injection is still possible.
-
There is no way to do it in a database independent way.
You may wonder how you could possibly limit the access to certain rows in table. It is possible but complicated. Take a look at the attached source code (method limitRowAccess) on how to do it. You store the current user id in the database (as a session variable, if your database supports this), then use a view to show only the rows with this id.
Using Placeholders
Lets go back and try to prevent SQL injection, instead of just limiting the effects. A good way to solve the problem is to use placeholders (also called parameters). So here is the code from "Secure Systems Inc.":
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS WHERE " +
"NAME=? AND PASSWORD=?");
prep.setString(1, name);
prep.setString(2, password);
ResultSet rs = prep.executeQuery();
Whatever the user name and the password is, SQL injection is not possible. The attacker has no chance. The problem seems to be solved. So we are done? Not yet. Programmers are lazy, and don't always use placeholders. As you see, the secure code is quite a big longer than the insecure code. People tend to use the most simple solution for a given problem (there are some exceptions, for example excessive use of XML, but that's another story). So if you are a manager, how do you make sure all developers of your system use placeholders? And how do you ensure placeholders are used in existing code: old code that works, nobody wants to read, much less touch (for good reason)?
Disabling Literals
With "disabling literals" the database engine runs in a mode where text and number literals are not allowed as part of SQL statements. Only placeholders are allowed. Currently only the H2 database engine supports this feature. To activate it, execute the SQL statement:
SET ALLOW_LITERALS NONE
The default would mode is ALLOW_LITERALS ALL, and there is also mode to allow only numbers. After disabling literals, the insecure code does not run, instead the database simply throws an exception:
"Literals of this kind are not allowed"
Allowing numbers is not as strict as disabling all kind of literals, and may be seen as a compromise, however other cases of SQL injection are still possible in this mode. Lets say the application allows login based on user id instead of user name. The developer of the company "Half Secure Systems Inc." might write:
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS WHERE " +
"ID=" + id + " AND PASSWORD=?");
prep.setString(1, password);
ResultSet rs = prep.executeQuery();
Here a PreparedStatement is used, but the user id is not set via placeholder for whatever reason. In the database, user IDs are numbers, not text, so this works even when allowing number literals in the database engine. But here is a user id that works always:
1 OR 1=1
In this case, the SQL statement becomes
SELECT * FROM USER WHERE
ID=1 OR 1=1 AND PASSWORD=?
So disabling all kinds of literals is clearly better. With all literals disabled, a working solution is:
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS WHERE " +
"ID=? AND PASSWORD=?");
prep.setInt(1, Integer.parseInt(id));
prep.setString(2, password);
ResultSet rs = prep.executeQuery();
Now number literals are also disabled. Disabling numbers may be a problem in legacy applications where numbers are hardcoded in the application. Let's say we have an ITEMS table, and this code lists all active items:
SELECT NAME FROM ITEMS WHERE ACTIVE=1
This statement is secure (no user input is embedded in the statement), however the database rejects it if number literals are disabled. What to do now? By using another new feature, called Constants. Constants are very common in "regular" programming languages such as Java (static final), but not common in SQL. Constants are very handy, they are descriptive and using them avoids typos. Among SQL databases only the H2 Database Engine currently supports constants. In other databases you could use user defined functions. In H2 constants are set like this:
CREATE CONSTANT TYPE_ACTIVE VALUE 1
The constant needs to be created while value literals are still allowed, of course. By the way, you can also create constants with prefix, like Const.ACTIVE if you like this better. The constants may be used as follows:
SELECT NAME FROM ITEM WHERE ACTIVE=TYPE_ACTIVE
So now we have enforced the use of placeholders and constants, and the problem is solved. Only one database engine (H2) currently supports those features, however. You have two options:
Plan A: Use H2 as your production database engine
Plan B: Use H2 to run unit tests
Plan A may not be your choice, as maybe you don't want to use such a 'young' database for production. But Plan B may be a working solution for those who don't want to wait until their database of choice supports Disabling Literals and Constants. Both features are not patented, and if you tell your database vendor to support them they might do it.
So the SQL injection problem is solved? Not fully, unfortunately.
ORDER BY Injection
A cool feature, not only in web applications, is the ability to sort a list on each column. Sometimes sorting is done in the application tier, or even (using Javascript) on the client side. However sometimes sorting is done in the database. An easy solution (and developers like easy solutions) is to place a link on each column header, and include the column name in the link. Usually 'table tools' do the same. The link to order by 'name' might look like this: list.jsp?order=name. The application then dynamically builds the SQL statement to order by the specified column:
ResultSet rs = stat.executeQuery(
"SELECT ID, NAME FROM ITEMS ORDER BY " + order);
Now, manipulating links is simple. Some people might say: "Oh, what can possibly happen, an attacker can only sort on other columns, no problem". Well, a clever attacker could do other things as well, for example retrieve the admin password. Using the following 'order by column':
CASE WHEN (SELECT PASSWORD FROM USERS WHERE NAME='ADMIN') LIKE 'S%' THEN ID ELSE -ID END
If the id in the resulting list is ordered ascending, the admin password starts with 's'. The attacker can retrieve even an 10 character password after at most 10*64=640 tries. And less than 70 tries, if he uses binary search. If the process is automated this might take at most a few seconds. If literals are disabled the attack would be much, much harder. But SQL Injection is still possible.
Sidenote: Storing only the (cryptographic) hash code of the password would help. For details see the method storePasswordHashWithSalt() in the source code. Many systems don't store the hash code of the password so that they can send you your old password by email. Technically it is not possible to compute the password from a hash code so systems that can give you your password keep the password in plain text or encrypt it in some reversible way. This is not really secure.
Can placeholders be used? In SQL, 'ORDER BY 1' means 'order by the first column'. But unfortunately, most databases don't support placeholders here (ORDER BY ?). A solution is to verify the column name is a valid identifier. Technically, this does not prevent a SQL injection, but prevents attacks. In Java this could be done using:
if (!order.matches("[a-zA-Z0-9_]*")) {
order = "1";
}
Other kinds of SQL Injection
The cases listed above are not the only SQL injection risks. Some creative web developers might put stored procedure names, table names, or even complete SQL statements in links or in hidden fields. Luckily this is much less common. One might be tempted to prohibit dynamic statements (i.e. only allow fixed statements) but when filtering and searching data, it is difficult to avoid dynamic statements. The best protection here is probably code analysis.
In a Nutshell
SQL Injection is possibly the biggest security risk for web applications. Most attacks can be prevented using a technique called 'Disabling Literals', which is currently only available in the H2 database engine.
Links
H2 Database Engine: Disabling Literals to solve SQL Injection
How Prevalent Are SQL Injection Vulnerabilities?
** Source Code**
The code is written for Java and JDBC. It is mostly database independent, but some parts only work with the H2 database.
package org.h2.samples;
import java.io.*;
import java.sql.*;
public class SQLInjection {
Connection conn;
Statement stat;
public static void main(String[] args) throws Exception {
new SQLInjection().run("org.h2.Driver",
"jdbc:h2:test", "sa", "sa");
new SQLInjection().run("org.postgresql.Driver",
"jdbc:postgresql:jpox2", "sa", "sa");
new SQLInjection().run("com.mysql.jdbc.Driver",
"jdbc:mysql://localhost/test", "sa", "sa");
new SQLInjection().run("org.hsqldb.jdbcDriver",
"jdbc:hsqldb:test", "sa", "");
new SQLInjection().run(
"org.apache.derby.jdbc.EmbeddedDriver",
"jdbc:derby:test3;create=true", "sa", "sa");
}
void run(String driver, String url, String user, String password) throws Exception {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
stat = conn.createStatement();
try {
stat.execute("DROP TABLE USERS");
} catch (SQLException e) {
// ignore
}
stat.execute("CREATE TABLE USERS(ID INT PRIMARY KEY, " +
"NAME VARCHAR(255), PASSWORD VARCHAR(255))");
stat.execute("INSERT INTO USERS VALUES(1, 'admin', 'super')");
stat.execute("INSERT INTO USERS VALUES(2, 'guest', '123456')");
stat.execute("INSERT INTO USERS VALUES(3, 'test', 'abc')");
loginByNameInsecure();
if(url.startsWith("jdbc:h2:")) {
loginStoredProcedureInsecure();
limitRowAccess();
}
loginByNameSecure();
if(url.startsWith("jdbc:h2:")) {
stat.execute("SET ALLOW_LITERALS NONE");
stat.execute("SET ALLOW_LITERALS NUMBERS");
stat.execute("SET ALLOW_LITERALS ALL");
}
loginByIdInsecure();
loginByIdSecure();
try {
stat.execute("DROP TABLE ITEMS");
} catch (SQLException e) {
// ignore
}
stat.execute("CREATE TABLE ITEMS(ID INT PRIMARY KEY, " +
"NAME VARCHAR(255), ACTIVE INT)");
stat.execute("INSERT INTO ITEMS VALUES(0, 'XBox', 0)");
stat.execute("INSERT INTO ITEMS VALUES(1, 'XBox 360', 1)");
stat.execute("INSERT INTO ITEMS VALUES(2, 'PlayStation 1', 0)");
stat.execute("INSERT INTO ITEMS VALUES(3, 'PlayStation 2', 1)");
stat.execute("INSERT INTO ITEMS VALUES(4, 'PlayStation 3', 1)");
listActiveItems();
if(url.startsWith("jdbc:h2:")) {
stat.execute("DROP CONSTANT IF EXISTS TYPE_INACTIVE");
stat.execute("DROP CONSTANT IF EXISTS TYPE_ACTIVE");
stat.execute("CREATE CONSTANT TYPE_INACTIVE VALUE 0");
stat.execute("CREATE CONSTANT TYPE_ACTIVE VALUE 1");
listActiveItemsUsingConstants();
}
listItemsSortedInsecure();
listItemsSortedSecure();
if(url.startsWith("jdbc:h2:")) {
storePasswordHashWithSalt();
}
conn.close();
}
void loginByNameInsecure() throws Exception {
System.out.println("Insecure Systems Inc. - login");
String name = input("Name?");
String password = input("Password?");
ResultSet rs = stat.executeQuery("SELECT * FROM USERS WHERE " +
"NAME='" + name + "' AND PASSWORD='" + password + "'");
if (rs.next()) {
System.out.println("Welcome!");
} else {
System.out.println("Access denied!");
}
}
public static ResultSet getUser(Connection conn, String userName, String password) throws Exception {
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS WHERE NAME=? AND PASSWORD=?");
prep.setString(1, userName);
prep.setString(2, password);
return prep.executeQuery();
}
public static String changePassword(Connection conn, String userName, String password) throws Exception {
PreparedStatement prep = conn.prepareStatement(
"UPDATE USERS SET PASSWORD=? WHERE NAME=?");
prep.setString(1, password);
prep.setString(2, userName);
prep.executeUpdate();
return password;
}
void loginStoredProcedureInsecure() throws Exception {
System.out.println("Insecure Systems Inc. - login using a stored procedure");
stat.execute("CREATE ALIAS IF NOT EXISTS " +
"GET_USER FOR \"org.h2.samples.SQLInjection.getUser\"");
stat.execute("CREATE ALIAS IF NOT EXISTS " +
"CHANGE_PASSWORD FOR \"org.h2.samples.SQLInjection.changePassword\"");
String name = input("Name?");
String password = input("Password?");
ResultSet rs = stat.executeQuery(
"CALL GET_USER('" + name + "', '" + password + "')");
if (rs.next()) {
System.out.println("Welcome!");
} else {
System.out.println("Access denied!");
}
}
void loginByNameSecure() throws Exception {
System.out.println("Secure Systems Inc. - login using placeholders");
String name = input("Name?");
String password = input("Password?");
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS WHERE " +
"NAME=? AND PASSWORD=?");
prep.setString(1, name);
prep.setString(2, password);
ResultSet rs = prep.executeQuery();
if (rs.next()) {
System.out.println("Welcome!");
} else {
System.out.println("Access denied!");
}
}
void limitRowAccess() throws Exception {
System.out.println("Secure Systems Inc. - limit row access");
stat.execute("DROP TABLE IF EXISTS SESSION_USER");
stat.execute("CREATE TABLE SESSION_USER(ID INT, USER INT)");
stat.execute("DROP VIEW IF EXISTS MY_USER");
stat.execute("CREATE VIEW MY_USER AS " +
"SELECT U.* FROM SESSION_USER S, USERS U " +
"WHERE S.ID=SESSION_ID() AND S.USER=U.ID");
stat.execute("INSERT INTO SESSION_USER VALUES(SESSION_ID(), 1)");
ResultSet rs = stat.executeQuery("SELECT ID, NAME FROM MY_USER");
while (rs.next()) {
System.out.println(rs.getString(1) + ": " + rs.getString(2));
}
}
void loginByIdInsecure() throws Exception {
System.out.println("Half Secure Systems Inc. - login by id");
String id = input("User ID?");
String password = input("Password?");
try {
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS WHERE " +
"ID=" + id + " AND PASSWORD=?");
prep.setString(1, password);
ResultSet rs = prep.executeQuery();
if (rs.next()) {
System.out.println("Welcome!");
} else {
System.out.println("Access denied!");
}
} catch(SQLException e) {
System.out.println(e);
}
}
void loginByIdSecure() throws Exception {
System.out.println("Secure Systems Inc. - login by id");
String id = input("User ID?");
String password = input("Password?");
try {
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS WHERE " +
"ID=? AND PASSWORD=?");
prep.setInt(1, Integer.parseInt(id));
prep.setString(2, password);
ResultSet rs = prep.executeQuery();
if (rs.next()) {
System.out.println("Welcome!");
} else {
System.out.println("Access denied!");
}
} catch(Exception e) {
System.out.println(e);
}
}
void listActiveItems() throws Exception {
System.out.println("Half Secure Systems Inc. - list active items");
ResultSet rs = stat.executeQuery(
"SELECT NAME FROM ITEMS WHERE ACTIVE=1");
while (rs.next()) {
System.out.println("Name: " + rs.getString(1));
}
}
void listActiveItemsUsingConstants() throws Exception {
System.out.println("Secure Systems Inc. - list active items");
ResultSet rs = stat.executeQuery(
"SELECT NAME FROM ITEMS WHERE ACTIVE=TYPE_ACTIVE");
while (rs.next()) {
System.out.println("Name: " + rs.getString(1));
}
}
void listItemsSortedInsecure() throws Exception {
System.out.println("Insecure Systems Inc. - list items");
String order = input("order (id, name)?");
try {
ResultSet rs = stat.executeQuery(
"SELECT ID, NAME FROM ITEMS ORDER BY " + order);
while (rs.next()) {
System.out.println(rs.getString(1) + ": " + rs.getString(2));
}
} catch(SQLException e) {
System.out.println(e);
}
}
void listItemsSortedSecure() throws Exception {
System.out.println("Secure Systems Inc. - list items");
String order = input("order (id, name)?");
if (!order.matches("[a-zA-Z0-9_]*")) {
order = "id";
}
try {
ResultSet rs = stat.executeQuery(
"SELECT ID, NAME FROM ITEMS ORDER BY " + order);
while (rs.next()) {
System.out.println(rs.getString(1) + ": " + rs.getString(2));
}
} catch(SQLException e) {
System.out.println(e);
}
}
void storePasswordHashWithSalt() throws Exception {
System.out.println("Very Secure Systems Inc. - login");
stat.execute("DROP TABLE IF EXISTS USERS2");
stat.execute("CREATE TABLE USERS2(ID INT PRIMARY KEY, " +
"NAME VARCHAR, SALT BINARY, HASH BINARY)");
stat.execute("INSERT INTO USERS2 VALUES" +
"(1, 'admin', SECURE_RAND(16), NULL)");
stat.execute("DROP CONSTANT IF EXISTS HASH_ITERATIONS");
stat.execute("DROP CONSTANT IF EXISTS HASH_ALGORITHM");
stat.execute("CREATE CONSTANT HASH_ITERATIONS VALUE 100");
stat.execute("CREATE CONSTANT HASH_ALGORITHM VALUE 'SHA256'");
stat.execute("UPDATE USERS2 SET " +
"HASH=HASH(HASH_ALGORITHM, STRINGTOUTF8('abc' || SALT), HASH_ITERATIONS) " +
"WHERE ID=1");
String user = input("user?");
String password = input("password?");
stat.execute("SET ALLOW_LITERALS NONE");
PreparedStatement prep = conn.prepareStatement(
"SELECT * FROM USERS2 WHERE NAME=? AND " +
"HASH=HASH(HASH_ALGORITHM, STRINGTOUTF8(? || SALT), HASH_ITERATIONS)");
prep.setString(1, user);
prep.setString(2, password);
ResultSet rs = prep.executeQuery();
while (rs.next()) {
System.out.println("name: " + rs.getString("NAME"));
System.out.println("salt: " + rs.getString("SALT"));
System.out.println("hash: " + rs.getString("HASH"));
}
stat.execute("SET ALLOW_LITERALS ALL");
}
String input(String prompt) throws Exception {
System.out.print(prompt);
BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
return reader.readLine();
}
}