[sqlite] 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?

for replace all (') in your string, use

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

example:

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

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

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

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

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

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