Protecting Against SQL Injection

SQL injection is a type of injection attack. Injection attacks occur when maliciously crafted inputs are submitted by an attacker, causing an application to perform an unintended action. Because of the ubiquity of SQL databases, SQL injection is one of the most common types of attack on the internet.

If you only have time to protect yourself against one vulnerability, you should be checking for SQL injection vulnerabilities in your codebase!

Risks

Prevalence
Occasional
Rating prevelance on Rating prevelance on Rating prevelance on
Exploitability
Easy
Rating exploitability on Rating exploitability on Rating exploitability on
Impact
Devastating
Rating impact on Rating impact on Rating impact on

What’s the worst thing that could happen when you suffer a SQL injection attack?

Our example hack showed you how to bypass the login page: a huge security flaw for a banking site. More complex attacks will allow an attacker to run arbitrary statements on the database. In the past, hackers have used injection attacks to:

  • Extract sensitive information, like Social Security numbers, or credit card details.
  • Enumerate the authentication details of users registered on a website, so these logins can be used in attacks on other sites.
  • Delete data or drop tables, corrupting the database, and making the website unusable.
  • Inject further malicious code to be executed when users visit the site.

SQL injection attacks are astonishingly common. Major companies like Yahoo and Sony have had their applications compromised. In other cases, hacker groups targeted specific applications or wrote scripts intended to harvest authentication details. Not even security firms are immune!

Protection

So SQL Injection is a serious risk. How can you protect yourself?

Parameterized Statements

Programming languages talk to SQL databases using database drivers. A driver allows an application to construct and run SQL statements against a database, extracting and manipulating data as needed. Parameterized statements make sure that the parameters (i.e. inputs) passed into SQL statements are treated in a safe manner.

For example, a secure way of running a SQL query in JDBC using a parameterized statement would be:


// Define which user we want to find.
String email = "user@email.com";

// Connect to the database.
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Statement stmt = conn.createStatement();

// Construct the SQL statement we want to run, specifying the parameter.
String sql = "SELECT * FROM users WHERE email = ?";

// Run the query, passing the 'email' parameter value...
ResultSet results = stmt.executeQuery(sql, email);

while (results.next()) {
  // ...do something with the data returned.
}

Contrast this to explicit construction of the SQL string, which is very, very dangerous:


// The user we want to find.
String email = "user@email.com";

// Connect to the database.
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Statement stmt = conn.createStatement();

// Bad, bad news! Don't construct the query with string concatenation.
String sql = "SELECT * FROM users WHERE email = '" + email + "'";

// I have a bad feeling about this...
ResultSet results = stmt.executeQuery(sql);

while (results.next()) {
  // ...oh look, we got hacked.
}

The key difference is the data being passed to the executeQuery(...) method. In the first case, the parameterized string and the parameters are passed to the database separately, which allows the driver to correctly interpret them. In the second case, the full SQL statement is constructed before the driver is invoked, meaning we are vulnerable to maliciously crafted parameters.

You should always use parameterized statements where available, they are your number one protection against SQL injection.

You can see more examples of parameterized statements in various languages in the code samples below.

Object Relational Mapping

Many development teams prefer to use Object Relational Mapping (ORM) frameworks to make the translation of SQL result sets into code objects more seamless. ORM tools often mean developers will rarely have to write SQL statements in their code – and these tools thankfully use parameterized statements under the hood.

The most well-known ORM is probably Ruby on Rails’ Active Record framework. Fetching data from the database using Active Record looks like this:


def current_user(email)
  # The 'User' object is an Active Record object, that has find methods 
  # auto-magically generated by Rails.
  User.find_by_email(email)
end

Code like this is safe from SQL Injection attacks.

Using an ORM does not automatically make you immune to SQL injection, however. Many ORM frameworks allow you to construct SQL statements, or fragments of SQL statements, when more complex operations need to be performed on the database. For example, the following Ruby code is vulnerable to injection attacks:


def current_user(email)
  # This code would be vulnerable to a maliciously crafted email parameter.
  User.where("email = '" + email + "'")
end

As a general rule of thumb: if you find yourself writing SQL statements by concatenating strings, think very carefully about what you are doing.

Escaping Inputs

If you are unable to use parameterized statements or a library that writes SQL for you, the next best approach is to ensure proper escaping of special string characters in input parameters.

Injection attacks often rely on the attacker being able to craft an input that will prematurely close the argument string in which they appear in the SQL statement. (This is why you you will often see ' or " characters in attempted SQL injection attacks.)

Programming languages have standard ways to describe strings containing quotes within them – SQL is no different in this respect. Typically, doubling up the quote character – replacing ' with '' – means “treat this quote as part of the string, not the end of the string”.

Escaping symbol characters is a simple way to protect against most SQL injection attacks, and many languages have standard functions to achieve this. There are a couple of drawbacks to this approach, however:

  • You need to be very careful to escape characters everywhere in your codebase where an SQL statement is constructed.
  • Not all injection attacks rely on abuse of quote characters. For example, when an numeric ID is expected in a SQL statement, quote characters are not required. The following code is still vulnerable to injection attacks, no matter how much you play around with quote characters:

def current_user(id)
  User.where("id = " + id)
end

Sanitizing Inputs

Sanitizing inputs is a good practice for all applications. In our example hack, the user supplied a password as ' or 1=1--, which looks pretty suspicious as a password choice.

Developers should always make an effort to reject inputs that look suspicious out of hand, while taking care not to accidentally punish legitimate users. For instance, your application may clean parameters supplied in GET and POST requests in the following ways:

  • Check that supplied fields like email addresses match a regular expression.
  • Ensure that numeric or alphanumeric fields do not contain symbol characters.
  • Reject (or strip) out whitespace and new line characters where they are not appropriate.

Client-side validation (i.e. in JavaScript) is useful for giving the user immediate feedback when filling out a form, but is no defense against a serious hacker. Most hack attempts are performed using scripts, rather than the browser itself.

Code Samples

The code samples below illustrate good and bad practices when trying to protect against SQL injection.

DB 2.0 API

# SQL and parameter is sent off separately to the database driver.
cursor.execute("select user_id, user_name from users where email = ?", email)

for row in cursor.fetchall():
  print row.user_id, row.user_name


# String concatenation is vulnerable.
cursor.execute("select user_id, user_name from users where email = '%s'" % email)

for row in cursor.fetchall():
  print row.user_id, row.user_name

Django

# Fetch using a user using native ORM syntax, good.
Users.objects.filter(email=email)


# Fetch a user using raw SQL, also safe.
Users.objects.raw("select * from users where email = %s", [email])


# Liable to get hacked.
Users.objects.raw("select * from users where email = '%s'" % email)

Active Record

def current_user(email)
   User.find_by_email(email)
end


def current_user(email)
  User.where("email = '" + email + "'")
end

Sequel

def current_user(email)
  User.where(:email=>email)
end


def current_user(email)
  User.where("email = #{params[:email]}")
end

JDBC

// The user we want to find, usually passed into the method.
String email = "user@email.com";

// Connect to the database.
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Statement stmt = conn.createStatement();

// Construct the SQL statement we want to run, specifying the parameter.
String sql = "SELECT * FROM users WHERE email = ?";

// Run the query, passing the 'email' parameter value...
ResultSet results = stmt.executeQuery(sql, email);

while (results.next()) {
  // ...do something with the data returned. 
}


// The user we want to find.
String email = "user@email.com";

// Connect to the database.
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Statement stmt = conn.createStatement();

// Bad, bad news! Don't construct the query with string concatenation.
String sql = "SELECT * FROM users WHERE email = '" + email + "'";

// I have a bad feeling about this...
ResultSet results = stmt.executeQuery(sql);

while (results.next()) {
  // ...oh look, we got hacked. 
}

Hibernate

@Entity
public class User {
  @Id
  @GeneratedValue
  Long id;

  @NaturalId
  String email;
}

// ORM will ensure safe passing of the 'email' parameter.
return session.bySimpleNaturalId(User.class).load(email);

Spring

public Customer findUserByEmail(String email){
  String sql = "select * from users where email = ?";

  User user = (User) getJdbcTemplate().queryForObject(
    sql,                    // SQL statement...
    new Object[] { email }, // ...separate from parameters.
    new UserRowMapper());

  return user;
}

SqlClient

// Create the SQL command.
SqlCommand command = new SqlCommand("select * from Users where email = @email", conn);

// Add the parameter values in separately.
command.Parameters.Add(new SqlParameter("email", email);

using (SqlDataReader reader = command.ExecuteReader())
{
  while (reader.Read())
  {
    // Do something with the retrieved data.
  }
}

LINQ

using (ServiceContext ctx = new ServiceContext(...))
{
  // LINQ will ensure safe passing of parameters.
  var users = from user in ctx.Users
             where user.email equals email
            select user;

  foreach (var user in users)
  {
    // Do something with the retrieved data
  }
}

node-sql

var sql = require('sql');

// Queries are constructed as parameterized by default.
var query = user.select(user.star()))
                .from(user)
                .where(
                   user.email.equals(email)
                 ).toQuery();
mysql

var mysql = require('mysql');

var connection = mysql.createConnection({
  host     : HOST,
  user     : USERNAME,
  password : PASSWORD
});

connection.connect();

// Query and parameters passed separately.
connection.query(
  'select * from users where email = ?',
  [email],
  function(err, rows, fields) {
    // Do something with the retrieved data.
  });

connection.end();

pg

var pg = require('pg');

var connection = "postgres://username:password@localhost/database";

var client = new pg.Client(connection);

// Query and parameters passed separately.
client.connect(function(err) {
  client.query(
    'select * from users where email = ?',
    [email],
    function(err, result) {
      // Do something with the retrieved data.
    });
  });

client.end();


$statement = $dbh->prepare("select * from users where email = ?");
$statement->execute(array(email));

Other Considerations

Principle of Least Privilege

Applications should ensure that each process or software component can access and affect only the resources it needs. Apply “levels of clearance” as appropriate, in the same way that only certain bank employees have access to the vault. Applying restricted privileges can help mitigate a lot of the risk around injection attacks.

It is rarely necessary for applications to change the structure of the database at run-time – typically tables are created, dropped, and modified during release windows, with temporarily elevated permissions. Therefore, it is good practice to reduce the permissions of the application at runtime, so it can at most edit data, but not change table structures. In a SQL database, this means making sure your production accounts can only execute DML statements, not DDL statements.

With complex database designs, it can be worth making these permissions even more fine-grained. Many processes can be permissioned to perform data edits only through stored procedures, or to execute with read-only permissions.

Sensibly designing access management in this way can provide a vital second line of defense. No matter how the attacker gets access to your system, it can mitigate the type of damage they can possibly do.

Password Hashing

Our example hack relied on the fact that the password was stored as plain-text in the database. In fact, storing unencrypted passwords is a major security flaw in itself. Applications should store user passwords as strong, one-way hashes, preferably salted. This mitigates the risk of malicious users stealing credentials, or impersonating other users.

Third Party Authentication

As a final note, it is often worth considering out-sourcing the authentication workflow of your application entirely. Facebook, Twitter, and Google all provide mature OAuth APIs, which can be used to let users log into your website using their existing accounts on those systems. This saves you as an application developer from rolling your own authentication, and assures your users that their passwords are only stored in a single location.

Further Reading

Relevant XKCD.

Is your website vulnerable to SQL Injection?
Netsparker n
Check today. Scan your website for SQL Injection and other vulnerabilities with Netsparker Web Application Security Scanner.