SQL injection single quote Vulnerability

Question

Hello I'm security testing a website I'm working on. Some developer tried to avoid SQL injection by replacing every single quote with double quotes. This is the C# code:

string sql = 
  @"SELECT *
      FROM users
     WHERE us_username = '$us'
       AND us_password = '$pw'";    

sql.Replace("$pw", txtPassword.Text.Replace("'","''"));

Is there any way that I can perform a SQL injection attack? I've tried the Unicode trick but it didn't work. The database runs on SQL Server 2008R2.

Answer 1

You should use parameterized command instead. Using string.Replace is just a bad idea.

var command = conn.CreateCommand();
command.CommandText = @"SELECT *
        FROM users
        WHERE us_username = @user
        AND us_password = @password";
cmd.Parameters.Add("@user", txtUser.Text);
cmd.Parameters.Add("@password", txtPassword.Text);

This might be a potential candidate for your setup :

As an example, note the following trivial Stored Procedure:
create procedure GetData ( @param varchar(20) ) as
begin
declare @s varchar(200)
select @s = 'select * from dataTable where name = ''' + @param + ''''
exec (@s)
end

This SP may be called from a Web page, which executes validation code before passing the input to the SP. At a minimum, this validation code either verifies that the input does not contain a quote, or sanitizes it to double any existing quote. For instance, the validation code may be using string.Contains(), string.Replace(), Regular expressions, etc. It is also possible that this Web page is behind a finely-tuned Web Application Firewall that validates all input and verifies that no quotes are included. A malicious user or attacker can submit malicious code containing a modifier letter apostrophe (U+02BC, URL encoded to %CA%BC). This will easily pass applicative validation code and WAF filters, since these search for an actual quote (U+0027) which does not exist in the input at this time. Obviously, IDS/IPS systems would also not detect anything amiss. The validation mechanisms may even search for various encodings of a quote, such as URL Encoding, UTF-8 encoding, Hex encoding, double encoding, and more – however, U+02BC is none of these, and is in fact a completely different character value.

---

And this is where the interesting (or scary) part starts – the Unicode homoglyph translation is not limited to base alphabet characters... Specifically, the Unicode character U+02BC (modifier letter apostrophe) can be translated by the database server to a simple quote – ' (U+0027). There are, of course, many other similar examples.

Source : http://web.archive.org/web/20130401091931/http://www.comsecglobal.com/FrameWork/Upload/SQL_Smuggling.pdf

Answer 2

The code for that partical query is safe from SQL injection, but only when used with a certain databases. Each system has its own set of characters that needs escaping, so if you use that with for example MySQL then it's not safe. Other queries might not be safe.

The code should be replaced nevertheless as it is broken. As you need to fix the code you should also change it to using parameterised queries, which is a more robust and portable solution.

So, let's see what's broken. As the code is replacing one parameter at a time, they may interfer with each other. If I for example enter the user name has$$$pwnd and the password 1234 (yeah, weak password), you end up with a query that looks like:

SELECT *
  FROM users
WHERE us_username = 'has$$1234nd'
  AND us_password = '1234'

If some values contain the codes that is used for parameters replaced after it, the values become broken.

This could even be used to make an SQL injection in other queries in the code, if there are parameters of different types and the values are not properly verified. As values from one parameter can end up in another parameter, a string value could end up in a numeric parameter which doesn't have apostrophes around it, thus there is no need to sneak in an apostrophe to break out of a string literal to put harmful code in the query.

Answer 3

The best way to counter SQL Injection is to add parameters.

   SqlCommand sql = new SqlCommand (@"SELECT *
            FROM users
            WHERE us_username = @user
            AND us_password = @password")

sql.Parameters.Add("@users", SqlDbType.Varchar2, 5).Value = "users"; 
sql.Parameters.Add("@user", SqlDbType.Varchar2, 6).Value = "your_value";
sql.Parameters.Add("@password", SqlDbType.Varchar2, 8).Value = "your_value";

As you can see, you can do quite a bit to ensure what is being executed are the values that you want to only get executed.

What the developer has coded will only alter the sql statement after the fact, which is good if they are logging this sql statement. What you have now however will not protect against Sql Injection.