Android Question SQLite SELECT ... LIKE ... UPPER() {SOLVED}

Discussion in 'Android Questions' started by VictorTandil, Feb 28, 2018.

  1. VictorTandil

    VictorTandil Member Licensed User

    Hi. I have a database in SQLite format and I need to do a partial search with a certain text. I would like to know what is the correct way to do it, since I have tried LIKE '%text%' but apparently does not yield results. Also, I used the UPPER (text) function to simplify the search.

    Code:
    Dim DLG as InputDialog
    Dim SQL1 as SQL
    Dim Cursor1 as Cursor
    '.... SQL1.Initialize ....
    '.... DLG.Show
    '.... DESC is a TEXT field in MARKERS table
    '.... Bla bla bla

    Cursor1 = SQL1.ExecQuery(
    "SELECT LAT, LON, DESC FROM MARKERS WHERE UPPER(DESC) LIKE '%" & DLG.Input.ToUpperCase & "'%")
    This Code does not produce errors, but neither results.
     
  2. Ormente

    Ormente Member Licensed User

    I see no obvious error, but you should avoid naming columns like reserved words (DESC). So, try to qualify the request :
    Code:
    Cursor1 = SQL1.ExecQuery("SELECT lat, lon, markers.desc FROM markers WHERE UPPER(markers.desc) LIKE '%" & DLG.Input.ToUpperCase & "'%")
    (You can also use write the "faulty" column name inside square brackets)

    Did you display the built request with log() to check it? And try it "manually" against your database?
     
    lemonisdead, mangojack and Mahares like this.
  3. klaus

    klaus Expert Licensed User

    Try this one:

    Cursor1 = SQL1.ExecQuery("SELECT LAT, LON, DESC FROM MARKERS WHERE UPPER(DESC) LIKE '%" & DLG.Input.ToUpperCase & "%'")

    At the end "%'" instead of "'%'".
     
    lemonisdead, Ormente and OliverA like this.
  4. OliverA

    OliverA Well-Known Member Licensed User

    Without @klaus' correction you were looking for a match that ends with your DLG.Input value, instead of containing the value at any point.
     
  5. LucaMs

    LucaMs Expert Licensed User

    OT.

    "I" don't like that query:

    1) usually we should write db queries inside classes or at least code modules;
    2) you're not checking the user input;
    3) you should use ExecQuery2, because it solves some problems, like escaping.
     
    Ormente and OliverA like this.
  6. OliverA

    OliverA Well-Known Member Licensed User

    This ^ 10
     
    LucaMs likes this.
  7. Ormente

    Ormente Member Licensed User

    Nice catch Klaus! I need to change my glasse :cool:
     
  8. Mahares

    Mahares Well Known Member Licensed User

    Just another way with parameterized query and collate no case:
    Code:
    Cursor1 = SQL1.ExecQuery2("SELECT LAT, LON, [DESC] FROM MARKERS WHERE [DESC] LIKE ? COLLATE NOCASE"), _
        
    Array As String"%" & DLG.Input & "%")
     
    Last edited: Feb 28, 2018
    Erel, LucaMs, lemonisdead and 2 others like this.
  9. VictorTandil

    VictorTandil Member Licensed User

    Thanks for your suggestions. Finally, it worked in this way:

    Code:
    Sub FindMarkerButton_Click
        
    Dim Dlg As InputDialog
        Dlg.Hint = 
    "Buscar Marcador"
        Dlg.InputType = Dlg.INPUT_TYPE_TEXT
        Dlg.Show(
    "Ingrese el nombre del Marcador a buscar""Buscar Marcador""Aceptar""Cancelar"""Null)
        
    If Dlg.Response = DialogResponse.POSITIVE Then
            
    If Dlg.Input <> "" Then
                
    Dim Cursor1 As Cursor
                
    Dim Sf As StringFunctions
                Sf.Initialize
                Cursor1 = Starter.SQL1.ExecQuery(
    "SELECT LAT, LON, [DESC] FROM MARKERS WHERE [DESC] LIKE '%" & Dlg.Input.ToUpperCase & "%'")
                
    If Cursor1.RowCount > 0 Then
                    
    For i = 0 To Cursor1.RowCount - 1
                        Cursor1.Position = i
                        
    Dim Busqueda As String = Cursor1.GetString("DESC")
                        
    Log("Busqueda: " & Busqueda)
                        
    Dim Pos As CameraPosition
                        Pos.Initialize(Cursor1.GetString(
    "LAT"), Cursor1.GetString("LON"), gmap.CameraPosition.Zoom)
                        gmap.MoveCamera(Pos)
                        
    Dim m1 As Marker = gmap.AddMarker2(Cursor1.GetString("LAT"), Cursor1.GetString("LON"), Cursor1.GetString("DESC"), gmap.HUE_BLUE)
                    
    Next
                
    Else
                    
    Log("No se han encontrado resultados")
                
    End If
                Cursor1.Close
            
    Else
                
    Msgbox("Debe establecer un nombre para la busqueda el Marcador""Error")
            
    End If
        
    End If
    End Sub
     
  10. Ormente

    Ormente Member Licensed User

    Congrats :)
    Now that it works you can improve it so it's less vulnerable, because for now you trust the user provided data too much.
    As the others said it's better to use escaping, because a user can accidentaly enter a "malformed" string (think: what if she enter a string containing a single quote?), or intentionaly craft a request to hack your app.

    To learn more about the risk, look at sql injection on google. Most often it's talked about websites, but any software trusting user input is vulnerable.
     
  11. VictorTandil

    VictorTandil Member Licensed User

    It is true. I've tried entering a letter only and it throws hundreds of results, which in low-end phones causes a freeze of several seconds. I'm going to narrow the search a bit with certain parameters. Thank you.
     
  12. OliverA

    OliverA Well-Known Member Licensed User

    Erel likes this.
  13. KMatle

    KMatle Expert Licensed User

    If a query is slow, it is always caused by a bad db design (structure and/or missing indexes). Often users retrieve thousands of rows which is bad, too. Just retrieve what an user can handle (max. 50 rows or less at one time). In most cases it doesn't make sense to show all entries starting with e.g. "A" if you get 1000 rows. So if a device takes seconds to do a query there surely is a design problem. Good queries take about max. 0.2 to 0.5 secs (even 0.5 can be seen as bad)
     
    OliverA likes this.
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