Escape single quote in a SQL query string in C#

Question

Just assume that I have no control of creating the query string, for example,

select * from customer where name='Joe's construction'

Also, there isn't any control of executing the query, for example, pass this to a REST API to execute on a database.

I need to escape the query as

 select * from customer where name='Joe\'s construction'

So I need to write a function like this:

 string escape(string sql)

The function pass the origin query and return the escaped string, so it can use the function like this:

 string s = "select * from customer where name='Joe's construction'";
 string es = escape(s);
 // 'es' should equals "select * from customer where name='Joe\'s construction'"

How can I make this function escape work?

Again, I have no control of creating that SQL query. I am only able to get the query string as a whole piece. And I am not using it to execute on any database; I just need to escape it and pass to an API.

Sigh. If you're not able to use parameterized queries, you're in trouble. "Escaping" is never gonna get it done. — Joel Coehoorn, Mar 20 '14 at 21:08
And I know you're not executing this query directly, but _someone_ will, and they're doing it **very** wrong. — Joel Coehoorn, Mar 20 '14 at 21:17
Is the SQL not already properly escaped for single quotes? I ask because `"... where name='Joe's construction'"` is invalid and should be `"... where name='Joe''s construction'"`. — TyCobb, Mar 20 '14 at 21:24
escape the string as name='Joe''s construction' is fine for me too, that won't make the question easier i think — liuhongbo, Mar 20 '14 at 21:40
This is in the top 2 for a search engine hit for "C# escape single quotes"... What is the canonical question for escaping string in C#? It definitely exists. Candidate: *[Can I escape a double quote in a verbatim string literal?](https://stackoverflow.com/questions/1928909/can-i-escape-a-double-quote-in-a-verbatim-string-literal/1928943#1928943)* (as an answer covers the most common cases and the external reference the rest). — Peter Mortensen, Sep 21 '22 at 14:18
However, is *this* question about escaping in C# or in SQL (or both at the same time)? — Peter Mortensen, Sep 21 '22 at 14:28

score 1 · Answer 1 · edited Sep 21 '22 at 23:01

1

Replace all single quotes with escaped quotes, and then remove the first and last slash. This will escape all inner single quotes.

string sql = "select * from customer where name = 'Joe's construction'";
sql = sql.Replace(@"'", @"\'");
sql = sql.Remove(sql.LastIndexOf(@"\"), 1).Remove(sql.IndexOf(@"\"), 1);

edited Sep 21 '22 at 23:01

Peter Mortensen

30,738
21
105
131

answered Mar 20 '14 at 21:10

tonyriddle

388
1
3
12

1

This won't work with more than one literal string in the query. – TyCobb Mar 20 '14 at 21:17

score 1 · Answer 2 · edited Sep 21 '22 at 23:06

1

This is very simple to resolve...

Just write a single quote twice in your query and it will be accepted by the SQL server...

You cannot escape a single quote with a backslash—you need another single quote:

select * from customer where name = 'Joe''s construction'

edited Sep 21 '22 at 23:06

Peter Mortensen

30,738
21
105
131

answered Feb 03 '15 at 17:52

Aman Thakur

159
4
14

score 0 · Answer 3 · edited Sep 21 '22 at 23:07

0

I've done something like this, and I didn't get any exception. This is probably a solution, but keep it mind the SQL injection issue that may come with it.

name.Replace("'", $"{(char)39}");

edited Sep 21 '22 at 23:07

Peter Mortensen

30,738
21
105
131

answered Sep 09 '19 at 19:51

Mahib

3,977
5
53
62

score 0 · Answer 4 · answered Sep 22 '22 at 14:23

Disclaimer: This is old now, but I'm here because I have (had) another answer on this question that was upvoted today. Looking back, I took the question a little too much at face value and don't recommend my original answer.

I have no control of creating the query string... Also, there isn't any control of executing the query, for example, pass this to a REST API to execute on a database.

Then I would beg off the assignment. I'm absolutely serious.

What you are asked to do is write software that you KNOW, with 100% certainly, will contain a serious SQL injection flaw. I have ethical issues being part of that.

The ONLY correct way to handle data like this is via parameterized queries. Parameterized queries do NOT sanitize or escape data. It's now how they work. Instead, they isolate query date from the SQL command string. At no point is the data ever substituted back into the query. In this way, you are perfectly protected from SQL injection. Anything else is only a matter of time until some unicode character or new feature opens up a serious attack route.

Escape single quote in a SQL query string in C#

4 Answers4