What is the best way to avoid SQL injection attacks?

Question

I've provided a solution for Python... please flesh this out with examples for other languages.

Answer 1

Using the Python DB API, don't do this:

cmd = "update people set name='%s' where id='%s'" % (name, id)
curs.execute(cmd)

instead, do this:

curs.execute('update people set name=:1 where id=:2', [name, id])

Answer 2

Here's an article on Avoiding SQL Injection from C# Online.NET

An interesting approach - Using Base64 to avoid SQL injection attacks

ASP.NET Debugging: SQL Injection and how to avoid it

Answer 3

Languages often have functions to escape the string, such as php with functions like mysql_escape_string as such:

$query = sprintf("update people set name = '%s' where id = '%s'",
    mysql_escape_string($name), mysql_escape_string($id));
$result = mysql_query($query);

Other languages and frameworks provide a mechanism similar to the one you mentioned. In Java, you can use a PreparedStatement as such:

PreparedStatement statement = connection.prepareStatement(
        "update people set name = ? where id = ?");
statement.setString(0, name);
statement.setString(1, id);
statement.execute();

Answer 4

If you're using .NET, there's a pretty comprehensive article on avoiding SQL Injection on MSDN: http://msdn.microsoft.com/en-us/library/ms161953(loband).aspx

But, just like Wargames, the best way to win the SQL Injection game is not to play. Don't use SQL strings - use an OR/M tool like LINQ to SQL, SubSonic, NHibernate, etc. Any popular OR/M (like those I mentioned) uses parameterized queries and other safeguards to prevent SQL Injection.

Answer 5

A general rule for avoiding any kind of injection is "DON'T TRUST USER INPUT!"

As for Java, use JDBC's PreparedStatements. Here's a small example:

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                                     SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)

Answer 6

In PHP, best practice is to use MySQLi (Tutorial) or PDO bound parameters in prepared statements. addslashes() is no longer recommended as it is is too easy to defeat.

I personally prefer PDO because it supports named parameters: all those ? placeholders in mysqli are icky and make the query hard to read

Answer 7

The venerable Joel Spolsky wrote an interesting article around this sort of thing. If you haven't read it yet, take a look at Making wrong code look wrong.

Of course the better method would be to always use parameterized queries or other built-in language features if possible.

Answer 8

Never trust user input, never trust client input at all, client side sanitation efforts are worthless, it doesn't take much sophistication to access your web app directly. Always perform sanitation server-side, don't even bother with client-side sanitation, it's a waste of effort. There are a couple techniques that are useful for avoiding SQL injection attacks, I'll go from least favored to most favored.

• 3: Custom-Written Value Sanitation. Avoid writing your own sanitation routines as much as possible except when it's absolutely the only option remaining (which is very unlikely in any modern language). Input sanitation is a hard problem, and the costs of getting it wrong are huge. It's best to leave that job to someone else. When you are forced to rely on this method use white-listing rather than black-listing to sanitize input.

• 2: Framework / Library Based Value Sanitation. Leave the sanitation routines to the domain experts. Sanitation routines in 1st party frameworks and libraries are typically created by the same folks that wrote the DB or the SQL API. They have a much higher chance of knowing, and properly handling, all of the edge cases than you do. An example of this technique would be using php's mysql_escape_string() function to sanitize values that will be inserted into strings that serve as dynamic SQL statements.

• 1: Parameter Binding. (aka prepared statements) Instead of constructing a SQL statement as a raw string which includes user data as in-place literal values, create a SQL statement with tokens where the user data would be. Then bind the user supplied data to the appropriate parameters. The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement. This can be used in conjunction with library based input sanitation as well.

Here's an example in C# (Java and Python have similar functionalities, see some of the other answers):

SqlCommand userInfoQuery = new SqlCommand(
    "SELECT id, name, email FROM users WHERE id = @UserName", 
    someSqlConnection);

SqlParameter userNameParam = userInfoQuery.Parameters.Add("@UserName",
    SqlDbType.VarChar, 25 /* max length of field */ );

// userName is some string valued user input variable
userNameParam.Value = userName;

Answer 9

Oracle has got an extensive tutorial on that topic

Answer 10

In mysql and php I use mysql_real_escape_string to protect agains injection. I assume this is sufficient

Answer 11

As others have said, parametrized or prepared queries are the answer. Here is how you do it in PHP/Postgres:

$sqlstr = 'SELECT thing FROM table WHERE field = $1::varchar AND other = $2::integer'
pg_query_params($connection, $sqlstr, array($untrusted, $input));

The explicit type casting is recommended. Since the SQL is parsed separately from the data, SQL injection is impossible.

Answer 12

Edit my post (see footnote).

Some of us are maintaining sites built in 1999. If you find an SQL injection vulnerability in ASP 3.0, maybe this will help. Don't do this:

DBConn.Execute("UPDATE members SET photo = '" + gblSQLSafe(fileName) + "' WHERE memberID = " + memberid);

Here, gblSQLSafe() doubles up single quotes in the string. Instead do something like this:

objCmd.CommandType = adCmdText;
objCmd.CommandText = "UPDATE members SET photo = @filename WHERE memberID = @memberID";
objCmd.Parameters.Append(objCmd.CreateParameter("@memberID", adInteger, adParamInput, 4, memberid ));
objCmd.Parameters.Append(objCmd.CreateParameter("@filename", adVarChar, adParamInput, 510, fileName));
objCmd.Execute(adExecuteNoRecords);
gblDelobjParams(objCmd);

That said, if you have 50 legacy sites with no time to actually change all the darned code on all of them, try something like UrlScan for iis6 and 7; dotDefender for pay which also covers Apache or IIS 5-7 or SQL Injection sanitation ISAPI for IIS6.

Please note that my ASP 3 is rusty and this code threw an error on my db about a variable (filename) not being assigned. I'm actually testing how stackoverflow might allow someone to correct an erroneous response or improve on it. So if no one fixes or supersedes this, I'll probably delete it soon enough.

Answer 13

In mysql and php I use mysql_real_escape_string to protect agains injection. I assume this is sufficient

Maybe not in all cases. Ilia Alshanetsky is perhaps the world's foremost authority on PHP security and he points out that mysql_real_escape_string can be prone to precisely the same character set vulnerability that has made addslashes rightly derided.

mysql_real_escape_string() versus Prepared Statements

I think in view of this that prepared statements are perhaps the only best practice way to prevent sql injection attacks in PHP. But Ilia points out that there are caveats even here:

The solution is to use prepared statements, which are supported by nearly all PHP database extensions with the notable exceptions of MySQL (ext/mysql) and SQLite2 (ext/sqlite). So, to be on the safe side, I'd recommend using the PDO interface to talks with those databases or in the case of MySQL using the newer MySQLi (ext/mysqli) extension. Those interfaces provide prepared statement support, which allows for separation between query structure and the query parameters. It should be noted that while PDO does emulated prepared statements for older versions of MySQL that do not support them natively, emulation is still prone to the same kind of issues demonstrated here and in Chris’ article. Therefore for security reasons you should definitely consider upgrading to a more modern version of MySQL and SQLite (SQLite 3).

I strongly recommend buying his book phpArchitect's Guide to PHP Security; it's to be preferred over Shiflett's Essential PHP Security in my opinion.

It's also important to validate data before using it in the query; ensure that variables are initialised before use, that integers are cast as such before use, and ideally check their range to ensure they're not out of bounds. For example, this is how we would initialise and cast integer input:

$userID = (isset($_GET['userID'])) ? (int) $_GET['userID'] : 0;

Answer 14

It's worth noting in light of a wide spread SQL Injection attempts that disallowing your webapp's db user account from querying the system tables (in MS SQL Server it's sysobjects and syscolumns) is a good idea.

It's so little effort and makes a lot of sense.

Alternatively if you could set everything to be read only, do so.

Answer 15

if your DB allows them, stored procedures can help reduce the risk of sql injection.

Answer 16

Use a database API (like SQLObject, SQLAchemy in python, ActiveRecord in Ruby/Rails and the likes) and check the documentation on their recommended way of adding data into queries.

Don't do things like query = "SELECT * FROM 'table' where 'a' == '" . users_input . "'". Instead use your db-API's method of adding data into queries - usually long the lines of db.query("SELECT * FROM 'table' WHERE 'a' = % and 'x' > %", users_input, second_item)

Don't ever try to do SQL-injection prevention yourself (using a regex or similar) - validate the data obviously, but don't try to write your own function to neuter strings before putting them in a query..

Answer 17

Always, always, always parameterise any user input.

Don't assume sprocs are automatically safe - they can be just as prone.

Answer 18

In ColdFusion there is a tag (function) called cfqueryparam that should be used whenever writing inline queries...

Usage:

<cfquery datasource="#application.dsn#" name="qryTest" maxrows="#intRows#">
SELECT FirstName, LastName, Phone
FROM   tblUser
WHERE  Status = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.status#">
</cfquery>

Naturally you can also call paramatised stored procedures using the cfstoredproc and cfprocparam tags...

Answer 19

1. Always use database query bind parameters
2. See point number 1

Answer 20

In Delphi, to use a prepared statement do something like this:

  query.SQL.Text := 'update people set name=:Name where id=:ID';
  query.Prepare;
  query.ParamByName( 'Name' ).AsString := name;
  query.ParamByName( 'ID' ).AsInteger := id;
  query.ExecSQL;

In addition to avoiding SQL injection, one of the benefits to using parametised queries is that it avoids problems with regional number, date and time formatting in your queries.

Answer 21

In Ruby on Rails (ActiveRecord):

Person.find :all, :conditions => ['id = ? or name = ?', id, name]

or, if you have to build your own SQL statement (which you should avoid if possible):

Person.find_by_sql "SELECT * from persons WHERE name = #{ Person.quote_value name}"

Answer 22

another important thing no one has mentioned yet:

• grant each query in your code with the minimal permissions it needs for the specific query (usually it means read-only access for a few tables only).
• try to design your database so that tables that can be written by users are separated from those who hold "your" data. this way you can create a user that has access only to these tables.

this way, even if there is some flaw somewhere, you limit its effect.

Answer 23

The only way to COMPLETELY prevent SQL injection is to either:

a) Not use a database backend (hope you like flat files or pure HTML)

or

b) Not accept ANY user input or any data from the client side (this includes things like form data).

In other words, the only way to completely prevent it is to make your application completely unusable.

It's like sex: the only 100% sure way to prevent pregnancy is to not have it (and be careful that your toilet seat lids are clean).

Once you've accepted that you will never be 100% safe, sanitize your inputs and follow the advice of all the other wonderful commenters.

But remember: new attacks appear with alarming frequency. You are never safe. Constant vigilance is the cost of freedom.

Answer 24

Since no-one has mentioned classic ASP yet, I'll jump in with what I have learned over the past year (during which we've been hacked 4 times - until I dealt with things). We use Microsoft SQL Server.

First of all, DON'T USE INLINE SQL. If you use only stored procedures (with NO dynamic SQL in them) you are a million times safer (it's faster too). Procs can only be passed data via parameters, which means all the hacker tricks with hyphens and apostrophes do not work.

If you have no choice but to use inline SQL (like me you have been brought in to work on an old site with 1000 pages all using it) then you need to be very careful! Make sure that when you build a SQL string, you wrap every number with CDbl() - don't use CInt() because SQL integers go up to a higher number than ASP integers and you may get overflow. Any strings you concatenate with the SQL string should be carefully checked too - even without hackers you'd need to double your quotes anyway:
Replace(strSomething, "'", "''")

The most useful thing I did to protect the old site I work on, is to check the Request.QueryString and Request.Form data on every page, before it get used. I put a function into the same ASP that makes the database connection, so that no page could access the database without hitting my extra code too. Basically I just check the QueryString and Form data for the following suspicious strings:
varchar
(0x
sysob
<script
char(
char (
sysobject
substring
declare @
insert into

These are not the sort of thing that occur in normal user input (although they might on StackOverflow!).

Here's how I call that function:

Dim sMakeDamnSure
sMakeDamnSure = CleanseString(Request.QueryString)
If Left(Request.ServerVariables("CONTENT_TYPE"), 19) <> "multipart/form-data" Then 'cant check uploads
    sMakeDamnSure = CleanseString(Request.Form)
End If

Finally, whether you are using ASP or ASP.NET or anything, you should make sure your database is as safe as possible too: split up databases rather than lump all tables into one, use separate logins for each website, and DENY access to everything you can, only using GRANT on the tables or procs that a login really needs. Don't forget to DENY access to the system tables and procs too.