escape characters in SQL query string produced via string.format

Question

I have the statement in c# :

String sql = String.Format("UPDATE Table SET FIRST_NAME='{0}',LAST_NAME='{1}',BIRTH_DATE='{2}' where CUSTOMER_NUMBER ='{3}'",FirstName, LastName,DateOfBirth,Number);

The above statement doesn't execute if the first name,last name etc have apostrophe like O'Hare,O'Callahagan because of this the update statement gets the wrong syntax.

How to escape the apostrophe in string.format?

Use parameterized SQL! http://www.dotnetperls.com/sqlparameter http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection — Justin Pihony, Nov 19 '13 at 18:37
Try with next: http://stackoverflow.com/questions/11528122/escape-character-for-sql-in-c-sharp/23365652#23365652 — Eduardo Cuomo, Apr 29 '14 at 13:20

score 16 · Accepted Answer · answered Nov 19 '13 at 18:44

How to escape the apostrophe in string.format?

Don't escape it, use parameterized query instead.

Imagine a user with a really unconventional name strongly resembling SQL statements for dropping a table or doing something equally malicious. Escaping quotes is not going to be of much help.

Use this query instead:

String sql = @"UPDATE Table
    SET FIRST_NAME=@FirstName
,   LAST_NAME=@LastName
,   BIRTH_DATE=@BirthDate
WHERE CUSTOMER_NUMBER =@CustomerNumber";

After that, set values of FirstName, LastName, DateOfBirth, and Number on the corresponding parameters:

SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@FirstName", FirstName);
command.Parameters.AddWithValue("@LastName", LastName);
command.Parameters.AddWithValue("@BirthDate", BirthDate);
command.Parameters.AddWithValue("@CustomerNumber", CustomerNumber);

Your RDMBS driver will do everything else for you, protecting you from malicious exploits. As an added benefit, it would let you avoid issues when the date format of your RDBMS is different from your computer: since your date would no longer be passed as a string representation, there would be no issues understanding which part of the formatted date represents a day, and which one represents a month.

I changed the entire structure of my program using parameters. That works thank you :) — user1118468, Nov 19 '13 at 18:54
@user1118468 You are welcome! If this works for you, consider accepting an answer by clicking the grey check mark next to it. This would let other visitors of the site know that you are no longer actively looking for an improved solution, and earn you a brand-new badge on Stack Overflow. — Sergey Kalinichenko, Nov 19 '13 at 18:58
What I am wondering: Why would escaping quotes not be of much help when confronted with a malicious name like "Robert'); DROP TABLE Students;--"? I see no issue when escaping the single quote to 2 single quotes. The malicious DROP TABLE would be a string because of the escaping and would not execute. — Manuel Hoffmann, Oct 05 '16 at 07:49

score 3 · Answer 2 · answered Nov 19 '13 at 18:48

You should use parameterized queries:

using (SqlCommand cmd = new SqlCommand("UPDATE Table SET FIRST_NAME= @FirstName, LAST_NAME= @LastName, BIRTH_DATE=@BirthDate where CUSTOMER_NUMBER = @CustomerNumber"))
{
    cmd.Parameters.Add(new SqlParameter("FirstName", FirstName));
    cmd.Parameters.Add(new SqlParameter("LastName", LastName));
    cmd.Parameters.Add(new SqlParameter("BirthDate", DateOfBirth));
    cmd.Parameters.Add(new SqlParameter("CustomerNumber", Number));

    // Now, update your database
} // the SqlCommand gets disposed, because you use the 'using' statement

By using parameterized queries, you solve your problem. Using parameterized queries has two other advantages:

Protection against SQL Injection
Readability

score 1 · Answer 3 · answered Nov 19 '13 at 18:44

Use parameterized query.

string commandString = "insert into MyTable values (@val1, @val2)";     
SqlCommand command = new SqlCommand(commandString, connection);
command.Parameters.AddWithValue("val1", "O'Hare");
command.Parameters.AddWithValue("val2", "O'Callahagan");
command.ExecuteNonQuery();

escape characters in SQL query string produced via string.format

3 Answers3