Escaping CHR(39) in Query strings

[ironfelix717]

Unsurprised, yet again, that escaping quote characters in strings is profoundly difficult.

I am trying to escape the glorious apostrophe, aka chr(39).

Usage:

Sub test()
Dim Criteria            As String
Dim SQL                 As String

Criteria = "Mc'Hammer"
SQL = "SELECT * FROM POINVrec_Line WHERE MaterialID = '" & Criteria & "';"

Debug.Print SQL
DoCmd.RunSQL SQL  'FAIL......

End Sub

I've made several attempts after reading various threads. None have been successful, as the result is the unlikeable "Syntax error in query expression".

Many have said 'doubling up' the chr(39) is the way to escape.

If InStr(1, Criteria, Chr(39)) > 0 Then
    Criteria = Replace(Criteria, Chr(39), Chr(39) & Chr(39))
End If

False. Does not work. At least with JET/ACE.


Whats the trick?
Thanks in advance.

 

[conception_native_0123]

the error is here:

MaterialID = '" & Criteria & "';"

you're trying to escape it but your code is adding to it:

Criteria = Replace(Criteria, Chr(39), Chr(39) & Chr(39))

does this work?

Criteria = Replace(Criteria, "'", "''")

 

[MarkK]

The best way to deal with delimiters in your data like that is to use a parameter query and a temp QueryDef, like...

Function GetRSTMcHammer() As DAO.Recordset
    Const SQL As String = _
        "SELECT * FROM YourTable WHERE MaterialID = p0 "
        
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = "Mc'Hammer"
        Set GetRSTMcHammer = .OpenRecordset
    End With
End Function

 

[conception_native_0123]

have never done that before Mark. kudos to you if it works!

 

[arnelgp]

you can also replace it, without using Querydef:

Sub test()
Dim Criteria            As String
Dim SQL                 As String

Criteria = "Mc'Hammer"
SQL = "SELECT * FROM POINVrec_Line WHERE MaterialID = '" & Replace$(Criteria,"'", "''") & "';"

Debug.Print SQL
DoCmd.RunSQL SQL  'FAIL......

End Sub

 

[ironfelix717]

The best way to deal with delimiters in your data like that is to use a parameter query and a temp QueryDef, like...

Function GetRSTMcHammer() As DAO.Recordset
    Const SQL As String = _
        "SELECT * FROM YourTable WHERE MaterialID = p0 "
      
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = "Mc'Hammer"
        Set GetRSTMcHammer = .OpenRecordset
    End With
End Function

Thanks for showing a simple example of this. I haven't got into using parameters yet, which from what I understand is the 'proper' way of handling criteria, especially for preventing SQL injection.

However, I need a string-based solution, as that solution has way too much overhead.

you can also replace it, without using Querydef:

Sub test()
Dim Criteria            As String
Dim SQL                 As String

Criteria = "Mc'Hammer"
SQL = "SELECT * FROM POINVrec_Line WHERE MaterialID = '" & Replace$(Criteria,"'", "''") & "';"

Debug.Print SQL
DoCmd.RunSQL SQL  'FAIL......

End Sub

This somehow works. Can someone explain how
Replace$(Criteria, "'", "''") is any different than Replace(Criteria, Chr(39), Chr(39) & Chr(39))

 

[arnelgp]

you need to add delimiter to the later:

Replace(Criteria, """" & Chr(39) & """" , """" & Chr(39) & Chr(39) & """")

 

[ironfelix717]

I see whats going on there and its the most un-natural and unintuitive thing I can imagine. Everything I know about strings is now useless.

The following statement should be universally true.
myvar = chr(39) myvar2 = "'" "'" = chr(39) = myvar = myvar2

What we've just demonstrated is that the above statement is not true...

Anything enclosed in chr(34) should be interpreted by VBA as LITERAL!

My point: It should not matter whether I am using "'" versus chr(39). As both those statements should be universally equal by VBA.

 

[moke123]

SQL = "SELECT * FROM POINVrec_Line WHERE MaterialID = '" & Criteria & "';"

Try doubling up the quotes

SQL = "SELECT * FROM POINVrec_Line WHERE MaterialID = """  & Criteria & """;"

Should work regardless of whether there is a special character or not

 

[cheekybuddha]

Should work regardless of whether there is a special character or not

Not if Criteria contains a double quote!

 

[ironfelix717]

SQL = "SELECT * FROM POINVrec_Line WHERE MaterialID = '" & Criteria & "';"

Try doubling up the quotes

SQL = "SELECT * FROM POINVrec_Line WHERE MaterialID = """  & Criteria & """;"

Hi,

The solution has been provided by @arnelgp

However, I now discovered my original attempt was correct and would work, and I wrongly thought it was flawed due to some incorrect code.

Which also renders my last reply regarding the difference between @arnelgp's solution and mine erroneous.

His solution is the same as my original attemp,t which is why I was extremely confused about them being any different (which they aren't).

Here is an example of a final solution when dealing with chr(39) in SQL strings.
Note that this code will ultimately fail at .RunSQL() because it is a SELECT statement, but will pass in the sense that the query syntax is accepted by DAO.

Dim sql As String
Dim criteria As String

criteria = "Mc'Hammer"
criteria = Replace(criteria, Chr(39), Chr(39) & Chr(39))
sql = "SELECT * FROM POINVrec_Line WHERE MaterialID = '" & criteria & "';"

Debug.Print sql
DoCmd.RunSQL sql  'PASS!

Alternatively, @arnelgp solution is also acceptable.

Thanks and sorry for the confusion on this petty issue.
Regards

 

[cheekybuddha]

The solution has been provided by @arnelgp

Also by Adam

 

[ironfelix717]

Not if Criteria contains a double quote!

Double quotes are not an issue for a SQL string, apparently as I've learned.

If by double quote you mean two instances of chr(34) or even one instance of chr(34).

criteria = "hello""world"
SQL = "SELECT * FROM MyTable WHERE ID = '" & criteria & "';"

The above will pass all day.

As well as the following:

criteria = "hello" & Chr(34) & Chr(34) & "world"

Regards

 

[cheekybuddha]

Double quotes are not an issue for a SQL string, apparently as I've learned.

They are if you use a double quote instead of single quote as SQL string delimiter as Moke123 was demonstrating.

The only character you need to escape within an SQL string is the string delimiter. You can use either single quote or double quote - just make sure you escape whichever you choose.

 

[ironfelix717]

The only character you need to escape within an SQL string is the string delimiter.

Not entirely true - See my latest post requesting help on escaping speciality characters in Access SQL.

My most joyous pass-time is figuring out how to escape a 'dot' operator in the string.

 

[moke123]

Not if Criteria contains a double quote!

Can't say I've ever had that problem.

 

[cheekybuddha]

Simple test:

Function SQLDblQuoteTest() As Boolean

  Dim SQL As String, Criteria As String
 
  Criteria = "Hello " & Chr(34) & "Moke123" & Chr(34)
  SQL = "SELECT * FROM tblEvents WHERE Narrative = """ & Criteria & """;"
  Debug.Print SQL
  With CurrentDb.OpenRecordset(SQL)
    Do While Not .EOF
      Debug.Print .Fields("Narrative")
      .MoveNext
    Loop
    .Close
  End With
  SQLDblQuoteTest = Err = 0
 
End Function

Immediate Window:

 

[cheekybuddha]

Not entirely true - See my latest post requesting help on escaping speciality characters in Access SQL.

I did see, but you haven't replied in that thread

 

[ironfelix717]

I did see, but you haven't replied in that thread

Updated with a reply.

I was incorrect again as a result of the original code error.

Special characters (at least the "." character) has no effect on the syntax and works successfully.

Apologies for confusion!

Regards

 

[moke123]

Simple test:

View attachment 95742

Function SQLDblQuoteTest() As Boolean

  Dim SQL As String, Criteria As String

  Criteria = "Hello " & Chr(34) & "Moke123" & Chr(34)
  SQL = "SELECT * FROM tblEvents WHERE Narrative = """ & Criteria & """;"
  Debug.Print SQL
  With CurrentDb.OpenRecordset(SQL)
    Do While Not .EOF
      Debug.Print .Fields("Narrative")
      .MoveNext
    Loop
    .Close
  End With
  SQLDblQuoteTest = Err = 0

End Function

Immediate Window:
View attachment 95743

I wasn't disagreeing, just 99.9% of the time it's a scenario like the OP's original Criteria = "Mc'Hammer", at least for me.
Should have qualified my statement.