By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

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

BHo15
100+
P: 142
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֗1fuD "), 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...

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtPW_AfterUpdate()
  2.     Dim strUser As String
  3.         strUser = Me.txtUser
  4.     Dim intAccLev As Integer
  5.  
  6.     If Me.txtPW = "" Or Me.txtUser = "" Then 'Checks for an entered username and password
  7.         MsgBox ("You must enter a username and password"), vbOKOnly, "Incomplete"
  8.         Exit Sub
  9.     End If
  10.  
  11.     Call PRNG
  12.     Call StrToHashToStr(Me.txtPW)
  13.  
  14.     If MsgBox("Will this user perform admin functions on DB?", vbYesNo, "Admin?") = vbYes Then 'User is an Admin for DB
  15.         intAccLev = 2
  16.     Else
  17.         intAccLev = 1
  18.     End If
  19.  
  20.     DoCmd.SetWarnings False
  21.     DoCmd.RunSQL "INSERT INTO tblUsers (UserName, PW, Na, AccLev, Active, Prelim ) " & _
  22.                     "SELECT '" & strUser & "', '" & strPWH & "', '" & strNa & "', " & intAccLev & ", " & _
  23.                     True & ", " & True & " FROM tblUsers"  '1st True is Active/Inactive, and 2nd True is Prelim
  24.  
  25.     DoCmd.SetWarnings True
  26. End Sub
Any thoughts?

Thanks!
Jul 31 '14 #1
Share this Question
Share on Google+
33 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,282
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?
Jul 31 '14 #2

BHo15
100+
P: 142
  • 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֗1fuD ") into the SQL statement (instead of strPWH), it works fine.

Does that help?
Jul 31 '14 #3

Rabbit
Expert Mod 10K+
P: 12,365
Does it fail on all hashes? Or just some hashes?
Jul 31 '14 #4

zmbd
Expert Mod 5K+
P: 5,397
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.RunSQL "INSERT INTO tblUsers (UserName, PW, Na, AccLev, Active, Prelim ) " & _ 
  2.                     "SELECT '" & strUser & "', '" & strPWH & "', '" & strNa & "', " & intAccLev & ", " & _ 
  3.                     True & ", " & True & " FROM tblUsers"  '1st True is Active/Inactive, and 2nd True is Prelim 
  4.  
  5.  
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...

Expand|Select|Wrap|Line Numbers
  1. '(... air code ... )
  2. Dim zStrSQL as String
  3. '
  4. '(...more code... ie error trapping)
  5. '
  6. ZStrSQL = "INSERT INTO tblUsers " & _
  7.    "(UserName, PW, Na, AccLev, Active, Prelim ) " & _
  8.    "SELECT '" & strUser & "', '" & _
  9.       strPWH & "', '" & strNa & "', " & _
  10.       intAccLev & ", " & _
  11.       True & ", " & True & _
  12.       " FROM tblUsers"
  13. '1st True is Active/Inactive, and 2nd True is Prelim 
  14. '
  15. 'Now insert a debug.print here and a <ctrl><g>
  16. 'and now you can see how your string is resolving.
  17. debug.print zStrSQL
  18. '
  19. DoCmd.RunSQL zStrSQL
  20. '
  21. (...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.
Jul 31 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,282
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.......
Jul 31 '14 #6

BHo15
100+
P: 142
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...

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblUsers (UserName, PW, Na, AccLev, Active, Prelim ) 
  2. SELECT 'brtest', '-7 ?ڷa{dqm ', 
  3. '^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).
Jul 31 '14 #7

zmbd
Expert Mod 5K+
P: 5,397
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
Jul 31 '14 #8

zmbd
Expert Mod 5K+
P: 5,397
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...
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblUsers 
  2.    (UserName
  3.       , PW
  4.       , Na
  5.       , AccLev
  6.       , Active
  7.       , Prelim )  
  8. VALUES
  9.    ('brtest'
  10.       , '-•7™ž Œ—?‹ڷa{dqmŸ '
  11.       , '^bE!4q#xRf\!/hk97yQq2UpN5$U8I6'
  12.       , 1
  13.       , True
  14.       , True)

Sorry, I use recordsets so much I forget about the INSERT syntax.
Jul 31 '14 #9

Rabbit
Expert Mod 10K+
P: 12,365
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.
Jul 31 '14 #10

BHo15
100+
P: 142
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...
Expand|Select|Wrap|Line Numbers
  1. Function SHA(ByVal sMessage)
  2.     Dim i, result(32), temp(8) As Double, fraccubeprimes, hashValues
  3.     Dim done512, index512, words(64) As Double, index32, mask(4)
  4.     Dim s0, s1, t1, t2, maj, ch, strLen
  5.  
  6.     mask(0) = 4294967296#
  7.     mask(1) = 16777216
  8.     mask(2) = 65536
  9.     mask(3) = 256
  10.  
  11.     hashValues = Array( _
  12.         1779033703, 3144134277#, 1013904242, 2773480762#, _
  13.         1359893119, 2600822924#, 528734635, 1541459225)
  14.  
  15.     fraccubeprimes = Array( _
  16.         1116352408, 1899447441, 3049323471#, 3921009573#, 961987163, 1508970993, 2453635748#, 2870763221#, _
  17.         3624381080#, 310598401, 607225278, 1426881987, 1925078388, 2162078206#, 2614888103#, 3248222580#, _
  18.         3835390401#, 4022224774#, 264347078, 604807628, 770255983, 1249150122, 1555081692, 1996064986, _
  19.         2554220882#, 2821834349#, 2952996808#, 3210313671#, 3336571891#, 3584528711#, 113926993, 338241895, _
  20.         666307205, 773529912, 1294757372, 1396182291, 1695183700, 1986661051, 2177026350#, 2456956037#, _
  21.         2730485921#, 2820302411#, 3259730800#, 3345764771#, 3516065817#, 3600352804#, 4094571909#, 275423344, _
  22.         430227734, 506948616, 659060556, 883997877, 958139571, 1322822218, 1537002063, 1747873779, _
  23.         1955562222, 2024104815, 2227730452#, 2361852424#, 2428436474#, 2756734187#, 3204031479#, 3329325298#)
  24.  
  25.     sMessage = Nz(sMessage, "")
  26.     strLen = Len(sMessage) * 8
  27.     sMessage = sMessage & Chr(128)
  28.     done512 = False
  29.     index512 = 0
  30.  
  31.     If (Len(sMessage) Mod 64) < 60 Then
  32.         sMessage = sMessage & String(60 - (Len(sMessage) Mod 64), Chr(0))
  33.     ElseIf (Len(sMessage) Mod 64) > 60 Then
  34.         sMessage = sMessage & String(124 - (Len(sMessage) Mod 64), Chr(0))
  35.     End If
  36.  
  37.     sMessage = sMessage & Chr(Int((strLen / mask(0) - Int(strLen / mask(0))) * 256))
  38.     sMessage = sMessage & Chr(Int((strLen / mask(1) - Int(strLen / mask(1))) * 256))
  39.     sMessage = sMessage & Chr(Int((strLen / mask(2) - Int(strLen / mask(2))) * 256))
  40.     sMessage = sMessage & Chr(Int((strLen / mask(3) - Int(strLen / mask(3))) * 256))
  41.  
  42.     Do Until done512
  43.         For i = 0 To 15
  44.             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))
  45.         Next
  46.  
  47.         For i = 16 To 63
  48.             s0 = largeXor(largeXor(rightRotate(words(i - 15), 7, 32), rightRotate(words(i - 15), 18, 32), 32), Int(words(i - 15) / 8), 32)
  49.             s1 = largeXor(largeXor(rightRotate(words(i - 2), 17, 32), rightRotate(words(i - 2), 19, 32), 32), Int(words(i - 2) / 1024), 32)
  50.             words(i) = Mod32Bit(words(i - 16) + s0 + words(i - 7) + s1)
  51.         Next
  52.  
  53.         For i = 0 To 7
  54.             temp(i) = hashValues(i)
  55.         Next
  56.  
  57.         For i = 0 To 63
  58.             s0 = largeXor(largeXor(rightRotate(temp(0), 2, 32), rightRotate(temp(0), 13, 32), 32), rightRotate(temp(0), 22, 32), 32)
  59.             maj = largeXor(largeXor(largeAnd(temp(0), temp(1), 32), largeAnd(temp(0), temp(2), 32), 32), largeAnd(temp(1), temp(2), 32), 32)
  60.             t2 = Mod32Bit(s0 + maj)
  61.             s1 = largeXor(largeXor(rightRotate(temp(4), 6, 32), rightRotate(temp(4), 11, 32), 32), rightRotate(temp(4), 25, 32), 32)
  62.             ch = largeXor(largeAnd(temp(4), temp(5), 32), largeAnd(largeNot(temp(4), 32), temp(6), 32), 32)
  63.             t1 = Mod32Bit(temp(7) + s1 + ch + fraccubeprimes(i) + words(i))
  64.  
  65.             temp(7) = temp(6)
  66.             temp(6) = temp(5)
  67.             temp(5) = temp(4)
  68.             temp(4) = Mod32Bit(temp(3) + t1)
  69.             temp(3) = temp(2)
  70.             temp(2) = temp(1)
  71.             temp(1) = temp(0)
  72.             temp(0) = Mod32Bit(t1 + t2)
  73.         Next
  74.  
  75.         For i = 0 To 7
  76.             hashValues(i) = Mod32Bit(hashValues(i) + temp(i))
  77.         Next
  78.  
  79.         If (index512 + 1) * 64 >= Len(sMessage) Then done512 = True
  80.         index512 = index512 + 1
  81.     Loop
  82.  
  83.     For i = 0 To 31
  84.         result(i) = Int((hashValues(i \ 4) / mask(i Mod 4) - Int(hashValues(i \ 4) / mask(i Mod 4))) * 256)
  85.     Next
  86.  
  87.     SHA = result
  88. End Function

I am calling it with this...
Expand|Select|Wrap|Line Numbers
  1. Function StrToHashToStr(PW As String)
  2.     Dim arrHash As Variant
  3.     Dim x As Integer
  4.     Dim strArr As String
  5.     arrHash = SHA(PW & strNa)
  6.     For x = LBound(arrHash) To UBound(arrHash)
  7.         strArr = strArr + CStr(Chr(arrHash(x)))
  8.     Next x
  9.     strPWH = strArr
  10. End Function
...with the call to hash being sent both the password and the salt string.
Aug 1 '14 #11

BHo15
100+
P: 142
Okay... I tried the changes to the SQL statement (and yes... that makes all the sense in the world). Here is my new SQL...

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO tblUsers (UserName, PW, Na, AccLev, Active, Prelim ) " & _
  2.                     "VALUES ('" & strUser & "', '" & strPWH & "', '" & strNa & "', " & intAccLev & ", " & _
  3.                     True & ", " & True & ")"

But alas... Same error. :(
Aug 1 '14 #12

BHo15
100+
P: 142
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?!?!?!
Aug 1 '14 #13

BHo15
100+
P: 142
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.
Aug 1 '14 #14

zmbd
Expert Mod 5K+
P: 5,397
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)
Aug 1 '14 #15

BHo15
100+
P: 142
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.
Aug 1 '14 #16

Rabbit
Expert Mod 10K+
P: 12,365
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.
Aug 1 '14 #17

BHo15
100+
P: 142
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.
Aug 1 '14 #18

NeoPa
Expert Mod 15k+
P: 31,487
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function StringDisp(strVar As String) As String
  2.     Dim lngX As Long
  3.  
  4.     With Debug
  5.         .Print Len(strVar),
  6.         For lngX = 1 To Len(strVar)
  7.             .Print Hex(Asc(Mid(strVar, lngX, 1)));
  8.         Next lngX
  9.     End With
  10. End Function
Aug 3 '14 #19

BHo15
100+
P: 142
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...

Expand|Select|Wrap|Line Numbers
  1.  33           EFB6794A89EFE2E73B3925E42762CECE517AAFCFE86BCC5C3CFE7BE0FD750
  2.  32           EFB6794A89EFE2E73B3925E42762CECE517AAFCFE86BCC5C3CFE7BE0FD75
  3.  32           37CA2EDC31DD42E0585CE7BACA4B17356762836A1DA5A368558D85C6CB6E6

Any thoughts?
Aug 4 '14 #20

Rabbit
Expert Mod 10K+
P: 12,365
What's that third one? It's completely different.
Aug 4 '14 #21

BHo15
100+
P: 142
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.
Aug 4 '14 #22

BHo15
100+
P: 142
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!
Aug 4 '14 #23

NeoPa
Expert Mod 15k+
P: 31,487
Sorry. My code was flawed :-( Try replacing the .Print line with :
Expand|Select|Wrap|Line Numbers
  1.             .Print IIf(Mid(strVar, lngX, 1) > Chr(15),' ','  ');
  2.             .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.
Aug 6 '14 #24

Rabbit
Expert Mod 10K+
P: 12,365
Try inserting a chr(0) and see if you get the error.
Aug 6 '14 #25

BHo15
100+
P: 142
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*žQ6t…

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*žQ6t…

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?
Aug 6 '14 #26

NeoPa
Expert Mod 15k+
P: 31,487
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.
Aug 7 '14 #27

NeoPa
Expert Mod 15k+
P: 31,487
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.
Aug 7 '14 #28

Rabbit
Expert Mod 10K+
P: 12,365
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.
Aug 7 '14 #29

BHo15
100+
P: 142
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?
Aug 8 '14 #30

NeoPa
Expert Mod 15k+
P: 31,487
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.
Aug 8 '14 #31

BHo15
100+
P: 142
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"
Aug 8 '14 #32

BHo15
100+
P: 142
Sorry my response has taken so stinkin' long. Life happened.

I tried this...

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "UPDATE tblUsers Set tblUsers.PW= '" & _
  2. Chr(0) & "' WHERE tblUsers.Username= '" & _
  3. "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?
Aug 13 '14 #33

Rabbit
Expert Mod 10K+
P: 12,365
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.

Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 13 '14 #34

Post your reply

Sign in to post your reply or Sign up for a free account.