Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
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". 
                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, 2013 at 12:48
                Can't we just use double quotes? like: INSERT INTO table_name (field1, field2) VALUES (123, "Hello there's"); ?
– JacksOnF1re
                Jun 8, 2018 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, 2018 at 0:16
                @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: sqlite.org/…
– Steven Fisher
                Apr 8, 2021 at 0:59
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
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("'", "''");
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)";
                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 7, 2021 at 23:16
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.