Tuesday, March 26, 2013

Basics: Avoiding SQL Injection

SQL injection is a pretty big deal. The attack is easy to carry out, the vulnerabilities are prevalent and the payoff is potentially large. Many of the password breaches reported over the last year or two are known or thought to have been carried out via SQL injection.

SQL injection attacks allow
attackers to execute arbitrary queries or commands against a database.
Developers introduce the vulnerabilities into their code when they concatenate or
substitute user input into the elements of a SQL query. In the
following Python example, the program will accept any input as the "user_id"
variable (returned as a part of login_data) and tack it onto the end of a string that is subsequently executed as
a sql query:

If the user enters "carlos",
this query will select the record with the ID "carlos". If the
user enters ' or 1=1 the query will return all the rows in the USERS
table. If he adds on ;DROP TABLE USERS it will delete the USERS
table. This attack is possible because the executable SQL statement
and the data portion of the query are both strings and developers mix them
together so that the SQL server has no way to tell them apart.

Preventing SQL
Injection

One of the primary methods for
preventing SQL injection is to use parameterized queries. With
parameterized queries, the developer creates a SQL query statement using
placeholders for the variables that contain user input. Then, the
programmer can pass the SQL query with the placeholders followed by the user data.
This allows the SQL server to distinguish between the two. Here's an
example:

login_data =
web.input()

query_string =
"SELECT * FROM USERS WHERE ID = ?"

cursor.execute(query_string,
(login_data.user_id))

Here, the "?" is a
placeholder for user input. When cursor.execute() is called, the
programmer passes the query string and the user_id parameter as two distinct
elements.

In order to avoid SQL injection and other attacks (e.g. XSS), developers should
also filter user input. Input filtering helps to prevent all attacks that
rely on malformed input, not just SQL injection. This is also an example
of defense in depth. Input filtering and parameterized queries are
complementary measures of protection with regards to SQL injection

Suppose that the username should be from 3 to 32 characters long and that it
can contain any alphanumeric character, a period or an underscore. You
could write a regular expression to check this:

The '^' and '$' match the beginning and
end of the string. \w is shorthand for [0-9a-zA-z_] and {3,32} specifies
a minimum and maximum length of 3 and 32 respectively.

It's very important to use
parameterized queries AND input checking. Parameterized queries do not
prevent all SQLinjection.
From Thomas Ptacek:

Specifically, you write: "Parameterized queries are a better way
of solving the problem, because it doesn't require any escaping". This is
wrong. Most database protocols will allow you to bind data to a query,
but not keywords, or even limits and offsets. A whole generation of programmers
has been convinced that using parameterized queries shields them from SQL
Injection, while writing pagination code or sortable tables that are trivially
injectable.

For Managers

If you'd like to save your organization
from the embarrassment of a breach, you need to ensure that your developers
understand SQL injection and other common application security problems.
There are a lot of sites out there with SQL injection vulnerabilities and many,
if not most, developers just don't seem to know better. Establish coding
standards and educate your developers. Once you have those parts in
place, incorporate them into your code reviews and software testing.

If you don't have enough security knowledge in-house, you can task some of your
developers with doingtheresearch. If you have money to burn, hire
consultants. They have to eat too :)