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:  

login_data = web.input()
query_string = "SELECT * FROM USERS WHERE ID = '%s'" %

login_data.user_id

cursor.execute(query_string)

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:

login_data = web.input()
if not re.match(r'^[\w.]{3,32}+$', login_data.user_id):
         some_error_thingy()
query_string = "SELECT * FROM USERS WHERE ID = ?"
cursor.execute(query_string, (login_data.user_id))

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 doing the research.  If you have money to burn, hire consultants.  They have to eat too :)