Escaping values in SQL queries (C# with SQL connector)

Question

Hi, I know I can use the parameters, but what is the right way to escape string sequences? The query could be like this:

"INSERT INTO records (ReferenceID,Name,Note,Author) VALUES ('" + ID+ "','" + addlevel.textBox1.Text + "','"+addlevel.textBox2_note.Text+ "','"+Program.Username+"')";

I am ONLY curious, just want to know :)

EDIT: But what about that? "CREATE TABLE "+string" .... parameters cannot be used here!

Answer 1

The right way is to use parameters.

"Just Say No" to trying to do the escaping yourself - it's far too easy to get wrong. Why do you think you'd want to escape them manually instead of using parameters?

Answer 2

If you really, really, really need to do the escaping yourself (of which there is no sign in your example):

string EncodeMySqlString(string value) {
   return value.Replace(@"\", @"\\").Replace("'", @"\'")
}

Answer 3

If you need to perform database operations, such as creating tables, then you should use SQL Server Management Objects instead of executing SQL strings.

For CRUD operations parameters is absolutely the only true path.

UPDATE: It appears that the MySQL client library contains a helper method for this ill-advised task. You can call MySqlHelper.EscapeString(string).

Answer 4

I think the only thing you need to do is value = value.Replace("'", "''")

Of course you shouldn't do this, but you know that.

Edit: Apparantly this is all wrong for MySQL. It should work for PostgreSQL, MS SQL and Oracle, though.