Unwanted Scientific Notation in MS Access

Question

I'm encountering a very strange problem with MS Access. I have some VBA code used on a password reset form. The code hashes the input password and then saves the hash to a table of users. Here's a relevant snippit:

If newPW1 = newPW2 Then
    MsgBox ("Passwords Match!")
    hashPW = Encrypt(newPW1)
    MsgBox ("HashedPW is " & hashPW)
    updatePW = "UPDATE Users SET Password = " & hashPW & " WHERE Username = pwChangeUsrnm"
    DoCmd.RunSQL (updatePW)

the MSGboxes are my debugging notes. I know the hash generates properly as a long string of numbers, all well and good. When I go into the datasheet for the Users table though, the number has always been converted into scientific notation.

Here's a screenshot of the data sheet. bob.smith is an example of what I end up with after the code runs, the other two are gibberish I entered manually. The field is formatted as a string, so I'm not sure why it would even try to convert the number into SN when as far as I can tell the item is always a string.

I'm thinking the error must creep in around the SQL query? If there's a better way of doing this then I'm all ears.

Thanks in advance for your help!

datasheet

datasheet

design view

design view

Complete code, just in case:

Option Compare Database




Private Sub Command84_Click()

Dim hashPW As String

Dim updatePW As String


Dim checkName As String
checkName = Nz(DLookup("Username", "Users", "Username = pwChangeUsrnm"), "aaa")
MsgBox ("checkName set to " & checkName)

If pwChangeUsrnm = checkName Then

    MsgBox ("Username Found")
    If newPW1 = newPW2 Then
        MsgBox ("Passwords Match!")
        hashPW = Encrypt(newPW1)
        MsgBox ("HashedPW is " & hashPW)
        updatePW = "UPDATE Users SET Password = " & hashPW & " WHERE Username = pwChangeUsrnm"
        DoCmd.RunSQL (updatePW)

    Else

    MsgBox ("Passwords Do Not Match!")

    End If

Else

MsgBox ("Username not found")

End If

End Sub

You are probably trying to display the password as a number. — zaph, Nov 05 '18 at 20:53
*`hashPW = Encrypt(newPW1)`* is concerning. **Do not encrypt passwords**, when the attacker gets the DB he will also get the encryption key. Just using a hash function is not sufficient and just adding a salt does little to improve the security. Use a function that iterates over an hash with a random salt for about a 100ms duration and save the salt with the hash. Use a function such as `PBKDF2`, `Rfc2898DeriveBytes`, `Argon2i`, `password_hash`, `Bcrypt` or similar functions. The point is to make the attacker spend substantial of time finding passwords by brute force. — zaph, Nov 05 '18 at 20:54
Your code is probably simplified too much, or it cannot work at all. `WHERE Username = pwChangeUsrnm` is wrong syntax, `Password` is a reserved word. Strings in SQL must be enclosed in quotes (single or double). [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Nov 05 '18 at 22:31

score 0 · Answer 1 · answered Nov 06 '18 at 10:53

I think Andre has the right of it. I tried adjusting the hashing code to add a letter character and this worked, but then I needed to go back and add the single quote around the hashed PW value- which probably would have made the code work even without adding the letter:

If newPW1 = newPW2 Then
    MsgBox ("Passwords Match!")
    hashPW = Encrypt(newPW1)
    MsgBox ("HashedPW is " & hashPW)
    updatePW = "UPDATE Users SET Password = '" & hashPW & "' WHERE Username = pwChangeUsrnm"
    DoCmd.RunSQL (updatePW)

A thanks to Zaph's second comment on security as well, I'll take that all into account. For the purposes of this database security isn't too much of a concern as it will be sitting behind existing security measures. The hashing of passwords is more just to avoid ever displaying the passwords in plain text. Nevertheless it's useful to know about these extra functions.

Unwanted Scientific Notation in MS Access

1 Answers1