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

Exploitability

Easy

Impact

Devastating

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:

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:

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:

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:

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

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.