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.

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.

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.

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.

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: https://stackoverflow.com/questions/3166952/how-to-return-values-from-a-dynamic-sql-stored-procedure-to-the-entity-framework/3167025#3167025

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);

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

System.Security.SecurityElement.Escape(Value)