Android Code Snippet How To Escape The Apostrophe (single quote) And % Sign In a SQLite Query

The best way to avoid escaping certain characters like the % sign or the single quote (apostrophe) is to use a parameterized query. But If you prefer To escape them, here is a solution:
To illustrate the point we first create a table which has 6 columns, but in the queries we are only interested In finding the First_name while searching by Last_name:

txt="CREATE TABLE tblPatients (Patient_ID TEXT PRIMARY KEY, First_name TEXT,Last_name TEXT, " _
    & "Address TEXT, City TEXT, Zip TEXT )"
    SQL1.ExecNonQuery(txt)
        txt="INSERT INTO tblPatients  VALUES(?,?,?,?,?,?)"
       SQL1.ExecNonQuery2(txt, Array As Object(1234,"Alex", "Smith","123 Main Street", "New York","14567"))
       SQL1.ExecNonQuery2(txt, Array As Object(5000,"John", "Johnson","656 Main Blvrd", "Toronto","45670"))
       SQL1.ExecNonQuery2(txt, Array As Object(9873,"Marc", "O' Riley","400 Pasadena Blvrd", "Los Angeles","04670"))
       SQL1.ExecNonQuery2(txt, Array As Object(4008,"Hulk", "10% body fat","5th Avenue", "Tel Aviv","Ger49"))
       SQL1.ExecNonQuery2(txt, Array As Object(2846,"Jimmy", "O' Malley","209 Carson Way", "Belfast","DD764"))
       SQL1.ExecNonQuery2(txt, Array As Object(4677,"Power", "100% Super man ","Wood Circle", "Kiev","6633"))
       SQL1.ExecNonQuery2(txt, Array As Object(1876,"Adam", "Osborne","671 Hiway 20 Blvrd", "Berne","SW70"))

To escape the apostrophe (single QUOTE), put 2 apostrophes next to each other:

Dim MyQuery As String
MyQuery="SELECT * FROM tblPatients WHERE Last_name LIKE 'O''%' "  ' Displays Marc and Jimmy
Cursor1=SQL1.ExecQuery(MyQuery)
For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log ("first name: " & Cursor1.GetString("First_name"))
Next

To use a parameterized query, which Is the preferred way, the code below yields the same result:

MyQuery="SELECT * FROM tblPatients WHERE Last_name LIKE ?"  
Cursor1=SQL1.ExecQuery2(MyQuery,Array As String("O'%")) ' Displays Marc and Jimmy

To escape the % sign which is frequently used with the LIKE operator in a query, use this code:

MyQuery="SELECT * FROM tblPatients WHERE Last_name LIKE '10\%%' ESCAPE '\'"   'Display only Hulk 
Cursor1=SQL1.ExecQuery(MyQuery)

The below query is not escaped, hence yields a very different recordset from the one above it:

MyQuery="SELECT * FROM tblPatients WHERE Last_name LIKE '10%' "  'Displays: Hulk and Power

Tags: SQLite, Escape
Libraries: SQL

 

[ac9ts]

Another way is to replace the characters that "break" the SQL commands with other characters to insert/store into the database. When the data is read back, re-store the character to the original.

' Remove "Illegal" characters
Sub FixString(S As String) As String
   
    ' Single Quote replaced with chr(31)
    ' Double Quote replaced with chr(30)
   
    S=S.Replace("'",Chr(31))
    S=S.Replace(Chr(34),Chr(30))
   
    Return S
   
End Sub

' Restore "Illegal" characters
Sub RestoreString(S As String) As String

    ' Chr(31) replaced with Single Quote
    ' Chr(30) replaces with Double Quote, Chr(34)
   
   
    S=S.Replace(Chr(31), "'")
    S=S.Replace(Chr(30),Chr(34))

    Return S

End Sub

 

[Kintara]

OK, what do you do in the following:
List1 is loaded from a CSV file
Cells() is a list from List1.get(i)
The CSV file may (and usually does) contain an apostrophe
so Cells (1) may contain "Obselete 'O' Ring"

Main.SQLStores.ExecNonQuery("INSERT INTO Stores (Item,Description, Bin)  Values (" & Cells(0) & "," &  Cells(1) & "," & Cells(8) & ")")

I did think about using the following, but I am not inserting all the Cells() elements

Main.SQLStores.ExecNonQuery2("INSERT INTO Stores (Item,Description,Null2,Null3, Null4, Null5,Null6,Null7, Bin) Values (?,?,?,?,?,?,?,?,?,?)",Cells)

 

[Mahares]

Why don't you use something like this to insert:

Main.SQLStores.ExecNonQuery2("INSERT INTO Stores (Item,Description, Bin)  Values (?,?,?)",Array As String(Cells(0),Cells(1),Cells(8)))

Then you can do this to view data:

txt="SELECT Description FROM Stores WHERE Description like ?"
Cursor1=SQL1.ExecQuery2(txt,Array As String("%'O'%"))  'displays: Obselete 'O' Ring

You can alternatively use this less desirable way to view:

txt="SELECT Description FROM Stores WHERE Description like '%''O''%'"
Cursor1=SQL1.ExecQuery(txt)

 

[Jmu5667]

The only way to overcome this issue with the ' is any text that is being written in an SQL statement replace ' with '' ( ' x 2), two of them.

 

[Mahares]

The only way to overcome this issue with the ' is any text that is being written in an SQL statement replace ' with '' ( ' x 2), two of them.

That is not correct. If you use a parameterized query, you will not have to escape the single quote. In addition, your statement was already addressed in post #1.

 

[Kintara]

Main.SQLStores.ExecNonQuery2("INSERT INTO Stores (Item,Description, Bin)  Values (?,?,?)",Array As String(Cells(0),Cells(1),Cells(8)))

Mahares, that works for me. It was just the Array As String(Cells(0),Cells(1),Cells(8) bit that I was lacking. Thanks.
By the way; I don't have any control what is in the CSV file and the apostrophe can appear in any of 9 thousand records in different text guises ('O' Ring ; 'PRINTING AND REJECT MODULE' ; FAN ASSY'S; etc) so I would find it difficult to escape every one.

Kintara

 

[ac9ts]

Kintara,

I had the same issue where the apostrophe is input by the user and can appear anywhere. Look at my solution in post #2. I call FixString when I insert into the database and RestoreString when reading back from the database. You can put any other translations in the subs as required.