How to escape simple SQL queries in C# for SqlServer

Question

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 quiet 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.

Thanks.

Seva Alekseyev thank you so, so, so much for reading my question and trying to answer it. Thank you for not preaching to me about "best practices". And thank you for not trying to "educate me" Really, thank you!

Answer 1

Parameterise it!

A Google search of SO for "SQL injection"

Edit:

In response to Seva Alekseyev, SO answer with character 8 injection

Answer 2

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

Answer 3

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.