B4J Question How Convert query string to smart string

[behnam_tr]

hi

how can i convert this string to smart string
i have a problem With ' character

 Dim RS2 As ResultSet=Main.Sql1.ExecQuery(" Select kala.id,kala.catid,kala.name  WHERE kala.name LIKE '%"&stext&"%'  Or kala.id='"&stext&"' " )

Dim RS2 As ResultSet=Main.Sql1.ExecQuery($"  Select kala.id,kala.catid,kala.name  WHERE kala.name LIKE ${'%stext%'}   Or kala.id=${text} "$)

 

[emexes]

The single quotes and % wildcards should be outside the the substitution delimiters eg:

Dim RS2 As ResultSet=Main.Sql1.ExecQuery($"  SELECT  kala.id,kala.catid,kala.name  WHERE  kala.name LIKE '%${stext}%'  OR  kala.id=${text} "$)

ps is the second search term ${text} supposed to be enclosed in single quotes too? Or are kala.id and text numeric?

 

[emexes]

I know it's an extra line, but it sure makes debugging easier if you gather the SQL query into a string first so that you can see what SQLite is seeing eg:

Dim Query As String = $"SELECT kala.id, kala.catid, kala.name WHERE kala.name LIKE '%${stext}%' OR kala.id = ${text}"$
Log(Query)
Dim RS2 As ResultSet = Main.Sql1.ExecQuery(Query)

 

[EnriqueGonzalez]

Just fyi this is the incorrect approach. You should use parametized queries (with ? And array of strings) doing it differently just make it more complex and insecure

 

[Erel]

Just fyi this is the incorrect approach. You should use parametized queries (with ? And array of strings) doing it differently just make it more complex and insecure

That's 100% correct.

 

[Mahares]

Using parameterized query and string literals and also an alias for the table name:

Dim RS2 As ResultSet=SQL.ExecQuery2($"SELECT K.id, K.catid, K.name FROM kala K WHERE K.name LIKE ? OR K.id =?"$ , Array As Object($"%${stext}%"$ , text))