MSSQL: single quotes causes to error in queries

Question

I've got a project with a lot of SQL-queries compiled in *.DLLs files.
Yesterday, I've received a new bug: username (which generated automatically) with single quote causes an error.
The reason is queries like this one:

    string.Format("SELECT TimeZone from yaf_User WHERE [Name]='{0}'", UserName);

Can someone to suggest any kind of trick or hack to fix it?

Update: I don't know why the developers use this horrible way to generate SQL-query, but for now I should fix it. The client will not understand why I should to rewrite a lot of code for fix.

Answer 1

What you are doing is dangerous because it lends itself to SQL injection attacks (as a side effect it also causes the issue you're seeing.

The solution is to use parameterised queries - this also avoids SQL injection attacks.

SqlCommand cmd = new SqlCommand("SELECT TimeZone from yaf_User WHERE [Name]=@UserName"), conn);
cmd.Parameters.Add(new SqlParameter("Username", theUsername));

Your only alternative is to escape the single quote. However this is a fix to your code but your solution would remain insecure. I cannot stress how important it is that you resolve this issue - as it stands I could wipe out entire tables of data in your system by logging in with a malicious username.

Answer 2

This is for Chris. Escaping characters may work. If the person is clever, the are some ways around it.

For instance.

-- Use Adventure works
use adventureworks2012
go

Say, I know you are replace a single quote with two, your chosen solution on the answere line. Enter the following

Bothell'; GRANT CONTROL TO [adw_user];PRINT' at a text box.

This boils down to this @fld variable.

-- Declare the vars
declare @sql nvarchar(max);
declare @fld varchar(128) = 'Bothell''; GRANT CONTROL TO [adw_user];PRINT''';
print @fld

-- Perform some injection
set @sql = 'select * from [Person].[Address] where City = ' + 
    char(39) +  @fld + char(39);
print @sql
exec sp_executesql @sql

There you have SQL Injection.

select * from [Person].[Address] where City = 'Bothell'; 
GRANT CONTROL TO [adw_user];PRINT''

(26 row(s) affected)

http://www.w3schools.com/sql/sql_injection.asp

Quote from W3Schools - The only proven way to protect a web site from SQL injection attacks, is to use SQL parameters.

A very good read. Check out link to truncation attacks. In short, parameterization makes sure the input is treated as a literal, not code.

http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

Answer 3

Single quotes are the reason why databases get hacked. It's the concept behind SQL Injection. Taking advantage of the fact that Strings use quotes. Manipulating the value to be inserted by adding additional quotes and messing up the whole SQL statement. You can use parametized queries to avoid SQL injection but takes a few more lines of codes or use this instead.

sql="SELECT TimeZone from yaf_User WHERE [Name]='" + VariableName.replace("'","''") + "'";