Escape single quote character for use in an SQLite query

Question

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?

score 311 · Accepted Answer · edited Jan 23 '18 at 23:18

311

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

edited Jan 23 '18 at 23:18

user4157124

2,452
12
22
36

answered Mar 02 '09 at 19:14

Ryan Guill

12,814
4
34
48

8

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
1

Can't we just use double quotes? like: INSERT INTO table_name (field1, field2) VALUES (123, "Hello there's"); ? – JacksOnF1re Jun 08 '18 at 10:18
Also, depending on the lifetime of the string, the first step might be to convert '' to ' before doubling them up again. – Gary Z Jul 11 '18 at 00:16
1

@JacksOnF1re In SQL, double quotes are for identifiers not strings. Like `INSERT INTO "table_name"("field1", "field2") VALUES('value 1', 'value 2');` It's a quirk of compatibility that double quotes work like as literals at all. See: https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted – Steven Fisher Apr 08 '21 at 00:59

score 47 · Answer 2 · answered Mar 02 '09 at 19:15

47

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

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

answered Mar 02 '09 at 19:15

overslacked

4,057
22
28

score 12 · Answer 3 · answered Jan 04 '18 at 01:26

12

for replace all (') in your string, use

.replace(/\'/g,"''")

example:

sample = "St. Mary's and St. John's";
escapedSample = sample.replace(/\'/g,"''")

answered Jan 04 '18 at 01:26

M.Fahri

161
1
6

score 6 · Answer 4 · answered Dec 11 '17 at 07:51

6

Just in case if you have a loop or a json string that need to insert in the database. Try to replace the string with a single quote . here is my solution. example if you have a string that contain's a single quote.

String mystring = "Sample's";
String myfinalstring = mystring.replace("'","''");

 String query = "INSERT INTO "+table name+" ("+field1+") values ('"+myfinalstring+"')";

this works for me in c# and java

answered Dec 11 '17 at 07:51

Charlie D. Eupeña

69
1
2

Note: Replace("'","''") in C#. (Only change is capital R.) – Kenneth Evans Jul 30 '20 at 02:40

score 1 · Answer 5 · answered Dec 05 '17 at 22:04

In C# you can use the following to replace the single quote with a double quote:

 string sample = "St. Mary's";
 string escapedSample = sample.Replace("'", "''");

And the output will be:

"St. Mary''s"

And, if you are working with Sqlite directly; you can work with object instead of string and catch special things like DBNull:

private static string MySqlEscape(Object usString)
{
    if (usString is DBNull)
    {
        return "";
    }
    string sample = Convert.ToString(usString);
    return sample.Replace("'", "''");
}

score 0 · Answer 6 · answered Jun 30 '20 at 20:00

0

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

answered Jun 30 '20 at 20:00

Dan Tanner

1,739
2
19
34

Double quotes in sqlite means "this is an identifier, not a string." So while it's fine for some hacking, you wouldn't want it in production. – Steven Fisher Apr 07 '21 at 23:16

Escape single quote character for use in an SQLite query

6 Answers6

Linked

Related