Escape single quote character for use in an SQLite query

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?

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

Solution 2:

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

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

Solution 3:

for replace all (') in your string, use

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

example:

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

Solution 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

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