Escaping various characters in C# SQL from a variable

Question

I'm working a C# form application that ties into an access database. Part of this database is outside of my control, specifically a part that contains strings with ", ), and other such characters. Needless to say, this is mucking up some queries as I need to use that column to select other pieces of data. This is just a desktop form application and the issue lies in an exporter function, so there's no concern over SQL injection or other such things. How do I tell this thing to ignore quotes and such in a query when I'm using a variable that may contain them and match that to what is stored in the Access database?

Well, an example would be that I've extracted several columns from a single row. One of them might be something like:

large (3-1/16" dia)

You get the idea. The quotes are breaking the query. I'm currently using OleDb to dig into the database and didn't have an issue until now. I'd rather not gut what I've currently done if it can be helped, at least not until I'm ready for a proper refactor.

Totally not clear. Try giving some examples of how it is mucking up and what you expect it to do — Hogan
– Hogan, Commented Aug 14, 2012 at 0:17
or, better yet, use an ORM that handles it for you (i.e. LINQ to SQL). — Adam
– Adam, Commented Aug 14, 2012 at 0:27

quetzalcoatl · Accepted Answer · 2012-08-14 00:19:43Z

3

This is actually not as big problem as you may see it: just do NOT handle SQL queries by building them as plain strings. Use SqlCommand class and use query parameters. This way, the SQL engine will escape everything properly for you, because it will know what is the code to be read directly, and what is the parameter's value to be escaped.

answered Aug 14, 2012 at 0:19

quetzalcoatl

33.8k9 gold badges75 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard Schneider · Accepted Answer · 2012-08-14 02:46:15Z

3

You are trying to protect against a SQL Inject attack; see https://www.owasp.org/index.php/SQL_Injection.

The easiest way to prevent these attacks is to use query parameters; http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx

var cmd = new SqlCommand("select * from someTable where id = @id");
cmd.Parameters.Add("@id", SqlDbType.Int).Value = theID;

answered Aug 14, 2012 at 2:46

Richard Schneider

35.5k9 gold badges59 silver badges74 bronze badges

Comments

TheEvilPenguin · Accepted Answer · 2012-08-14 00:18:13Z

0

At least for single quotes, adding another quote seems to work: '' becomes '.

Even though injection shouldn't be an issue, I would still look into using parameters. They are the simpler option at the end of the day as they avoid a number of unforeseen problems, injection being only one of them.

answered Aug 14, 2012 at 0:18

TheEvilPenguin

5,7022 gold badges30 silver badges48 bronze badges

Comments

John Bingham · Accepted Answer · 2012-08-14 05:22:36Z

So as I read your question, you are building up a query as a string in C#, concatenating already queried column values, and the resulting string is either ceasing to be a string in C#, or it won't match stuff in the access db.

If the problem is in C#, I guess you'll need some sort of escaping function like

stringvar += escaped(columnvalue)
...
private static void escaped(string cv) as string {
  //code to put \ in front of problem characters in cv
}

If the problem is in access, then

' escapes ' " escapes "

& you can put a column value containing " inside of '...' and it should work.

However my real thought is that, the SQL you're trying to run might be better restructured to use subqueries to get the matched value(s) and then you're simply comparing column name with column name.

If you post some more information re exactly what the query you're producing is, and some hint of the table structures, I'll try and help further - or someone else is bound to be able to give you something constructive (though you may need to adjust it per Jet SQL syntax)

Collectives™ on Stack Overflow

Escaping various characters in C# SQL from a variable

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related