AES Encryption

Hi,

I am having a nightmare with mySQL AES_DECRYPT.

I can enrypt things fine, but how do you decrypt a column? For example, I write this line:

insert into tblTest (TestColumn) VALUES (AES_ENCRYPT('testworks','encryptkey'))

which happily inserts "testworks" in encrypted format

When I then try and use:

SELECT TestColumn, AES_DECRYPT(tblTest.TestColumn,'encryptkey') FROM tblTest;

The decrypted column is just nulls. I've been reading google for ages, but I just can't make it work

Any help would be great,

Thanks
James

jimbo wrote:
> Hi,
>
> I am having a nightmare with mySQL AES_DECRYPT.
>
> I can enrypt things fine, but how do you decrypt a column? For example, I write this line:
>
> insert into tblTest (TestColumn) VALUES (AES_ENCRYPT('testworks','encryptkey'))
>
> which happily inserts "testworks" in encrypted format
>
> When I then try and use:
>
> SELECT TestColumn, AES_DECRYPT(tblTest.TestColumn,'encryptkey') FROM tblTest;
>
> The decrypted column is just nulls. I've been reading google for ages, but I just can't make it work
>
> Any help would be great,
>
> Thanks
> James

Ensure all fields that are to be encrypted are BLOBS. You cannot store
AES encrypted data in text fields.

Leastways that's what I did and it works.



--
Ineptocracy

(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.

hmmm, I did that before.

When I did it, the encrypted text did not appear in the blob column - only the word "BLOB". And the unencrypted column was still all "NULL"...

ok, so I am no PHP expert, what I am actually trying to do is to do it from VBA. At the moment I am passing in the query as string (I will change this to a mySQL routine once I get it working).

So, I looked at what you sent over and did the following:

In mySQL query editor:
- Created a new table - tblTest
- put in one column - TestColumn
- insert an encrypted value -> insert into tblTest (TestColumn) VALUES (AES_ENCRYPT('TestValue','encryptkey'))

If I view the table in mySQL I just see "BLOB" - as you explained (I am not well versed enough to know this) - this seems to be what you would expect to see.

I then (in VBA) - passed the following query into mySQL:

strSQL = "SELECT AES_DECRYPT(TestColumn,'encryptkey') from tblTest"

This returned me a non-empty recordset, but when I interrogate the value returned it is defined as:

?rsdataPersonalInfo.fields(0)
????

I'm obviously missing something here - any help would be appreciated.

El 08/08/2012 10:58, jimbo escribió/wrote:
> I am having a nightmare with mySQL AES_DECRYPT.
>
> I can enrypt things fine, but how do you decrypt a column? For example, I write this line:
>
> insert into tblTest (TestColumn) VALUES (AES_ENCRYPT('testworks','encryptkey'))
>
> which happily inserts "testworks" in encrypted format
>
> When I then try and use:
>
> SELECT TestColumn, AES_DECRYPT(tblTest.TestColumn,'encryptkey') FROM tblTest;
>
> The decrypted column is just nulls.

And what does the _encrypted_ column look like?


mysql> SELECT
-> AES_ENCRYPT('testworks','encryptkey') AS encrypted,
-> AES_DECRYPT(AES_ENCRYPT('testworks','encryptkey'),
'encryptkey') AS decrypted;
+------------------+-----------+
| encrypted | decrypted |
+------------------+-----------+
| Õåó█I)╗#?�ëxra�Ë | testworks |
+------------------+-----------+
1 row in set (0.00 sec)


> I've been reading google for ages, but I just can't make it work

The official manual is often a good alternative to random Googleing ;-)

http://dev.mysql.com/doc/refman/5.5/...functions.html



--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

�lvaro G. Vicario wrote:
> El 08/08/2012 10:58, jimbo escribió/wrote:
>> I am having a nightmare with mySQL AES_DECRYPT.
>>
>> I can enrypt things fine, but how do you decrypt a column? For
>> example, I write this line:
>>
>> insert into tblTest (TestColumn) VALUES
>> (AES_ENCRYPT('testworks','encryptkey'))
>>
>> which happily inserts "testworks" in encrypted format
>>
>> When I then try and use:
>>
>> SELECT TestColumn, AES_DECRYPT(tblTest.TestColumn,'encryptkey') FROM
>> tblTest;
>>
>> The decrypted column is just nulls.
>
> And what does the _encrypted_ column look like?
>
>
> mysql> SELECT
> -> AES_ENCRYPT('testworks','encryptkey') AS encrypted,
> -> AES_DECRYPT(AES_ENCRYPT('testworks','encryptkey'),
> 'encryptkey') AS decrypted;
> +------------------+-----------+
> | encrypted | decrypted |
> +------------------+-----------+
> | Õåó█I)╗#?�ëxra�Ë | testworks |
> +------------------+-----------+
> 1 row in set (0.00 sec)
>
>
> > I've been reading google for ages, but I just can't make it work
>
> The official manual is often a good alternative to random Googleing ;-)
>
> http://dev.mysql.com/doc/refman/5.5/...functions.html
>
>
>
"If AES_DECRYPT() detects invalid data or incorrect padding, it returns
NULL."

"encryption and compression functions return strings for which the
result might contain arbitrary byte values. If you want to store these
results, use a column with a VARBINARY or BLOB binary string data type.
This will avoid potential problems with trailing space removal or
character set conversion that would change data values, such as may
occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT). "

--
Ineptocracy

(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.