An MD5 Hash with a single quote = grief in SQL query
 

I know when you need to create a query string and the data contains a single
quote, you must double the quote as an escape sequence.

Maybe I am blundering, but I cannot figure out why this DAO recordset
FindFirst is returning a noMatch.

The situation. I am using a wwGetMD5Hash2 function See:
http://excelsig.org/VBA/wwHash.htm#wwGetMD5Hash2
I use this to track whole sets of assumptions in my portfolio models.
Hundreds of assumption cells combined into one 16 character Hash. That
Hash I write to a database table called H70. The HASH field is a No
Duplicates index. Each record has an IDHash, a Long integer, random
autonumber.

When I run the model, I check the Hash result against the H70 table in the
database. If the Hash cannot be found, I create a new record. If it can
be found, I retrieve the IDHash long integer value.

It works 95% of the time. But if the Hash string contains a single quote,
my DAO rsH70.FindFirst fails to find the Hash string.

Here is an example. The 14th character in the hash in a row of the H70
table contains a single quote.
Field:HASH in Table H70 contains
~8æQzzûù¦ÿ5'?;

The Hash Value in Spreadsheet read into variable strHash
~8æQzzûù¦ÿ5'?;

I have a small function to replace a single quote with two single quotes.
Dim sql as string
sql is set to the value
sql = "HASH = '" & wwQuoteFix(strHash) & "'"

Function wwQuoteFix(str1 As String) As String
wwQuoteFix = Replace(str1, "'", "''")
End Function


In the H70Check function below, the value of sql in the find first statement
is:
HASH = '~8æQzzûù¦ÿ5''?;'
so I think I have correctly doubled the single quote within the string.

yet the DAO recordset.NoMatch is true

Function H70Check(strHash As String) As Long
'return the IDHashProspSched if the strHash is found in
H70HashProspSched table
'return 0 if not found. Protect against 0 being a legitimate ID.
Dim sql As String
sql = "HASH = '" & wwQuoteFix(strHash) & "'" 'Rasey 040804 v24j
With rsH70ProspSched 'a Public DAO.Recordset
.FindFirst sql
If .NoMatch Then
H70Check = 0
Else
H70Check = !IDHashProspSched
End If
End With
End Function

Maybe the non-printing characters are part of the problem. Other Hashes
with non-printing characters but without single quotes work fine.

I am prepared to be very embarrased at some dumb mistake I made. I'll
accept that. I am out of ideas.
I hope someone sees the problem.

Stephen Rasey
WiserWays, LLC
Houston
http://excelsig.org

An MD5 Hash with a single quote = grief in SQL query
 

(cross posted on microsoft.public.access.modulesdaovba)

I am still dumbfounded, but happier.

I tried using an ADO recordset to do the check the Hash. It worked.
One of the possibilities is there is a bug in the DAO 3.6 library.

I am using libraries VBA, Excel 11.0, Office 11.0, Forms 2.0, Access 11.0,
DAO 3.6, ADO Ext. 2.7,
and ActiveX Data Objects 2.7 defined in that order.

I am willing to close the thread, but I'll be glad to discuss anything I
might have done wrong .
Stephen Rasey
WiserWays, LLC
Houston
http://excelsig.org

Sub TestH70Ado()
H02_Define_H_Recordsets
Dim strH As String
Dim ID As Long

ID = H70Check("$¥¨3¶¯ô¼.Ë©£F³Ñ") 'Answer is -1915054114 works
ID = H70CheckADO("$¥¨3¶¯ô¼.Ë©£F³Ñ") 'Answer is -1915054114 works
ID = H70Check("-s3ë=*'[+@û;ÒëN") 'Answer is supposed to be
1106461086, returns 0
ID = H70CheckADO("-s3ë=*'[+@û;ÒëN") 'Answer is 1106461086 works.
End Sub

Sub H02_Define_H_Recordsets
Set adoConn1 = Nothing
Set adorsH70 = Nothing
Set adoConn1 = New ADODB.Connection
Set adorsH70 = New ADODB.Recordset
adoConn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & gstrDBFullPath

adorsH70.CursorLocation = adUseServer
adorsH70.Open "select * from H70HashProspSched", adoConn1,
adOpenDynamic, adLockReadOnly
end sub


Function H70CheckADO(strHash As String) As Long
'return the IDHashProspSched if the strHash is found in
H70HashProspSched
'return 0 if not found. Protect against 0 being a legitimate ID.
Dim sql As String
sql = "HASH = '" & wwQuoteFix(strHash) & "'" 'Rasey 040804 v24j
With adorsH70
.MoveFirst 'With ADO, to do a find first, you must do a
'MoveFirst, then Find.
.Find sql
If .EOF Then
H70CheckADO = 0
Else
H70CheckADO = !IDHashProspSched
End If
End With
End Function

Function H70Check(strHash As String) As Long
'return the IDHashProspSched if the strHash is found in
H70HashProspSched
'return 0 if not found. Protect against 0 being a legitimate ID.
'Bug of unknown cause: if strHash has a single quote, it fails to find
the record.
Dim sql As String
sql = "HASH = '" & wwQuoteFix(strHash) & "'" 'Rasey 040804 v24j
'sql = "HASH = '" & strHash & "'" 'Rasey 040804 v24j
With rsH70ProspSched
.MoveFirst 'Tried a .MoveFirst to see if that
would fix the DAO problem. It didn't.
.FindFirst sql
If .NoMatch Then
H70Check = 0
Else
H70Check = !IDHashProspSched
End If
End With

End Function

Function wwQuoteFix(str1 As String) As String
wwQuoteFix = Replace(str1, "'", "''")
End Function