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 gt   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

User · Answer

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

User · Answer

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  quot insert into foo values    quot  columnA  quot    columnB  quot

User · Answer

I believe you d want to escape by doubling the single quote   INSERT INTO table name  field1  field2  VALUES  123   Hello there  s

User · Answer

for replace all     in your string  use    replace     g         example    sample    St  Mary s and St  John s   escapedSample   sample replace     g

User · Answer

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

User · Answer

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

[sqlite] Escape single quote character for use in an SQLite query

Examples related to sqlite

Examples related to escaping