Escape single quote character for use in an SQLite query

jpm_35478 0
Viewed 167308

I wrote the database schema (only one table so far), and the INSERT statements for that table in one file. Then I created 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');

The problem is the escape character for a single quote. I also tried double escaping the single quote (using \\\' instead of \'), but that didn't work either. What am I doing wrong?

sqlite escaping
7 Answers

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

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".

Ryan_Guill_71860

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

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

In bash scripts, I found that escaping double quotes around the value was necessary for values that could be null or contained characters that require escaping (like hyphens).

In this example, columnA's value could be null or contain hyphens.:

sqlite3 $db_name "insert into foo values (\"$columnA\", $columnB)";
user_573570

Demonstration of single quoted string behavior where complexity or double quotes are not desired.

Test:

SELECT replace('SAMY''S','''''','''');

Output:

SAMY'S

SQLite version:

SELECT sqlite_version();

Output:

3.36.0
user_76619600
Related