Module

SQL Injection

Summary

SQL injection is an attack technique that exploits a security vulnerability occurring in the database layer of an application . Hackers use injections to obtain unauthorized access to the underlying data, structure, and DBMS. It is one of the most common web application vulnerabilities.

Description

A Database is the heart of many, if not all, web-applications and is used to store information needed by the application, such as, credit card information, customer demographics, customer orders, client preferences, etc. Consequently, databases have become attractive and very lucrative targets for hackers to hack into. SQL Injections happen when a developer accepts user input that is directly placed into a SQL Statement and doesn’t properly validate and 􀀁lter out dangerous characters. This can allow an attacker to alter SQL statements passed to the database as parameters and enable her to not only steal data from your database, but also modify and delete it.

A database is vulnerable to SQL injections when user input is either incorrectly altered for string literal escape characters embedded in SQL statements or user input is not strongly typed. SQL injection attacks are also known as SQL insertion attacks.

Injection vulnerabilities, such as SQL, LDAP, HTTP header injection and OS command injection, have been ranked number one on the OWASP (Open Web Application Security Project) Top 10 Web application vulnerabilities 2010 and the top 25 Most Dangerous Software Errors 2011.

Risk

SQL injection attacks occur when a web application does not validate values received from a web form, cookie, input parameter, etc., before passing them to SQL queries that will be executed on a database server. This will allow an attacker to manipulate the input so that the data is interpreted as code rather than as data.

SQL injection attack risk is usually very high and the consequences are severe. A successful attack can bypass authentication and authorization to gain full control of the database, steal sensitive data, change users’ passwords, retrieve users’ credential information, add non-existent accounts, drop tables, make illegal 􀀁nancial transactions, and destroy the existing database, and a lot more. The following table summarizes SQL injection examples which result in di􀀂erent types of threats.

Example

As reported, in 2011, the hacker group LulzSec used Security Injection attacks to cause databases to spit out user names and passwords from Websites, including one associated with F.B.I.;s InfraGard program, SonyPictures and NATO’s online bookstore and deface the PBS site.

Example Code (Bad Code)

The following code dynamically constructs and executes a SQL query that searches for items matching a specified name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user.

...
string userName = ctx.getAuthenticatedUserName();
string query = "SELECT * FROM items WHERE owner = '" + userName + "' AND itemname = '" + ItemName.Text + "'";
sda = new SqlDataAdapter(query, conn);
DataTable dt = new DataTable();
sda.fill(dt);
...

The query that this code intends to execute follows:

SELECT * FROM items WHERE owner = <userName> AND itemname = <itemName>;

However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name wiley enters the string:

name' OR 'a'='a

for itemName, then the query becomes the following:

SELECT * FROM items WHERE owner = 'wiley' AND itemname = 'name' OR 'a'='a';

The addition of the:

OR 'a'='a

condition causes the WHERE clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:

SELECT * FROM items;

This simplification of the query allows the attacker to bypass the requirement that the query only return items owned by the authenticated user; the query now returns all entries stored in the items table, regardless of their specified owner.

Addressing SQL Injection

There have been a few types of SQL injection attacks. These include:

• String SQL Injection
• Numeric SQL Injection

In this exercise, we focus on String SQL Injection. However, to guard against the two types of SQL injection attacks, we can use a Parameterized Query. The following example illustrates how we use a parameterized query to prevent a String SQL injection attack.

Consider the following SQL query that intends to be used to verify if a user entered a valid username and password on a login screen/page:

String userName = loginView.getUserName();
String password = loginView.getPassword();
String query = "SELECT * FROM users WHERE username = '" + userName + "' AND password = '" + password + "'";

An SQL inject attack can be performed by entering the following username and password:

String userName = "anyuser' OR '1'='1";

Assume the password is entered as “anypassword”. The SQL query will then be evaluated as:

String query = "SELECT * FROM users WHERE username = 'anyuser' AND password = 'anypassword' OR '1'='1'";
PreparedStatement preparedStatement = connection.prepareStatement(query);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
    // User is logged in
}

Since '1'='1' is always true, the SQL query will return all the records in the users table. If the login logic is written as checking if the query returned anything, and an attacker can log in. This is a serious security issue. To prevent this, we can use a parameterized query as follows:

// the following is a parameterized query where "?" is a placeholder for the parameters
String query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, userName);
preparedStatement.setString(2, password);
ResultSet resultSet = preparedStatement.executeQuery();

This way, the SQL query is not directly constructed using user input, and the database will treat the parameters as data, not as part of the SQL command. This prevents any SQL injection attacks.

Note that using PreparedStatement does not mean that the application is free of SQL injection. We must use a parameterized query for all SQL commands that are constructed using user input.

Acknowledgement

This page is derived from the Security Injection@Towson project.