Escape characters in an SQL Query

Question

I'm trying to update a database using this query:

string query = "UPDATE pagine SET titolo= '" + pa.titolo + @"', contenuto = '" + pa.contenuto + @"' WHERE id= " + pa.id;

I can't get it working. More details are available at Updating an mdb database table

The problem might be the fact that i have some apostrophes in the title and the content. I tried using Regex.Escape but it just adds tons of \\\ to the content.

Is there any way to escape just the ' and " characters in ASP.NET?

Note: since i'm italian i use italian names for variables but i translated them for better clarity in the other question posted yesterday.

This is not the correct approach - read up on [SQL injection](http://en.wikipedia.org/wiki/SQL_injection). Use parameters, it'll make your life much simpler... — PinnyM, Apr 10 '13 at 16:38
How is this different from your [previous question](http://stackoverflow.com/questions/15906455/updating-an-mdb-database-table)? — Tim Lehner, Apr 10 '13 at 16:41

score 5 · Answer 1 · answered Apr 10 '13 at 16:38

5

My advice is to use SqlCommand object with SqlParameters to avoid all the concatenation and escape chars fuss. They'll automatically handle all of it for you. In addition they'll save you from SQL injection attacks too.

If you have option, I'd suggest that you go for typed DataSet approach and create a Typed Query for yourself. Then you can call that as a regular .NET function in your code.

answered Apr 10 '13 at 16:38

dotNET

33,414
24
162
251

Not all database providers support parameters (Odbc does not) so this is not always an option. – user169771 Jun 18 '19 at 21:28
@user169771: Yeah, but my answer (and most SO answers in general) are good in the context that OP provides. Here OP seems to be using an MDB and is most like not connecting through ODBC. See his other linked question. – dotNET Jun 19 '19 at 05:18

paparazzo · Answer 2 · 2013-04-22T20:55:30.790

0

SQLParameter are safer as you can't forget to clean up text.
You can just escape the text.

string userText = @"you realy shouldn't do this";

string sqlSafeText = sqlSafeText.Replace(@"'", @"''");

edited Apr 22 '13 at 20:55

answered Apr 10 '13 at 16:43

paparazzo

44,497
23
105
176

score -1 · Answer 3 · answered Apr 10 '13 at 16:46

-1

In C# you can escape " like below -> " to \"

"ABELE\"o\""

you can escap single Quotes ' like below ' to ''

"ABELE''o"

answered Apr 10 '13 at 16:46

Pandian

8,848
2
23
33

Escape characters in an SQL Query

3 Answers3