Take the 2-minute tour ×

I'm trying to create a new SQLite database from scratch by writing the schema for my new tables (only one so far though), and the INSERT statements for that table in one file.

Then I go into sqlite3 and try to create the database as follows:

$ sqlite3 newdatabase.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .read ./schema.sql
SQL error near line 16: near "s": syntax error

Line 16 of my file looks something like this:

INSERT INTO table_name (field1, field2) VALUES (123, 'Hello there\'s');

So the problem there is the escape character for a single quote, but I cannot figure out why this is not working. I already tried double escaping the single quote (using \\' instead of \'), but that didn't work either.

What am I doing wrong here?

share|improve this question
after submitting my question it looks like maybe you already tried this but in your question it isn't formatted correctly or something... what do you mean by (using \' instead of \') ? –  Ryan Guill Mar 2 '09 at 19:16
add comment

2 Answers

up vote 40 down vote accepted

I am not familiar with SQLite, but did you try doubling up the single quotes? many databases expect it that way. So it would be

INSERT INTO table_name (field1, field2) VALUES (123, 'Hello there''s');

Edit: Since this question seems to get a lot of traffic, here are some relevant useful links.

Documentation: http://www.sqlite.org/lang_expr.html

Relevant quote from the documentation:

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. ... A literal value can also be the token "NULL".

share|improve this answer
That works indeed. I just assumed that it would escape single quotes with a backslash. Thanks! –  jpm Mar 2 '09 at 19:17
Also, consider using bound parameters if the host language supports them (most do, but the SQLite shell doesn't). The SQL would then be INSERT INTO table_name (field1, field2) VALUES (?, ?) and the values would be supplied directly (and without substitutions). –  Donal Fellows May 20 '13 at 12:48
add comment

I believe you'd want to escape by doubling the single quote:

share|improve this answer
add comment

Your Answer


