Run-time error '3075': Syntax error in string... Hash String

[BHo15]

Hello All.

I am getting my error on an Insert SQL statement in VBA. It is bombing on the value for strPWH. I can see that the value for strPWH is valid (e.g. "a%¬ßÙÒ×wx^¨´m¤"ès•‚›ÏÖ—à1¤fuÄÏD "), and if I just use Debug.Print for the string, and then put that in instead of the strPWH, it works fine.

Here is my sub...

Private Sub txtPW_AfterUpdate()
    Dim strUser As String
        strUser = Me.txtUser
    Dim intAccLev As Integer
 
    If Me.txtPW = "" Or Me.txtUser = "" Then 'Checks for an entered username and password
        MsgBox ("You must enter a username and password"), vbOKOnly, "Incomplete"
        Exit Sub
    End If
 
    Call PRNG
    Call StrToHashToStr(Me.txtPW)
 
    If MsgBox("Will this user perform admin functions on DB?", vbYesNo, "Admin?") = vbYes Then 'User is an Admin for DB
        intAccLev = 2
    Else
        intAccLev = 1
    End If
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblUsers (UserName, PW, Na, AccLev, Active, Prelim ) " & _
                    "SELECT '" & strUser & "', '" & strPWH & "', '" & strNa & "', " & intAccLev & ", " & _
                    True & ", " & True & " FROM tblUsers"  '1st True is Active/Inactive, and 2nd True is Prelim
 
    DoCmd.SetWarnings True
End Sub

Any thoughts?

Thanks!

[twinnyfo]

Where are your variables: strUser, strPWH, and strNa coming from?

Also, is it possible for you to build your string first, then debug.print it, so we can see the final string befor it executes?

[BHo15]

• strUser comes from the form (Me.txtUser)
• strPWH is a global variable that comes from the Hash function
• strNa is also a global and comes from the pseudo random number generator

The random string I put in the post was a sample of one of the strPWH strings (from a debug.print). Again, if I put the value of the string (e.g. "a%¬ßÙÒ×wx^¨´m¤"ès•‚›ÏÖ—à1¤fuÄÏD ") into the SQL statement (instead of strPWH), it works fine.

Does that help?

[Rabbit]

Does it fail on all hashes? Or just some hashes?

[zmbd]

 DoCmd.RunSQL "INSERT INTO tblUsers (UserName, PW, Na, AccLev, Active, Prelim ) " & _ 
                    "SELECT '" & strUser & "', '" & strPWH & "', '" & strNa & "', " & intAccLev & ", " & _ 
                    True & ", " & True & " FROM tblUsers"  '1st True is Active/Inactive, and 2nd True is Prelim 
 
 

My petpeeve... all of the examples say... do it this way... and show exactly what you have here;
HOWEVER,
this is impossible to troubleshoot!

Instead...

'(... air code ... )
Dim zStrSQL as String
'
'(...more code... ie error trapping)
'
ZStrSQL = "INSERT INTO tblUsers " & _
   "(UserName, PW, Na, AccLev, Active, Prelim ) " & _
   "SELECT '" & strUser & "', '" & _
      strPWH & "', '" & strNa & "', " & _
      intAccLev & ", " & _
      True & ", " & True & _
      " FROM tblUsers"
' 
'1st True is Active/Inactive, and 2nd True is Prelim 
'
'Now insert a debug.print here and a <ctrl><g>
'and now you can see how your string is resolving.
debug.print zStrSQL
'
DoCmd.RunSQL zStrSQL
'
(...more code...)

This is what TwinnyFo was talking about in the first reply.

Additionally, because this is an action query you should consider the database.execute method as you can have this set to fail on error and even setup a transaction so that you can roll the database back should everything not properly execute. I do this when makeing multiple entries or deletions that have dependency upon each other.

[twinnyfo]

Does anyone know if it may have somethings to do with the Unicode characters? However, OP says inserting that value inserts the string just fine.......

[BHo15]

Rabbit... It is happening with each hash I create.

Sorry I didn't understand what you were asking for twinnyfo. Here is what I got on the debug.print of the SQL...

INSERT INTO tblUsers (UserName, PW, Na, AccLev, Active, Prelim ) 
SELECT 'brtest', '-ü�¸ø•Ô7™ž Œªô—?ì‹Ú·a{dqêÎmŸ×üõ ', 
'^bE!4q#xRf\!/hk97yQq2UpN5$U8I6', 1, True, True FROM tblUsers

I will look into the database.execute and transaction thoughts you had zmbd. However, in my limited understanding, the SQL statement looks like it is resolving fairly cleanly (unless I just missing something that is right under my nose).

[zmbd]

sorry didnt catch this sooner... [Na] very close to a reserved word... I don't think this is your issue though. (^_^)
I'm looking at your SQL...
You might look at BASE64 encoding your hash... thus avoiding any non-printables...

-It is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY importaint to avoid all reserved words and tokens:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[zmbd]

duh, (*^_^*)
Don't I feel stupid today.
remove the "SELECT" from the string.
Enclose the new data in parentheses " (data, list, here) "
Remove the "FROM" from the string.
Insert "VALUES"
You're creating a new record, not inserting an entire table...

so that you have...

INSERT INTO tblUsers 
   (UserName
      , PW
      , Na
      , AccLev
      , Active
      , Prelim )  
VALUES
   ('brtest'
      , '-ü�¸ø•Ô7™ž Œªô—?ì‹Ú·a{dqêÎmŸ×üõ '
      , '^bE!4q#xRf\!/hk97yQq2UpN5$U8I6'
      , 1
      , True
      , True)

Sorry, I use recordsets so much I forget about the INSERT syntax.

[Rabbit]

If that's the exact SQL you're inserting, then isn't the hash one character too long because of the extra space at the end? If you're using a 256 bit hash, that's 32 bytes or 32 characters. If you defined the field as 32, then you're attempting to insert 33 characters into that field.

[BHo15]

I need to process zmbd's suggestions, but Rabbit... I was wondering about that trailing space. Why is it there? But... That said... It is 32 characters, even with the trailing space.

Here is the hash I am using...

Function SHA(ByVal sMessage)
    Dim i, result(32), temp(8) As Double, fraccubeprimes, hashValues
    Dim done512, index512, words(64) As Double, index32, mask(4)
    Dim s0, s1, t1, t2, maj, ch, strLen
 
    mask(0) = 4294967296#
    mask(1) = 16777216
    mask(2) = 65536
    mask(3) = 256
 
    hashValues = Array( _
        1779033703, 3144134277#, 1013904242, 2773480762#, _
        1359893119, 2600822924#, 528734635, 1541459225)
 
    fraccubeprimes = Array( _
        1116352408, 1899447441, 3049323471#, 3921009573#, 961987163, 1508970993, 2453635748#, 2870763221#, _
        3624381080#, 310598401, 607225278, 1426881987, 1925078388, 2162078206#, 2614888103#, 3248222580#, _
        3835390401#, 4022224774#, 264347078, 604807628, 770255983, 1249150122, 1555081692, 1996064986, _
        2554220882#, 2821834349#, 2952996808#, 3210313671#, 3336571891#, 3584528711#, 113926993, 338241895, _
        666307205, 773529912, 1294757372, 1396182291, 1695183700, 1986661051, 2177026350#, 2456956037#, _
        2730485921#, 2820302411#, 3259730800#, 3345764771#, 3516065817#, 3600352804#, 4094571909#, 275423344, _
        430227734, 506948616, 659060556, 883997877, 958139571, 1322822218, 1537002063, 1747873779, _
        1955562222, 2024104815, 2227730452#, 2361852424#, 2428436474#, 2756734187#, 3204031479#, 3329325298#)
 
    sMessage = Nz(sMessage, "")
    strLen = Len(sMessage) * 8
    sMessage = sMessage & Chr(128)
    done512 = False
    index512 = 0
 
    If (Len(sMessage) Mod 64) < 60 Then
        sMessage = sMessage & String(60 - (Len(sMessage) Mod 64), Chr(0))
    ElseIf (Len(sMessage) Mod 64) > 60 Then
        sMessage = sMessage & String(124 - (Len(sMessage) Mod 64), Chr(0))
    End If
 
    sMessage = sMessage & Chr(Int((strLen / mask(0) - Int(strLen / mask(0))) * 256))
    sMessage = sMessage & Chr(Int((strLen / mask(1) - Int(strLen / mask(1))) * 256))
    sMessage = sMessage & Chr(Int((strLen / mask(2) - Int(strLen / mask(2))) * 256))
    sMessage = sMessage & Chr(Int((strLen / mask(3) - Int(strLen / mask(3))) * 256))
 
    Do Until done512
        For i = 0 To 15
            words(i) = Asc(Mid(sMessage, index512 * 64 + i * 4 + 1, 1)) * mask(1) + Asc(Mid(sMessage, index512 * 64 + i * 4 + 2, 1)) * mask(2) + Asc(Mid(sMessage, index512 * 64 + i * 4 + 3, 1)) * mask(3) + Asc(Mid(sMessage, index512 * 64 + i * 4 + 4, 1))
        Next
 
        For i = 16 To 63
            s0 = largeXor(largeXor(rightRotate(words(i - 15), 7, 32), rightRotate(words(i - 15), 18, 32), 32), Int(words(i - 15) / 8), 32)
            s1 = largeXor(largeXor(rightRotate(words(i - 2), 17, 32), rightRotate(words(i - 2), 19, 32), 32), Int(words(i - 2) / 1024), 32)
            words(i) = Mod32Bit(words(i - 16) + s0 + words(i - 7) + s1)
        Next
 
        For i = 0 To 7
            temp(i) = hashValues(i)
        Next
 
        For i = 0 To 63
            s0 = largeXor(largeXor(rightRotate(temp(0), 2, 32), rightRotate(temp(0), 13, 32), 32), rightRotate(temp(0), 22, 32), 32)
            maj = largeXor(largeXor(largeAnd(temp(0), temp(1), 32), largeAnd(temp(0), temp(2), 32), 32), largeAnd(temp(1), temp(2), 32), 32)
            t2 = Mod32Bit(s0 + maj)
            s1 = largeXor(largeXor(rightRotate(temp(4), 6, 32), rightRotate(temp(4), 11, 32), 32), rightRotate(temp(4), 25, 32), 32)
            ch = largeXor(largeAnd(temp(4), temp(5), 32), largeAnd(largeNot(temp(4), 32), temp(6), 32), 32)
            t1 = Mod32Bit(temp(7) + s1 + ch + fraccubeprimes(i) + words(i))
 
            temp(7) = temp(6)
            temp(6) = temp(5)
            temp(5) = temp(4)
            temp(4) = Mod32Bit(temp(3) + t1)
            temp(3) = temp(2)
            temp(2) = temp(1)
            temp(1) = temp(0)
            temp(0) = Mod32Bit(t1 + t2)
        Next
 
        For i = 0 To 7
            hashValues(i) = Mod32Bit(hashValues(i) + temp(i))
        Next
 
        If (index512 + 1) * 64 >= Len(sMessage) Then done512 = True
        index512 = index512 + 1
    Loop
 
    For i = 0 To 31
        result(i) = Int((hashValues(i \ 4) / mask(i Mod 4) - Int(hashValues(i \ 4) / mask(i Mod 4))) * 256)
    Next
 
    SHA = result
End Function

I am calling it with this...

Function StrToHashToStr(PW As String)
    Dim arrHash As Variant
    Dim x As Integer
    Dim strArr As String
    arrHash = SHA(PW & strNa)
    For x = LBound(arrHash) To UBound(arrHash)
        strArr = strArr + CStr(Chr(arrHash(x)))
    Next x
    strPWH = strArr
End Function

...with the call to hash being sent both the password and the salt string.

[BHo15]

Okay... I tried the changes to the SQL statement (and yes... that makes all the sense in the world). Here is my new SQL...

strSQL = "INSERT INTO tblUsers (UserName, PW, Na, AccLev, Active, Prelim ) " & _
                    "VALUES ('" & strUser & "', '" & strPWH & "', '" & strNa & "', " & intAccLev & ", " & _
                    True & ", " & True & ")"

But alas... Same error. :(

[BHo15]

A little more fun and exciting info. I just added a local variable to the Sub (strP), and gave it the value of strP = InputBox("Paste in strPWH"). I then debug.printed the strPWH value (DEFINITELY 33 characters this time), and copied it. I then pasted it into the input box, and finished the code. It worked fine, and all 33 characters ended up in the table.

HUH?!?!?!

[BHo15]

I've now tried totally going the long way around, and tried setting my local variable as follows... strP=Left(strPWH,32). When I do that... It works!

However, I can't use this as a solution, because it doesn't work on comparing the hashes.

I don't understand why I'm getting 33 characters on a 256 bit hash function.

[zmbd]

the new string.... what does the debug.print resolve it to...
and did you check the field length in the table per Rabbit? (or did I miss that... not using my glasses right now 1/2 asleep)

[BHo15]

Will have to try the debug.print at lunch time. But the field length of the table is sufficient b/c it's the default 'short text' field length at 255.

[Rabbit]

That's weird. If it's able to hold it, then using the left 32 shouldn't have fixed the issue. Anyways, there shouldn't be a problem comparing the hash if you truncate it to 32 bytes each time. I don't see in your code where that final 33rd byte is being added.

[BHo15]

Turns out to be a day (trying to leave town and all). Will not be able to look at it until Sunday evening.

That is good to know that truncating each time SHOULD work. I thought the same thing, but it wasn't, but now that I know that you also think it should, then I'll play with it more.

Thank you all for your input, and if either of you have another revelation... please chime in.

[NeoPa]

I would suggest determining exactly what values are used when it succeeds and when it fails. Knock up a function to show the Hex values of each character and exactly how long the string is. When you have that try the various approaches and see what is different between it working and failing.

Public Function StringDisp(strVar As String) As String
    Dim lngX As Long
 
    With Debug
        .Print Len(strVar),
        For lngX = 1 To Len(strVar)
            .Print Hex(Asc(Mid(strVar, lngX, 1)));
        Next lngX
    End With
End Function

[BHo15]

Good thoughts NeoPa. Here's what I've seen so far.

I ran it 3 times (once with the full hash (didn't work), once with the the truncated hash (worked), and once with 33 regular characters (worked). Here is what they all printed...

 33           EFB6794A89EFE2E73B3925E42762CECE517AAFCFE86BCC5C3CFE7BE0FD750
 32           EFB6794A89EFE2E73B3925E42762CECE517AAFCFE86BCC5C3CFE7BE0FD75
 32           37CA2EDC31DD42E0585CE7BACA4B17356762836A1DA5A368558D85C6CB6E6

Any thoughts?

[Rabbit]

What's that third one? It's completely different.

[BHo15]

The 3rd one was just an attempt to pass 33 non cryptic characters through the SQL Insert statement. So it was just a phrase with 33 letters in it.

[BHo15]

I'm still very curious if someone figures out why the problems, but... I've decided to go with truncating the stringed hash. I got the comparisons of two truncated hashes working (it was my error causing it not to work in the first place).

Thank you all for your input!

[NeoPa]

Sorry. My code was flawed :-( Try replacing the .Print line with :

            .Print IIf(Mid(strVar, lngX, 1) > Chr(15),' ','  ');
            .Print Hex(Asc(Mid(strVar, lngX, 1)));

The existing code merges all characters in together - which is fine if they're all two long displayed as Hex. Unfortunately, this isn't guaranteed when dealing with random strings.

I'd be interested to see the results when run against this code if you can.

[Rabbit]

Try inserting a chr(0) and see if you get the error.

[BHo15]

NeoPa... Here is what I got with the changes you suggested. The first run is for the full hash (Hash string printed first), and the second run is for the truncated hash (also with Hash string printed first).

Full Hash
šÌé'´Ô!ÛÞ¥ Ó‘¾Z. ·$a*®žQÛø¤6Útê…

33 9A CC E9 27 B4 D4 21 DB DE A5 20 D3 91 BE 5A 2E 9 B7 24 61 A0 AE 9E 51 DB F8 A4 36 DA 74 EA 85 0

----------

Truncated Hash
šÌé'´Ô!ÛÞ¥ Ó‘¾Z. ·$a*®žQÛø¤6Útê…

35 9A CC E9 27 B4 D4 21 DB DE A5 20 D3 91 BE 5A 2E 20 20 20 20 B7 24 61 A0 AE 9E 51 DB F8 A4 36 DA 74 EA 85

See anything of interest?


Rabbit... Not sure what you mean by insert chr(0). Where were you thinking?

[NeoPa]

It seems you missed off the last semi-colon :-D See the mess that made. No worries - It included all the relevant data and I've formatted it to look as it would have with the code as posted :-)

NB. The first value is the string length so I should have added a separator into the code to help distinguish that from the string data.

[NeoPa]

BHo15:
See anything of interest?

Two things :

1. This doesn't seem to be the same data as was used before. This means I'm not sure which of them worked.
2. When you created the second string it appears you used an editor that converted a single TAB character to a number of spaces. If you look at your updated post you'll see what I'm talking about.

Sorry I can't make any comments at this stage on the viability of the strings but I don't believe we quite have what we're after with the data just yet.

[Rabbit]

The only major difference I see is that you have a ASCII value of 0 as the last character in the 33 byte version. I was suggesting that you try inserting just that ASCII value of 0 to see if that is what's causing the error.

[BHo15]

NeoPa: Sorry about the semi-colon. I purposely took it out, b/c I was getting an error with it in there. I didn't troubleshoot the error, I just noticed it there, and it seemed like something that would have come from a Query, so I took it out. My bad.

Also... Sorry it was a different string. I didn't remember what string I used in the first go-round. I actually did try to post the two runs side by side for comparison sake, but that obviously didn't work. I was wishing there was a way to post a table so that I could have put the full hash in column A and the truncated hash in column B.

Rabbit: I did indeed see the 0 at the end, and wondered about that. How would I go about inserting just the ASCII 0?

[NeoPa]

Apologies not necessary. It was more amusing than a problem :-) Dealing with you is proving easier than most members - especially new ones. I doubt you'd even have posted had the problem not been particularly complicated.

Adding a 0 into a string can be done one of two ways :

1. vbNullChar is a predefined constant.
2. Chr(0) returns the same value in either VBA or SQL.

NB though.
The latest example you posted of data included a string where the original TAB character (==>Chr(9)) had been converted as part of the print process into multiple spaces. These two strings will never work the same. You will need to find a way to duplicate the input value more accurately when it comes to non-printable characters.

[BHo15]

Sounds good. I would like to play with it this evening, but unfortunately, the DB is sitting on a jump drive on my desk at work. :(

To quote a Southern Bell from film... "Tomorrow is another day"

[BHo15]

Sorry my response has taken so stinkin' long. Life happened.

I tried this...

DoCmd.RunSQL "UPDATE tblUsers Set tblUsers.PW= '" & _
Chr(0) & "' WHERE tblUsers.Username= '" & _
"TestUser" & "'"

...and got the same error. So, indeed, it appears to be the 0 at the end that is nutting things up.

So my truncating is of course solving (or at least masking) the problem. But why oh why the 0 at the end?

[Rabbit]

I don't know the underlying reason, I have some vague guesses but no reason to believe my guesses are accurate so I won't air them here. What you can do is use a replace function to replace the actual value with a function call like the example below.

DoCmd.RunSQL "INSERT INTO t2 VALUES ('" & Replace(t, Chr(0), "' & Chr(0) & '") & "')"

I still don't know why you even have it at the end but the replace function should prevent errors if the hash produces any valid Chr(0) characters.

Another solution is to store the hex representation using a variation of NeoPa's function.