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.execut e 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.execut e 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(strPW H,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)