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?

Philoprogenitive

Answer 1

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

Roommate

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). – Golem

Can't we just use double quotes? like: INSERT INTO table_name (field1, field2) VALUES (123, "Hello there's"); ? – Wallsend

Also, depending on the lifetime of the string, the first step might be to convert '' to ' before doubling them up again. – Ambuscade

@Wallsend 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: sqlite.org/… – Guyguyana

As @DonalFellows mentioned: consider prepared statements. See a good example here #61795229. – Lipman

Answer 2

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

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

Colorimeter

Answer 3

for replace all (') in your string, use

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

example:

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

Canaanite

Answer 4

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

Darondarooge

Note: Replace("'","''") in C#. (Only change is capital R.) – Secrecy

Answer 5

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("'", "''");
}

Highhat

Answer 6

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

Leo

Answer 7

According to the doc:

• "" surrounded by "" are evaluated to " so """" are evaluated to "" and """""" are evaluated to """ (even numbers) but ", """ and """"" (odd numbers) do not work. *"" can be used to surround identifiers like table and column names, SELECT, WHERE, etc and [] and `` can be also used to surround identifiers.

• '' surrounded by '' are evaluated to ' so '''' are evaluated to '' and '''''' are evaluated to ''' (even numbers) but ', ''' and ''''' (odd numbers) do not work. *'' can be used to surround string literals like values.

*Don't use "" to surround string literals because there is error according to my experiments.

*Don't use '' to surround identifiers because sometimes do not work properly according to my experiments.

For example, you can create my"table with my"column as shown below:

CREATE TABLE "my""table" (
  "my""column" TEXT
);

Or:

CREATE TABLE [my"table] (
  [my"column] TEXT
);

Or:

CREATE TABLE `my"table` (
  `my"column` TEXT
);

Then, you can insert a row with my"value for my"column to my"table as shown below:

INSERT INTO "my""table" ("my""column") VALUES ('my"value');

Or:

INSERT INTO [my"table] ([my"column]) VALUES ('my"value');

Or:

INSERT INTO `my"table` (`my"column`) VALUES ('my"value');

Finally, you can get the row as shown below:

SELECT * FROM "my""table";

Or:

SELECT * FROM [my"table];

Or:

SELECT * FROM `my"table`;

And, you can create my'table with my'column as shown below:

CREATE TABLE "my'table" (
  "my'column" TEXT
);

Or:

CREATE TABLE [my'table] (
  [my'column] TEXT
);

Or:

CREATE TABLE `my'table` (
  `my'column` TEXT
);

Then, you can insert a row with my'value for my'column to my'table as shown below:

INSERT INTO "my'table" ("my'column") VALUES ('my''value');

Or:

INSERT INTO [my'table] ([my'column]) VALUES ('my''value');

Or:

INSERT INTO `my'table` (`my'column`) VALUES ('my''value');

Finally, you can get the row as shown below:

SELECT * FROM "my'table";

Or:

SELECT * FROM [my'table];

Or:

SELECT * FROM `my'table`;

Fuselage

Answer 8

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

Contango

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. – Guyguyana