Use prepared statement and LIKE to search part of a string

Ryan_Hartz · July 29, 2020, 8:54pm

Hi. I have been querying a database table with something like this:
dim sql as String = "SELECT * FROM RDexam WHERE Question LIKE '%" + txtSearch.Text + "%'"

This would allow me to search any part of the Question field with the use of the percent signs. However, as you can easily tell, if I wanted to search for Ryan’s, I would receive an error since the apostrophe throws the SQL statement off. I started looking into the prepared statements as this seems to be the way to help against characters that would cause an error, like the apostrophe. However, I am having a hard time figuring how to search for part of the field. If I change the above to this:
dim sql as String = "SELECT * FROM RDexam WHERE Question LIKE ?"

This may be safe for the apostrophe, but just using LIKE forces me to type in the whole field instead of just one or two words. I tried playing around with the statement by adding in some percent signs, but this was a no go. In the search bar, putting in a percent sign before and/or after the search word did work, but I wanted to automate this

Thoughts?

brian_franco · July 29, 2020, 9:00pm

Yes prepared statements BUT you could always use ReplaceAll (API 1)

return replaceAll( O’riely, “’”, “’’” )

Maurizio_Rossi · July 29, 2020, 9:11pm

For documentation about LIKE look at SQL Language Expression and section:
5. The LIKE, GLOB, REGEXP, and MATCH operators
You will find infos on how to escape special chars like % and _.
Prefer the prepared statement as a better alternative to string manipulation.
Of course this can be automated.

Norman_Palardy · July 29, 2020, 9:12pm

try something like

dim sql as String = "SELECT * FROM RDexam WHERE Question LIKE  ?"
dim ps as preparedsqlstatement = db.prepare(sql)
// appropriate binds for your db

ps.sqlexecute( "%" + txtSearch.Text + "%" )

Ryan_Hartz · July 29, 2020, 10:54pm

Thanks all!

I was actually thinking of trying this when just out for a walk. I’ll give this a try tomorrow and post back

Kem_Tekinay · July 30, 2020, 2:29am

Remember that the user might enter % or _, so have a plan for that.

Ryan_Hartz · July 30, 2020, 2:51am

I was curious about that and was going to do a test with that too. So if I wanted to search the fields that contained “50%” for example, this would still not work?

Currently, I am looking to implement this for the apps I’ve built for myself, so I would use caution for any erroneous characters. But in the future, I plan on using a search feature in one of my customer apps, so I may need to plan accordingly for anything the user can enter

Kem_Tekinay · July 30, 2020, 3:07am

It would work in that you’d match anything that contained “50”.

“%” would match the literal percent. Use ReplaceAll to escape the backslash, percent, and underscore in your search string by swapping them out with \\, \%, and \_ respectively.

Thom_McGrath · July 30, 2020, 3:23am

Yes prepared statements BUT you could always use ReplaceAll (API 1)
return replaceAll( O’riely, “’”, “’’” )

Don’t do that. Escaping the apostrophes is not sufficient data sanitization.

David_Cox · July 30, 2020, 7:55am

Parsing text differs whether inside or outside a LIKE statement, plus what database brand you’re using, so I use the parser below. I am still confused out to find a ‘%’ within some text in MySQL!


Protected Function getFormat(SQLBrand As String, myFieldValue As String, LIKEcommand As Boolean = False) as String
  'Convert string containing a single quotation or slash into escape codes
Select Case SQLBrand

Case “DB2”, “PostgreSQL”, “Postgres”

myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote

'if needed, fix the below with: UPDATE logfiledata SET sourcelocation = REPLACE(sourcelocation, ‘\’, ‘’) WHERE sourcelocation LIKE ‘C:\\%’

If LIKEcommand Then

myFieldValue = ReplaceAll(myFieldValue, “”, “\”) 'this causes DOS paths in fields to not be recognised

myFieldValue = ReplaceAll(myFieldValue, “;”, “;”) 'semi-colons may be used to put multiple statements on one line, so make it a literal

myFieldValue = ReplaceAll(myFieldValue, “%”, “%”) 'percentages might muck up LIKE if they’re in the string being searched

myFieldValue = ReplaceAll(myFieldValue, “_”, “_”) 'underscore can act as a single character wild card in Postgres

End If

Return DefineEncoding(myFieldValue, Encodings.UTF8)
Case “SQLite”, “CubeSQL”

myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote

'myFieldValue = ReplaceAll(myFieldValue, “&”, “&&”) 'causes 2for1 ‘&’ entries in FileNameExtreme

Return DefineEncoding(myFieldValue, Encodings.UTF8)
Case “MySQL”

myFieldValue = ReplaceAll(myFieldValue, “’”, “’”) ‘escape the single quote

If LIKEcommand Then

myFieldValue = ReplaceAll(myFieldValue, “”, “\”)

myFieldValue = ReplaceAll(myFieldValue, "’", “’”) 'escape the single quote

myFieldValue = ReplaceAll(myFieldValue, “;”, “;”) 'semi-colons may be used to put multiple statements on one line, so make it a literal

'myFieldValue = ReplaceAll(myFieldValue, “%”, “%”) 'percentages might muck up LIKE if they’re in the string being searched - doesn’t work, imports as ‘%’, not ‘%’

End If

Return DefineEncoding(myFieldValue, Encodings.UTF8)
Case “MSSQL”

myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote

Return DefineEncoding(myFieldValue, Encodings.UTF8)
Case “JavaScript”

'myFieldValue = ReplaceAll(myFieldValue, “ﬁ”, “”) 'Shift-Option-5 fails to import in HTML

'myFieldValue = ReplaceAll(myFieldValue, “ﬂ”, “”) 'Shift-Option-6 fails to import in HTML

‘myFieldValue = ReplaceAll(myFieldValue, “˘”, “”) ‘Shift-Option-> fails to import in HTML

myFieldValue = ReplaceAll(myFieldValue, "’", "’") 'escape the single quote

Return DefineEncoding(myFieldValue, Encodings.UTF8)
Case Else

myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote

Return DefineEncoding(myFieldValue, Encodings.UTF8)

End Select End Function

AlbertoD · July 30, 2020, 12:16pm

Just testing if I can use the code format:

Protected Function getFormat(SQLBrand As String, myFieldValue As String, LIKEcommand As Boolean = False) as String 'Convert string containing a single quotation or slash into escape codes

Select Case SQLBrand
Case “DB2”, “PostgreSQL”, “Postgres”
myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote
'if needed, fix the below with: UPDATE logfiledata SET sourcelocation = REPLACE(sourcelocation, ‘\’, ‘’) WHERE sourcelocation LIKE ‘C:\\%’
If LIKEcommand Then
myFieldValue = ReplaceAll(myFieldValue, “”, “\”) 'this causes DOS paths in fields to not be recognised
myFieldValue = ReplaceAll(myFieldValue, “;”, “;”) 'semi-colons may be used to put multiple statements on one line, so make it a literal
myFieldValue = ReplaceAll(myFieldValue, “%”, “%”) 'percentages might muck up LIKE if they’re in the string being searched
myFieldValue = ReplaceAll(myFieldValue, “_”, “_”) 'underscore can act as a single character wild card in Postgres
End If
Return DefineEncoding(myFieldValue, Encodings.UTF8)

Case “SQLite”, “CubeSQL”
myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote
'myFieldValue = ReplaceAll(myFieldValue, “&”, “&&”) 'causes 2for1 ‘&’ entries in FileNameExtreme
Return DefineEncoding(myFieldValue, Encodings.UTF8)

Case “MySQL”
myFieldValue = ReplaceAll(myFieldValue, “’”, “’”) ‘escape the single quote
If LIKEcommand Then
myFieldValue = ReplaceAll(myFieldValue, “”, “\”)
myFieldValue = ReplaceAll(myFieldValue, "’", “’”) 'escape the single quote
myFieldValue = ReplaceAll(myFieldValue, “;”, “;”) 'semi-colons may be used to put multiple statements on one line, so make it a literal
'myFieldValue = ReplaceAll(myFieldValue, “%”, “%”) 'percentages might muck up LIKE if they’re in the string being searched - doesn’t work, imports as ‘%’, not ‘%’
End If
Return DefineEncoding(myFieldValue, Encodings.UTF8)

Case “MSSQL”
myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote
Return DefineEncoding(myFieldValue, Encodings.UTF8)

Case “JavaScript”
'myFieldValue = ReplaceAll(myFieldValue, “ﬁ”, “”) 'Shift-Option-5 fails to import in HTML
'myFieldValue = ReplaceAll(myFieldValue, “ﬂ”, “”) 'Shift-Option-6 fails to import in HTML
‘myFieldValue = ReplaceAll(myFieldValue, “˘”, “”) ‘Shift-Option-> fails to import in HTML
myFieldValue = ReplaceAll(myFieldValue, "’", "’") 'escape the single quote
Return DefineEncoding(myFieldValue, Encodings.UTF8)

Case Else
myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote
Return DefineEncoding(myFieldValue, Encodings.UTF8)

End Select
End Function

Just copy the code, paste into my post, select all code and then select the Preformatted Text.

Thank you David.

Ryan_Hartz · July 30, 2020, 3:44pm

Getting closer, but I’m still just missing something. So I am working off of the example project PreparedStatements in the examples folder. I also modified the .txt file in the examples folder to include this in the FirstName column since I am trying to search with an apostrophe:

Paul’s house is actually smaller than Ryan’s summer house

Here is the code in the Search method in the example. I added an extra “%” before searchString so the search can be any part of the field:
// perform the search
Var rs As RowSet

Try
  rs = db.SelectSQL("SELECT * FROM Customers WHERE FirstName LIKE ?;", "%" + searchString + "%")
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  Return
End Try

If rs <> Nil Then
  ResultsList.RemoveAllRows
  ResultsList.ColumnCount = rs.ColumnCount
  ResultsList.ColumnWidths = "100,100,100,100,100,100,100,100,100,100,100"
  ResultsList.HasHeader = True
  Var hasHeadings As Boolean
  While rs.AfterLastRow <> True
    ResultsList.AddRow("")
    For i As Integer = 0 To rs.ColumnCount - 1
      If Not hasheadings Then ResultsList.HeaderAt(i) = rs.ColumnAt(i).Name
      ResultsList.CellValueAt(ResultsList.LastAddedRowIndex, i) = rs.ColumnAt(i).StringValue
    Next
    rs.MoveToNextRow
    hasHeadings = True
  Wend
  rs.Close
  
End If

Search “paul”, it works. Search “paul’s”, it works. Search “ryan” and “ryan’s”, and these work too.

Ok, so I took this whole method and dropped it into my project but replaced with the below so it fits my project. The only pieces I omitted were the ColumnCount, ColumsWidths, and HasHeader pieces:
// perform the search
Var rs As RowSet

Try
  rs = dbTests.SelectSQL("SELECT * FROM RDexam WHERE Question LIKE ?;", "%" + searchString + "%")
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  Return
End Try

If rs <> Nil Then
  lbxID.RemoveAllRows
  
  While rs.AfterLastRow <> True
    lbxID.AddRow(rs.Column("ID").StringValue)
    
    lbxID.CellValueAt(lbxID.LastAddedRowIndex, 1) = rs.Column("DateRev").StringValue
    
    rs.MoveToNextRow
    
  Wend
  rs.Close
  
End If

This is one of the example questions I am practicing on since I know it contains an apostrophe:

At 12:50pm, Matt’s blood sugar level was 55 mg/dL. He drank 4 oz apple juice. At 1:05pm, he checked his levels again, which is now 65 mg/dL. What should Matt do next?

Search “matt”, get the result. However, search “matt’s”, and the listbox is empty. I am using basically the exact same code as in the examples folder but cannot get this to get the search when an apostrophe is included. What am I missing?

Side question, is it possible to search multiple columns in the table with the LIKE statement? Ultimately, this is what I want my sql statement to look like, but this did not produce any results even with the search of “matt”:
"SELECT * FROM RDexam WHERE TestSet LIKE ? OR Question LIKE ? OR ID LIKE ? OR Solution LIKE ? ORDER BY ID"

Norman_Palardy · July 30, 2020, 3:46pm

Its possible what got inserted looks like an apostrophe but is actually a different character
Smart quotes tends to do this sort of thing

yes you can do that

Ryan_Hartz · July 30, 2020, 3:58pm

That is interesting. So I just tried this out in my database manager (an app I built with Xojo to manage the question database). The question field is a textarea. I typed in an apostrophe (small straight up and down character), and it automatically converted to a slightly slanted-looking apostrophe. I’ve never noticed that before.

So now, is it possible to change all of these in the database to the actual apostrophe (I don’t want to do this) or make it so the search can find this modified apostrophe. I do also use some other characters, such as quotation marks, asterix, and various punctuation, so I’d have to check if any of these normally change to this smart quote. Do you know?

Something must be missing then, because this didn’t work. Does there need to be any commas or semi-colon separating each OR?
rs = dbTests.SelectSQL("SELECT * FROM RDexam WHERE TestSet LIKE ? OR Question LIKE ? OR ID LIKE ? OR Solution LIKE ? ORDER BY ID;", "%" + searchString + "%")

Norman_Palardy · July 30, 2020, 4:08pm

So now, is it possible to change all of these in the database to the actual apostrophe (I don’t want to do this) or make it so the search can find this modified apostrophe. I do also use some other characters, such as quotation marks, asterix, and various punctuation, so I’d have to check if any of these normally change to this smart quote. Do you know?

should be possible
the exact update sql may vary depending on what DB were talking about

I forget the unicode character for that one

for each ? you need to pass a parameter
since you have several in there you’d need to pass a criteria that works for each one
that can be several copies of the same one or different ones

TimStreater · July 30, 2020, 5:05pm

Can’t you do:

rs = dbTests.SelectSQL(“SELECT * FROM RDexam WHERE TestSet LIKE ?1 OR Question LIKE ?1 OR ID LIKE ?1 OR Solution LIKE ?1 ORDER BY ID;”, “%” + searchString + “%”)

Norman_Palardy · July 30, 2020, 5:15pm

depends on the DB really

and I wasnt sure they would all accept a string parameter

hence the more generic advice

Ryan_Hartz · July 30, 2020, 5:27pm

This does work, thank you Tim! And SQLite. Sorry, should have mentioned that earlier

Decimal value 8217. I discovered this when I remembered last year’s XOJO.CONNECT meeting and I sat in on @Kem_Tekinay’s presentation of Text Encoding. I ran his example project. In the textarea, I pasted the snippet from my database manager " 's ", an apostrophe s. Included the s because I wasn’t sure which value I was actually looking for. The s showed me the decimal value is 115, and the crazy apostrophe is 8217

I then placed the following code in the KeyUp of the txtSearch, and now when the regular apostrophe is typed in, it will be changed to the special one:
If Key = Chr(39) Then 'apostrophe change to different apostrophe in textarea
me.Value = me.Value.ReplaceAll(chr(39), chr(8217))
End If

Think I am good for now. Thanks everyone for chiming in! I will be playing around with the handling of other characters, such as an extra % if looking for 50% and not just 50

Norman_Palardy · July 30, 2020, 5:45pm

This really isnt an encoding issue in that way

There is code laying around the forums some where that can programmatically disable the automatic substitution of smart quotes and dashes

On macOS this setting is in Preferences > KeyBoard Text

But since you cant be sure that any user will / wont have this on or off controlling it programatically can be useful

Thom_McGrath · July 30, 2020, 6:18pm

Wouldn’t full text search be better suited to this task?