SQL Injection, simplest solution.

Question

I'm unsure how someone would break my SQL if I simply replace all incoming single quotes with double quotes. Can someone enlighten me for both Oracle and SQL Server examples? Thanks.

string sql1 = "select * from users where user_id = '" + "O'Reily".Replace("'", "''").Replace("\", "") + "'";

==> "select * from users where user_id = 'O''Reily'

string sql2 = "select * from users where user_id = '" + "O'''Reily".Replace("'", "''").Replace("\", "") + "'";

==> "select * from users where user_id = 'O''''''Reily"

UPDATE: the slash '\' is a restricted character in the application and will be stripped out before it is used in the query. A double dash can just as easily be added to this list of restricted characters.

Answer 1

Your proposed solution is vulnerable to the inclusion of the \' string, which would end your quoted section and allow the injection of other commands.

You want to use SQL prepared statements wherever possible, which should be everywhere. Basically, you write your sql with specific placeholders for your data, and then pass that data via a separate, non-interpreted channel to the sql server.

A few links:

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

http://mattbango.com/notebook/web-development/prepared-statements-in-php-and-mysqli/

Answer 2

Parameterize your variables. Seriously. All modern environments have facilities to do so and you don't have to worry about escape sequences like \' which will turn into \'' with your scheme (in Oracle) which becomes an escaped quote and a regular (terminating) quote.

There are plenty of other tricks to pull this off which I'm not enumerating as they aren't helpful.

Again: Parameterize your variables. Seriously. If you won't learn how to use the parameterization you will become another hacked statistic.

EDIT: Read the links in Paul's answer and here is another: http://unixwiz.net/techtips/sql-injection.html

No matter how clever you think your sanitation of strings is, you are doing it wrong. Especially if you have to handle multiple back ends.

Composing queries out of strings is one of the few things I will flat out fire people for... the risk such a programmer poses to the company is greater than just about anything else they bring to the table (especially after we make it very clear we won't accept such code on day one and provide an entity framework that makes such things unnecessary).

Answer 3

To prevent SQL injection, what you really should do is use bound positional or named parameters instead of constructing your SQL as a string with the user input inlined. How this is done depends on how your application accesses the database. For example, here is what it would look like in Java using JDBC:

Bad:

String updateString = "UPDATE COFFEES SET SALES = 75 " + 
                      "WHERE COF_NAME LIKE 'Colombian'";
stmt.executeUpdate(updateString);

Good:

PreparedStatement updateSales = con.prepareStatement(
        "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75); 
updateSales.setString(2, "Colombian"); 
updateSales.executeUpdate():

I borrowed the example from here: http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Answer 4

There are some tricks involving the attacker taking advantage of the application truncating input :

http://www.rampant-books.com/t%5Fsuper%5Fsql%5F154%5Fideas%5Fprevent%5Finjection.htm