CODEWITHSUNDEEP
c#sqlsql-server
sc45
sc45

Reputation: 1956

How to escape simple SQL queries in C# for SqlServer

I use an API that expects a SQL string. I take a user input, escape it and pass it along to the API. The user input is quite simple. It asks for column values. Like so:

string name = userInput.Value;

Then I construct a SQL query:

string sql = string.Format("SELECT * FROM SOME_TABLE WHERE Name = '{0}'",
                           name.replace("'", "''"));

Is this safe enough? If it isn't, is there a simple library function that make column values safe:

string sql = string.Format("SELECT * FROM SOME_TABLE WHERE Name = '{0}'",
                           SqlSafeColumnValue(name));

The API uses SQLServer as the database.

Upvotes: 93

Views: 85258

Answers (6)

Kind Contributor
Kind Contributor

Reputation: 18583

SqlCommand and Entity Framework use exec sp_executesql....

So there really is an alternative to raw strings with your own escaping pattern presumably. With SqlCommand you are technically using parameterised queries but you're bypassing the ADO.Net abstraction of the underlying SQL code.

So while your code doesn't prevent SQL Injection, the ultimate answer is sp_executesql not SqlCommand.

Having said that, I'm sure there are special handling requirements for generating an SQL Injection-proof string which utilizes sp_executesql.

see: How to return values from a dynamic SQL Stored Procedure to the Entity Framework?

Upvotes: 1

user3820515
user3820515

Reputation:

One may wish to replace ' with '' instead of parameterizing when needing to address the ' problem in a large amount of ad hoc sql in a short time with minimal risk of breakage and minimal testing.

Upvotes: 0

ThurstonFord
ThurstonFord

Reputation: 16

I was using dynamic sql (I can hear the firing squad loading their rifles) for search functionality, but it would break whenever a user searched for somebody with a surname like "O'Reilly".

I managed to figure out a work-around (read "hack"):

Created a scalar-valued function in sql that replaced a single quote with two single quotes, effectively escaping the offending single quote, so
"...Surname LIKE '%O'Reilly%' AND..." becomes "...Surname LIKE '%O''Reilly%' AND..."

This function gets invoked from within sql whenever I suspect fields could contain a single quote character ie: firstname, lastname.

CREATE FUNCTION [dbo].[fnEscapeSingleQuote]
    (@StringToCheck NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)
    SELECT @Result = REPLACE(@StringToCheck, CHAR(39), CHAR(39) + CHAR(39))
    RETURN @Result
END

Not very elegant or efficient, but it works when you're in a pinch.

Upvotes: 0

Kevin
Kevin

Reputation: 9

If you need to escape a string for a MSSQL query try this:

System.Security.SecurityElement.Escape(Value)

Upvotes: -11

Seva Alekseyev
Seva Alekseyev

Reputation: 61378

Since using SqlParameter is not an option, just replace ' with '' (that's two single quotes, not one double quote) in the string literals. That's it.

To would-be downvoters: re-read the first line of the question. "Use parameters" was my gut reaction also.

EDIT: yes, I know about SQL injection attacks. If you think this quoting is vulnerable to those, please provide a working counterexample. I think it's not.

Upvotes: 168

Marc Gravell
Marc Gravell

Reputation: 1062975

Simple:

const string sql = "SELECT * FROM SOME_TABLE WHERE Name = @name";

and add the @name parameter with value:

cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@name", name);

Upvotes: -6

Related Questions