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!

Answer 1

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

Answer 2

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