Access VBA Encryption - AES256 Cipher for Text strings?

[ironfelix717]

Hi,

What are you guys using for good 'data access' level encryption? There are a number of RC4 and XORC Ciphers out there for strings. While they work OK, i have a few issues with everything i've found using these...

1.) I'm not convinced a 40-50 line Function is all that secure of an algorithm...
2.) They don't output Base 64 text which doesn't play nice with Access' text fields. (I tried resolving that myself and resulted in an asymmetrical function.)
3.) Continuing on point #2 above: I had issues with specific encryptions that clashed with SQL Queries (must've outputted leading CHR(34)s?) "Error in Syntax...."
3.) They aren't even a cipher (I have code for an AES256 Hash - thats easy to find).

Does anyone have anything out there ready to go?

Thanks

 

[isladogs]

I have used RC4 encryption in numerous databases for over 15 years.
It uses 128 bit encryption and is secure enough for any purpose where I would employ an Access database.
I would never use XOR - its too easy to crack

Unless the RC4 cipher is known, the encrypted data is effectively unreadable.
I usually encrypt the cipher itself (using a different function)

Using the cipher, the encryption is reversible.
For that reason, RC4 encryption does not meet the standards required for storing e.g. credit card information ...
...but I would never store that info in Access anyway due to the inherent weaknesses in a file based database such as Access

My usage includes encrypting any passwords stored in the database e.g. password protected forms, user passwords & in certain cases, data.
I have no problems with output in forms or queries
Here are links to two of my example apps which include RC4 encryption - the RC4 code is included in a module modEncryption

Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

Password Login - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

.

 

[ironfelix717]

Colin,

Thanks for sharing. Fair points about RC4. I don't find the file system weakness of Access really that big of a deal with security if the encrypted data is very strong and well designed (hash the pass with a randomized salt as the key, keep field names unspecific or under a unique alias). Should sensitive data be stored in an .accdb (personal use), the file system nature of Access is all the more reason to be utilizing the strongest encryption technology due to the fact that a brute force attack is really only feasible on data that is in possession of the attacker. (which might imply weak network security in the first place).

A WCS attack on this db would not cause the victim any direct life changing, financial, or otherwise damage so I'm not extremely concerned but t would be nice to have AES encryption technologies in this community.

For password encryption i make use of SHA512, SHA256.. with the System.Security.Cryptography object.

I'll check out the RC4 module.

-Regards

 

[ironfelix717]

Update:

Colin,
I examined the code in your example for RC4 encryption. It was one of the many i came across while searching for a sufficient algorithm. Albeit, one of the least favorited of the bunch... The mistake the author made was 1.) not fixing his error 2.) superficially patching a function as critical as an encryption device with "On Error Resume Next"... Shameful!!! Other than that, I am a fan.

The code errors when it gets a subscript error when he assigns to the Key() array in first loop. I made some modifications to the code which handles this, as I really wasn't able to figure out how to properly solve this error. Someone else probably can with little effort.

The last thing I did was (somewhat crudely) apply a Base64 arg. When set, the output is converted to Base 64, which plays much better with Access. The principle reason full ASCII isn't ideal with Access is because there will be inevitable SQL Syntax clashes. Suppose the encryption text is outputted by the cipher and results in a leading or trailing character of any SQL operator (ex: ",',>,<), there will inevitably be a, Error 3075 syntax collision with a statement such as...

SQL = "SELECT * FROM Accounts WHERE ID = '" & EncryptedText & "';"

And I can say just in development typing random values for debugging my application, I came across way more instances of that clash than I was comfortable with... Maybe there's a SQL work-around with syntax.

Whether this Base64 conversion implements weaknesses into the algorithm, I'm not sure. I don't believe it has an impact.

Attached is a .txt of the module, which contains the cipher, as well as 2 supporting functions and 1 test function.


-Regards

 

[isladogs]

WCS???
Perhaps you could share your SHA512/SHA256 code

EDIT: Your new post appeared as I was typing this. Will read it properly later

 

[cheekybuddha]

I posted a SHA512 hashing function here.

When it comes to passwords, you're better off hashing rather than using [as]symmetric encryption. You never need to know a user's password, just that it is correct.

hth,

d

 

[sonic8]

They don't output Base 64 text which doesn't play nice with Access' text fields.

Quick question:
The encrypted data cannot be used with any text operation anyways. So, why bother converting it to text at all and not store it as raw binary?

 

[ironfelix717]

WCS???

worst case scenario

When it comes to passwords, you're better off hashing rather than using [as]symmetric encryption. You never need to know a user's password, just that it is correct.

Correct. That's the point of a hash - to be asymmetric, which is not encryption (as you know).

The encrypted data cannot be used with any text operation anyways. So, why bother converting it to text at all and not store it as raw binary

Would that be using the Binary data type, you are saying? Never used that data type so this might be a first. However, whether or not controls (listboxes, etc) support full ASCII then base 64 is still a safer option. Imagine a scenario where you display the encrypted values in a listbox (just to show they are encrypted). The listbox control might have issues with displaying the ASCII characters (i'm not sure here, i'm just taking the safe approach of B64, which is why i added the functionality). In other words, there are considerations beyond the backend (FE controls, etc).

----UPDATE
As per colin's request, I attached the SHA Hash function i modified. The source is in the code header. Its from Wiki so its pretty popular but my god, the code is hideous. I cleaned it up and added support for 3 different output encodes: HEX, B64, FULL ASCII. Includes popular hashing algs, like 512, 384, 256, MD5...

 

[cheekybuddha]

>> That's the point of a hash - to be asymmetric, which is not encryption (as you know). <<

Asymmetric encryption is encryption - it just uses different keys to encrypt and decrypt whereas symmetric encryption uses the same key for both encrypting/decrypting.

Hashing is different.

 

[isladogs]

I examined the code in your example for RC4 encryption. It was one of the many i came across while searching for a sufficient algorithm. Albeit, one of the least favorited of the bunch... The mistake the author made was 1.) not fixing his error 2.) superficially patching a function as critical as an encryption device with "On Error Resume Next"... Shameful!!! Other than that, I am a fan.

The code errors when it gets a subscript error when he assigns to the Key() array in first loop. I made some modifications to the code which handles this, as I really wasn't able to figure out how to properly solve this error. Someone else probably can with little effort.

As I said before I've been using this code in many production databases and several thousand users for over 15 years without problems
I tried removing the Resume Next line and got no errors on testing

I haven't yet had time to study your code yet but see no reason to modify the original

The last thing I did was (somewhat crudely) apply a Base64 arg. When set, the output is converted to Base 64, which plays much better with Access.

I never use Base64. How is it better in your opinion (or is that covered below)?

The principle reason full ASCII isn't ideal with Access is because there will be inevitable SQL Syntax clashes. Suppose the encryption text is outputted by the cipher and results in a leading or trailing character of any SQL operator (ex: ",',>,<), there will inevitably be a, Error 3075 syntax collision with a statement such as...

SQL = "SELECT * FROM Accounts WHERE ID = '" & EncryptedText & "';"

And I can say just in development typing random values for debugging my application, I came across way more instances of that clash than I was comfortable with... Maybe there's a SQL work-around with syntax.

Whether this Base64 conversion implements weaknesses into the algorithm, I'm not sure. I don't believe it has an impact.

That's not the way to query a table with encrypted data. Instead you should use something like

SQL = "SELECT * FROM Accounts WHERE ID = '" & RC4(EncryptedText,"YourRC4Cipher") & "';"

In other words, replicate the code I use in my TestRC4 function -which of course should NEVER exist in a production database

I agree with @cheekybuddha's comments.
In general, I recommend that clients rely on passwords stored in Active Directory rather than store these in Access.
However if they insist on storing passwords within the Access database, I use RC4 encryption to do so.
I have never yet known of passwords being 'stolen' except in cases where users were 'sloppy' about security.
For example a student who obtained several teachers' passwords due to their carelessness

 

[The_Doc_Man]

My question is this: So you have a Base64 string. What are you going to do with in in that format OTHER than store it or decrypt it? The string is total gibberish in that format. What SELECT statement can you use on it that would make any kind of sense?

 

[cheekybuddha]

How do you propose to store the byte array output by the hashing function?

 

[cheekybuddha]

Sorry @The_Doc_Man, I skimmed over your reply/question on my phone and didn't really understand it.

I'm not sure who your question was aimed at, but you ask:

>> What are you going to do with in in that format OTHER than store it or decrypt it? <<

What else would you want to do with it?

I would only ever want a hash so I could compare whether an input password is correct, without needing to store the actual password in the db. So all I ever need to do is select it.

 

[CJ_London]

another use for hash is for comparing new data with existing data in tables with many fields. hash all the fields (or the critical ones) and store in the table - not essential but improves performance. New record comes along with the same hash value - you have a duplicate. Makes for simpler sql when you only need to compare one field rather than lengthy and, and and field comparisons.

 

[The_Doc_Man]

What else would you want to do with it?

OK, I'll explain that I was looking at discussions of using a SELECT clause that looked at an encrypted field and the discussions that ensued. I can tell you that from the U.S. Navy viewpoint, code that would compare an encrypted field using a function to show the encryption method (by encrypting a potential comparand) is an immediate no-no because what it does is it reveals the encryption parameters. So unless the code is ALSO encrypted, you just gave away the keys to the front door. And without that encryption of a comparand as part of the SELECT, you have no insight into the content of the encrypted field and therefore cannot compare to it, compute with it, etc. The only things you could do were to decrypt it or copy it or delete it.

Other than a discussion of encryption, I guess I am confused by the purpose of the thread.

 

[cheekybuddha]

>> So unless the code is ALSO encrypted <<
Yes, you would never, deploy your encryption code in an Access database without compiling it to an accde.

However, one ought to distinguish between hashing and using [a]symmetric encryption. When using (and storing) a hash, you will never* reveal the user's password. As long as the method for salting and number of hashing passes remains within the compiled code it would take an inordinate amount of computing power and time to crack the pw. Also, if the code is compiled a hacker won't be able to intercept the point at which the comparison of the freshly hashed comparand to the stored hash is made.

*If an attacker can decompile your accdb it's game-over anyway!

With [a]symmetric encryption life is more tricky since you have to manage the cipher key within the database and decompiling might well reveal it/them.

As we all know, Access is not a tool to store really sensitive info - its security is non-existent.

 

[The_Doc_Man]

I'm quite familiar with hashing and encryption methods. I agree that if you are talking a serious need for encryption in Access, you are using the wrong tool for the job. But then again, I came from a U.S. Navy background for the last 28 1/2 years of my career before I retired. We were thoroughly steeped in issues of how to encrypt and how to use encrypted data. I held a CompTIA Security+ certificate that was renewed every three years and I took other Navy security courses as well. I was just wondering where that conversation was going, that's all.

It was sort of like an oxymoron - such as "jumbo shrimp" or "military intelligence" or "secure Access."

 

[ironfelix717]

Hi all,

I see some discussion took way. I haven't been working on this project since which is due to my absence here.

Allow me to explain/answer some of the questions/concerns.

Isladogs:

As I said before I've been using this code in many production databases and several thousand users for over 15 years without problems
I tried removing the Resume Next line and got no errors on testing

I haven't yet had time to study your code yet but see no reason to modify the original

Unless their are differences in bittness of our machines (i am x64), and that somehow has an effect on my execution versus yours, your original function does fail and you should re-verify your test. See attached GIF which I tested on your latest copy per your website. If you do find it errors on x86, I would be curious to know if you are able to understand why. In fact, if it doesn't error, I would be curious to know why it errors only on x64.


Secondly, whether it fails or not, the practice of using On Error Resume Next is extremely poor and especially negligent in this application (encrypting data). The user is asking for corruption with that - even if 9999/10000 the function is successful. I certainly don't want to be the one trying to figure out what a piece of data was because an encryption string came out erroneous due to a blind error (which would be theoretically impossible).

I never use Base64. How is it better in your opinion (or is that covered below)?

Yes that was covered, which is not a unique idea of my own. See this thread: HERE
Special characters provide 2 problems with this application:
1.) They invoke an Error 3075 which is the result of a failed SQL query due to SQL specific operators being outputted to the encryption text, thus when queried it fails. I mentioned that I had multiple collisions already just testing similar encryption algorithms. This was already discussed but I have further demonstrated this in the attached image, which attempts to execute a SQL string that queries from a LIKE clause that is generated with a function to simulate special char encryption. Perhaps there is a way to tell the Jet Engine to work around these characters, but this is how I write queries every day and I would prefer not to change.


2.) They presumably have conflicts with controls or other components in Access. Why would you load encrypted text in a control? Great question. Mostly you wouldn't, afterall it is jibberish, but in my application, the user has an option to encrypt partial amounts of text on the screen via button click to prevent prying eyes nearby. Yeah there are other ways, (just hide the control), but this is cooler. (this should answer @The_Doc_Man questions).

I think that covers most of what you were trying to ask or discuss.

@cheekybuddha

*If an attacker can decompile your accdb it's game-over anyway!

I think someone above answered this. Security is a multi-layer science. No production frontend in the world should be distributed in .accdb and if that is happening, I'm sorry to hear that... Even with compiled code you're not 100% safe, as memory analyses can be done. Fortunately, you don't always have to be 100% safe, you just have to be safe long enough to catch the bad guy.



-Regards

 

[isladogs]

Sorry to repeat myself but I cannot replicate your out of range error UNLESS I comment out the On Error Resume Next line (AS YOU HAVE DONE).
Whilst I accept your point about using that line in code, it is of course there for a reason!
There is nothing to stop you adding proper error handling if its use is a deal breaker for you

I have thoroughly tested my code in 'pure' 32 bit and 64-bit Access/Windows as well as 32-bit Access in 64-bit Windows. No error.

Moreover the code has been in widespread use in production databases for 15 years using all versions of Access from 2003 through to 365.
In all that time, I have never had to deal with an issue where that code led to an error.

I recommend storing the RC4 key as a public constant or in a settings table using a different form of encryption.
For the purposes of the password login demo that you tried, I stored it as an unencrypted public constant at the top of the module.

Public Const RC4_Key = "isladogs" 'This key is used to store the passwords - ideally it should be encrypted by a different method

Here are some test results from the immediate window. You can either use the public constant or a variable

?rc4("hello world",RC4_Key)
ËË|‹o׸™
?rc4("hello world","isladogs")
ËË|‹o׸™
?rc4("hello world","RC4_Key")
£®Ïû|JkIW
?rc4("hello world","mypass")
ª¥ÅÄü ˜¦
?rc4("hello world","ironfelix717")
œ(7µò_NØ

I've already answered the point you made about the query.
Of course the query will fail if you filter encrypted data using the decrypted string.
However, I covered how to run a query based on encrypted data in post #10.
Perhaps you missed that ...

SQL = "SELECT * FROM Accounts WHERE ID = '" & RC4(EncryptedText,"YourRC4Cipher") & "';"

That also works using Like filters.

I'll leave others to respond to the last part of your post.

 

[ironfelix717]

Sorry to repeat myself but I cannot replicate your out of range error UNLESS I comment out the On Error Resume Next line (AS YOU HAVE DONE).
Whilst I accept your point about using that line in code, it is of course there for a reason!

You said, "I tried removing the Resume Next line and got no errors on testing". That is inconsistent with that you say above, which is why in my last post I explained that your code does error. And it errors 100% of the time if the On Error Resume Next is removed. Regardless, as I argued, this isn't the way I'm going to handle a function as critical as this - whether or not you've used it for 15 years - and I offered my modified solution to this forum which i've explained the advantages of several times now... I would like to solve this Error #9 permanently because i think its a pretty simple fix, i just can't seem to understand why the array is out of assignment.

Of course the query will fail if you filter encrypted data using the decrypted string.

This isn't the topic were discussing. All that would result in is a query that returns 0 results, so no. it wouldn't fail. It would fail if the user is querying an encrypted table with an encrypted text that includes leading characters such as chr34,chr39... etc. I demonstrated exactly that problem with non-base64 SQL in my attachment in the previous post.

Assume this example:

SQL = "SELECT * FROM Accounts Where ID = '" & RC4(txtUserID) & "';"

txtUserID contains plaintext and the table Accounts contains IDs that are all encrypted.

If RC4 produces leading or trailing special chars that collide with SQL, you will have a bad time, hence Base64 was added.