CODEWITHSUNDEEP
c#sqlescaping
liuhongbo
liuhongbo

Reputation: 2131

Escape single quote in a SQL query string in C#

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.

Upvotes: 0

Views: 12271

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415755

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.

Upvotes: 0

Mahib
Mahib

Reputation: 4063

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

Upvotes: 0

Aman Thakur
Aman Thakur

Reputation: 169

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'

Upvotes: 1

tonyriddle
tonyriddle

Reputation: 388

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

Upvotes: 1

Related Questions