How to defeat doubling up apostrophes to create SQLi attack?

Question

I know some developers double up apostrophes to mitigate SQLi. (This is when the input is ' so it becomes '')

Is there a way to beat this?

This is on MS SQl Server.

Answer 1

I try to show both sides of the security spectrum. Security is important, so you shouldn't just know how to defeat security, you should know how to implement it as well. Thus, I'm going to list a prevention first. If you don't want to read this, scroll down.

How do I prevent SQL injection?

---

Doubling up apostrophes is not the answer when it comes to security, and it can lead to insecurity. The answer depends on the programming language you're using.

• For SQL Server / Oracle / MySQL
  • With Java, use CallableStatements and PreparedStatements correctly.
  • With PHP, you'll need the appropriate Prepared Statements.
  • With C#/VB.NET, you'll need parameterized queries.

Note that these are all the same concepts in every language. They just have different names.

Even with prepared/callable statements or parameterized queries, the following is incorrect:

// Bad code, don't use 
string sqlString = "SELECT * FROM [table] WHERE [col] = '"+ something +"' AND [col2] = @Param";

You must never concatenate your SQL variables.

With SQL server, depending on your chosen language of choice, your query should look something like this:

• C#

  using (SqlCommand command = new ("SELECT * FROM [tab] WHERE LName = @LName", connection))
  {
      // Add new SqlParameter to the command.
      command.Parameters.Add(new SqlParameter("LName", txtBox.Test));
  
      // Read in the SELECT results.
      SqlDataReader reader = command.ExecuteReader();
      while (reader.Read())
      {
      }
  }
  

• Java

  Connection con = DriverManager.getConnection("database-connection-string","name","pass");
  
  // Question marks are the bound variables which are parsed in the defined column order. 
  PreparedStatement findLName = con.prepareStatement("SELECT * FROM [tab] WHERE LName = ?");
  
  // The order in which the question marks appear. You can have more than one in a prepared statement. First one is "1", second is "2", and so on.
  findLName.setString(1, Lastname);
  
  findLName.executeQuery();
  
  Statement stmt = con.createStatement();
  

• PHP

  Check w3schools for an example, I don't want my answer getting too long.

Note that this still doesn't get rid of potential injection of scripts onto a web page. You could insert exactly as they ask, within acceptable ranges, and then output the results to HTML.

If they insert the following (dumbed-down example):

<script> window.location.href='hxxp://www.mymalwarewebsite.com/'; </script>

...and you output that result to your page, then you're in big trouble. So what if you remove anything from <script> to </script>? What if they insert:

<scri<script>pt> window.location.href='hxxp://www.mymalwarewebsite.com/'; </scri<script>pt>

...? If you remove the script tags with replace, you're still stuck with that exploit.

What you really want is the following in addition to the above:

• For C#, you'll want to use HtmlEncode
• For Java, you'll want to use Java Html Sanitizer from OWASP
• For PHP, you'll want to use HtmlEntities.
• For all cases involving unicode parsing, you want to prevent unicode injection.

Shut up, Mark! How do I beat doubling up apostrophes?

---

• You may be able to beat them in different ways; you may be able to force various SQL databases to translate unicode to the local charset. For example, Ā could be converted to A. Even worse: U+02BC, or ʼ would be translated as ', which is U+0027. This is called Unicode-based Smuggling.
  • Doubling quotes doesn't work in older versions of MySQL.
• Although not really an SQL injection attack, you can try to force the website to inject malicious code to display to their users. You can try inserting script tags (read above for an example). Imagine injecting a drive-by download when people view your page, or a user list.
• Although not technically an SQL injection attack, you may be able to beat this protection by looking through the console in your browser and checking for integers being sent to the database, and then modify the request. This is called a Direct Object Reference exploit. There are various tools which can do this. Prepared Statements will not protect you against this attack. Please read the OWASP article

Answer 2

Is there a way to beat this?

Maybe (at least with MySQL). And it's really simple (you don't need any special encodings or the absence of quotes, or anything):

\' [injection] -- -

So for example, you have this query:

SELECT FROM table WHERE user = '[INPUT]';

Input is \' [injection] -- -, but with ' doubled, it becomes: \'' [injection] -- -, which leads us to this query:

SELECT FROM table WHERE user = '\'' [injection] -- -';

The injected part will be executed as a query, as it's not inside quotes anymore.

The only proper defense against SQL injection are prepared statements. They are also not hard to use, and often result in nicer code. There is really no good excuse for homemade defenses such as doubling quotes.

// update: It seems that this would not actually be possible with MSSQL. So that would leave the two issues already mentioned in the other answers: It's still vulnerable if no quotes are used in the query, or (possibly) for certain character sets.

It also depends on how the doubling actually takes place. If it's eg done via the database, it may be vulnerable.

Answer 3

This only prevents SQL injection attacks via string parameters. Sometimes, the parameter is an integer (think http://www.example.org/?page=3), then you don't need an apostrophe. Sometimes you can check if the parameters vulnerable if you replace page=3 with page=1+2, for example.