SQL statement containing semicolon causes error

Question

It's already been reported by a few people that a semicolon inside a quoted string can cause an SQL error.

My current setup is an SQLite 3 database and I'm trying to perform an insert, one of my new data's fields contain some HTML, and as part of that I have html entities which end in a semicolon, example - è

A simple stripped down example query I'm trying is the following:

insert into my_table (some_field) values ('Hello; world');

And the errors I am getting are:

1)

insert into my_table (some_field) values ('Hello
Error : unrecognized token: "'Hello"

2)

world')
Error : near "world": syntax error

So the SQL engine is obviously seeing the semicolon in Hello; world and interpreting it as an end of statement. I've asked a few other people to test this and some people this works find when they are using a SQLite GUI which leads me to believe it's just an SQL setting that needs changing.

It seems that at least by default the SQL engine wants semi colons only for end of statements, does anyone know how to change this?

It's very strange, please try the following query:

DROP TABLE IF EXISTS djp;
CREATE Table djp ("mykey" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "name" VARCHAR(25));
INSERT INTO djp (name) VALUES ('This is test two; OK?');
SELECT * FROM djp;

Please note that if I insert without specifying which fields to insert (making my auto increment a little pointless), the error doesn't happen...

INSERT INTO djp VALUES (1, 'This is test two; OK?');

Answer 1

As stated in the comments, I ran your code example in the stand alone sqlite3.exe engine and it executed as any other text would (with or without the semi-colon). Since you pointed out with the INSERT INTO Foo VALUES () and INSERT INTO Foo (column) VALUES () quirk, I agree that this isn't a problem with your code, it is a bug with Navicat.

Answer 2

As mentioned in Sam's answer the issue is the SQLite editor and not SQLite itself. In the case of this question the problem was with Navicat (Note: now patched as of 10.1.2). I ran into the same issue but for me it was with the same issue with SqlQuery.

I struggled with this until I realized many of the free SQLite clients/editors do not have with semicolon in string support!

I eventually found MesaSQLite which supports semicolons within strings and is free (but has some other slight drawbacks).

I am posting this answer because I found this question easily while search engining around but I did not find the answer as easily. Best of luck to others who come across this.

Other questions that might help:

Mac SQLite editor

What are good open source GUI SQLite database managers?

Remember, the problem is not SQLite! :)