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

Discussion in 'Code Snippets' started by Mahares, Jul 31, 2015.

  1. Mahares

    Mahares Well Known Member Licensed User

    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:

    Code:
    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:
    Code:
    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:
    Code:
    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:
    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:
    Code:
    MyQuery="SELECT * FROM tblPatients WHERE Last_name LIKE '10%' "  'Displays: Hulk and Power

    Tags: SQLite, Escape
    Libraries: SQL
     
    metrick, JakeBullet70 and Erel like this.
  2. ac9ts

    ac9ts Active Member Licensed User

    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.

    Code:
    ' Remove "Illegal" characters
    Sub FixString(S As StringAs 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 StringAs 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
     
    Phayao, Craig T, edm68 and 1 other person like this.
  3. Kintara

    Kintara Member Licensed User

    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"
    Code:
    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
    Code:
    Main.SQLStores.ExecNonQuery2("INSERT INTO Stores (Item,Description,Null2,Null3, Null4, Null5,Null6,Null7, Bin) Values (?,?,?,?,?,?,?,?,?,?)",Cells)
     
  4. Mahares

    Mahares Well Known Member Licensed User

    Why don't you use something like this to insert:
    Code:
    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:
    Code:
    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:
    Code:
    txt="SELECT Description FROM Stores WHERE Description like '%''O''%'"
    Cursor1=SQL1.ExecQuery(txt)
     
    Last edited: Sep 10, 2015
  5. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    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.
     
  6. Mahares

    Mahares Well Known Member Licensed User

    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.
     
  7. Kintara

    Kintara Member Licensed User

    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 :cool:
     
  8. ac9ts

    ac9ts Active Member Licensed User

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice