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

VictorTandil · Feb 28, 2018

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.

B4X:

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.

Ormente · Feb 28, 2018

I see no obvious error, but you should avoid naming columns like reserved words (DESC). So, try to qualify the request :

B4X:

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?

klaus · Feb 28, 2018

Try this one:

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

At the end "%'" instead of "'%'".

OliverA · Feb 28, 2018

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

LucaMs · Feb 28, 2018

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.

OliverA · Feb 28, 2018

LucaMs said:
you should use ExecQuery2, because it solves some problems, like escaping.

This ^ 10

Ormente · Feb 28, 2018

Nice catch Klaus! I need to change my glasse

Mahares · Feb 28, 2018

Just another way with parameterized query and collate no case:

B4X:

Cursor1 = SQL1.ExecQuery2("SELECT LAT, LON, [DESC] FROM MARKERS WHERE [DESC] LIKE ? COLLATE NOCASE"), _
    Array As String( "%" & DLG.Input & "%")

VictorTandil · Feb 28, 2018

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

B4X:

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

Ormente · Mar 1, 2018

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.

VictorTandil · Mar 1, 2018

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.

OliverA · Mar 1, 2018

VictorTandil said:
low-end phones causes a freeze of several seconds

Switch to SQL with Wait For. That should keep the UI from freezing. See https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content.

KMatle · Mar 1, 2018

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)

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

VictorTandil

Member

Ormente

Member

klaus

Expert

OliverA

Expert

LucaMs

Expert

OliverA

Expert

Ormente

Member

Mahares

Expert

VictorTandil

Member

Ormente

Member

VictorTandil

Member

OliverA

Expert

KMatle

Expert

Similar Threads

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

Member

Member

Expert

Expert

Expert

Expert

Member

Expert

Member

Member

Member

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency