md5 and salt on a column in MSAccess table

Question

Is there anyway I can run an update query to change a column of plain text stored passwords into a column that's md5+salt'ed in msaccess?

I tried this:

UPDATE TableName
SET Pass = CONCAT(MD5(CONCAT('salt', Pass)), ':salt');

But of course it didn't work, if i put the table name first like: users.Pass, then it asks for a file, if I don't it it tells me that access can't find the input table. And also that there's no CONCAT function, which I don't understand at all.

I'm trying to convert an access db to a mysql db to import customers into a magento install and don't have a lot of experience with access. Thanks.

MS languages use `+`instead of a `concat` function or the SQL standard `||`. — FrankPl, Jul 30 '13 at 17:12
I would recommend using a different hashing function than MD5 because it is vulnerable. Even if the data you're protecting is trivial, users tend to use real passwords for them. http://security.stackexchange.com is a good starting point to find out some basics. — ashareef, Jul 30 '13 at 17:17
@ashareef - but magento uses md5+salt, I don't think I can change that during the import — salty, Jul 30 '13 at 17:26
@FrankPl - so replace concat with +? I'll try it. Hmm not working, maybe I'll just transfer the plain text into a mysql db and run it that way — salty, Jul 30 '13 at 17:27
@Salty - yes. But Access does not have MD5 either I would assume. You would have to implement this somehow in VBA if you need it within Access SQL. — FrankPl, Jul 30 '13 at 17:29
@FrankPl - yes, I ran into that as soon as I tried a few more things. I'm thinking this solution is above my paygrade and I need to finish this import. I'll just have to make it a normal mysql db. Thanks again! — salty, Jul 30 '13 at 17:47

score 3 · Accepted Answer · edited May 23 '17 at 12:05

I suggest making a DAO loop that loops through all records and sets the Pass for each record. You'll have to write an MD5 hashing function for VBA or find one, or as already suggested, use SHA1.

Concatenation in Access and Access/Jet/DAO SQL is done using either ampersands or plus signs.

Here is a function for hashing a string using SHA1. It returns a 40 character hex string. I'm not sure though if this will be compatible with Magentos hashing algorithms.

Public Function SHA1Hex(S As String) As String
    Dim asc, enc, bytes, outstr, pos
    'Borrow some objects from .NET (supported from 1.1 onwards)
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.SHA1CryptoServiceProvider")
    'Convert the string to a byte array and hash it
    bytes = asc.GetBytes_4(S)
    bytes = enc.ComputeHash_2((bytes))
    outstr = ""
    'Convert the byte array to a hex string
    For pos = 1 To LenB(bytes)
        outstr = outstr & LCase(Right("0" & Hex(AscB(MidB(bytes, pos, 1))), 2))
    Next
    SHA1Hex = outstr 'Returns a 40 byte/character hex string
    Set asc = Nothing
    Set enc = Nothing

End Function

Code for a DAO loop can be found on SO here: Code to loop through all records in MS Access

md5 and salt on a column in MSAccess table

1 Answers1