AES_ENCRYPT/DECRYPT & MS Access VBA

[Benjamin Kleynhans]

Hi Guys and Girls,

I've been developing as a hobby for quite a while now, but I've never had a need for encryption, until now that is.

I'm quite advanced in VBA and am currently studying towards C, but I'm not proficiant enough in C to complete what I'm currently busy with, and this is where the trick comes in.

##########################
Project Overview
##########################

MS Access 2010 is used for the Front-End and MySQL 5.1.61 used for the database. There are loads of tables, but I just need help on the user access part.

Since I had to do Encryption, I thought what better way to do it than to go all out, so I'm using a query to generate individual keys per user for encryption. This works well within MySQL, but the moment I re-write the query for use with Record Sets in VBA, the encrypted data is returned as a Hex "NULL", the idnumber selected in the same statement is however returned correctly.

I've included the following for your perusal:

1. Descriptions of 2 Tables being used.
2. INSERT and SELECT statements that are WORKING in MySQL as well as results
3. SELECT statement that generates the Key as well as result
4. The SELECT statement in VBA. This one returns the value that is not encrypted, but a Hex value for NULL as the password.

Tables look as follows:

##########################
1.1 Users Table
##########################

mysql> desc t_users;
+------------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------------+------+-----+---------+----------------+
| f_anumid | int(11) | NO | UNI | NULL | auto_increment |
| f_firstname | varchar(25) | YES | | NULL | |
| f_lastname | varchar(25) | YES | | NULL | |
| f_employer | varchar(30) | YES | MUL | NULL | |
| f_idnumber | varchar(13) | NO | PRI | NULL | |
| f_activeinactive | enum('Active','Inactive') | YES | | Active | |
+------------------+---------------------------+------+-----+---------+----------------+

##########################
1.2 Passwords Table
##########################

mysql> desc t_passwords;
+------------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------+------+-----+---------+----------------+
| f_anumid | int(11) | NO | PRI | NULL | auto_increment |
| f_idnumber | varchar(13) | YES | MUL | NULL | |
| f_password | varbinary(200) | YES | | NULL | |
+------------+----------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

##########################
2.1 INSERT Statement
##########################

INSERT INTO t_passwords(f_idnumber, f_password) VALUES (
"7912156215348",
AES_ENCRYPT("Password",(
SELECT CONCAT(
LEFT(f_firstname,1),
LEFT(f_lastname,1),
LEFT(f_idnumber,6))
FROM t_users WHERE f_idnumber = "7912156215348")));

##########################
2.2 SELECT Statement and Result
##########################

SELECT f_idnumber,
AES_DECRYPT(f_password,(
SELECT CONCAT(
LEFT(f_firstname,1),
LEFT(f_lastname,1),
LEFT(f_idnumber,6))
FROM t_users WHERE f_idnumber ="7912156215348")) AS "Password"
FROM t_passwords WHERE f_idnumber = "7912156215348";

+---------------+----------+
| f_idnumber | Password |
+---------------+----------+
| 7912156215348 | Password |
+---------------+----------+
1 row in set (0.00 sec)

##########################
3.1 Key SELECT Statement and Result
##########################

SELECT CONCAT(
LEFT(f_firstname,1),
LEFT(f_lastname,1),
LEFT(f_idnumber,6)) AS "Key"
FROM t_users WHERE f_idnumber ="7912156215348";

+----------+
| Key |
+----------+
| BK791215 |
+----------+
1 row in set (0.00 sec)

##########################
4.1 VBA RecordSet SELECT Statement
##########################

rs.Source = "SELECT f_idnumber, " & _
"AES_DECRYPT(f_password, (" & _
"SELECT CONCAT(" & _
"LEFT(f_firstname,1), " & _
"LEFT(f_lastname,1), " & _
"LEFT(f_idnumber,6)) " & _
"FROM t_users WHERE f_idnumber = '" & UUsername & "'))" & _
"FROM t_passwords WHERE f_idnumber = '" & UUsername & "'"

##########################
4.2 Result from RecordSet Query
##########################

idnumber password
-------------- --------
7912156215348 ????

##############################################################################
##############################################################################

Hence, if anyone can help me to understand where I'm going wrong, or how to approach this if decryption cannot be done within the Record Set selection I would greatly appreciate it.

C'ya
Ben