17

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.

37

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:

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.
  • 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
  • 2
    Just a comment on Java - StringEscapeUtils is ancient and rather rubbish. If you really want to escape HTML properly, you need to use a proper HTML sanitiser such as the one from OWASP. – Boris the Spider Dec 18 '15 at 15:22
  • 5
    Just want to add that "prepared statements" and "parameterized queries" are two names for the same concept. Different programming languages just refer to them differently. – Derek W Dec 18 '15 at 16:02
  • 3
    I'm not sure why you mention XSS or Direct Object Reference. I mean, sure they are issues that aren't prevented by doubling single quotes, but neither are CSRF, session fixation, and so on. But none of them have anything to do with SQL Injection either, which is what the question was about. – tim Dec 18 '15 at 16:05
  • @tim My reasoning is that the purpose of an SQL injection attack is to negatively affect users/the website/app. Given the command to UpdateUser(int), if this isn't properly handled on the server side, you could modify the request to update any id. That means you can install yourself as an admin if you know the admin's userid. From there, you can likely access things most users aren't allowed to, and cause a lot of trouble. Likewise, being able to inject a drive-by-download on all of your website visitors could enable you to steal far more information than is stored in the database. – Mark Buffalo Dec 18 '15 at 17:08
  • @DerekW Agreed, same concept. I want people to know they're often called different things in other languages. – Mark Buffalo Dec 18 '15 at 17:10
13

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.

  • 2
    Does this work on MSSQL? I know it works on MySQL, but the question is about MSSQL. – paj28 Dec 18 '15 at 17:39
  • @paj28 thanks, that's a very good point. I just assumed that it would work (I don't have access to MSSQL to test right now), but I think you are right, it probably wouldn't. Here is a very similar question on stackoverflow. – tim Dec 18 '15 at 18:00
7

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.