SQLite issues, escaping certain characters

Question

I'm working on my first database application. It is a WinForms application written in C# using a SQLite database.

I've come across some problems, when a apostrophe is used, my SQLite query fails. Here is the structure of my queries.

string SQL = "UPDATE SUBCONTRACTOR SET JobSite = NULL WHERE JobSite = '" + jobSite + "'";

For instance, if an apostrophe is used in the jobSite var, it offsets the other apostrophes in the command, and fails.

So my questions are:

1. How do I escape characters like the apostrophe and semicolon in the above query example?

2. What characters do I need to escape? I know I should escape the apostrophe, what else is dangerous?

Thanks for your help!

I'd really like to avoid asking another question, an addition to my questions above, can someone show me how to build a query like the one I have above using parameters with the UPDATE/WHERE command? I've seen lots of examples of queries using parameters, but they always use the INSERT command. — CODe, Jan 05 '11 at 00:40

score 4 · Accepted Answer · edited May 23 '17 at 11:58

4

Rather use Parameters

There is a previous stack-overflow question about it

Adding parameters in SQLite with C#

if you need more functionality you can also use Entity Framework

http://sqlite.phxsoftware.com/

Sorry not to familiar with the Syntax but the concept should same. Something like :

SQLiteCommand Command = "UPDATE SUBCONTRACTOR SET JobSite = NULL WHERE JobSite = @JobSite";
Command.Parameters.Add(new SQLiteParameter("@JobSite", JobSiteVariable));
command.ExecuteNonQuery();

edited May 23 '17 at 11:58

Community

1
1

answered Jan 05 '11 at 00:30

Gaven

371
1
6

1

Thanks! But, how do I modify my UPDATE/WHERE query to use parameters? I see plenty of examples of using parameters, but they always use INSERT. – CODe Jan 05 '11 at 00:31

Bnjmn · Answer 2 · 2011-01-05T00:15:06.557

3

to escape an apostrophe add another apostrophe...

so a string like it's should be inserted as it''s

You may also need to escape quotation marks. The way to do this is to use a backslash as an escape charater...

like so... 'and he said\"escape all those quotes\"'

You should also beware of SQL injections... depending on the type of programming language you are using there exist different functions that can help clean out any malicious code.

C# tutorial on SQL Injections for example

edited Jan 05 '11 at 00:15

answered Jan 05 '11 at 00:02

Bnjmn

1,973
1
20
34

Thanks for the contribution, did you have some literature on insight on my second question by chance? – CODe Jan 05 '11 at 00:06
@CODe I've made an amendment to my post, as per your request. – Bnjmn Jan 05 '11 at 00:15
Thanks! Per your link, how do I modify their "proper way to build an ad hoc SQL query" with an update/where command instead of an insert command? – CODe Jan 05 '11 at 00:21

score 1 · Answer 3 · answered Jan 05 '11 at 00:26

You should never concatenate strings to build SQL queries for SQLite - or for any other SQL DB if possible. It makes your code fragile and opens up potential entry points for injection attacks.

The proper way to do it is to use hosted parameters. This approach removes the need for cumbersome string filtering. I am not sure how to do that in C# for SQLite but any decent language binding for SQLite should allow you to use hosted parameters.

SQLite issues, escaping certain characters

3 Answers3

Linked