Paul,
I did try VARCHAR(16) BINARY and it still failed to INSERT in a NOT NULL column.
The encrypted string seems to be equivalent to the NULL value even though it visually looks like some kind of data.
Herb
Okay, I investigated this further and I believe I know the cause of the problem. The solution is to use a TINYBLOB NOT NULL rather than a VARCHAR(16) NOT NULL.
Here's what's going on:
- The particular encrypted value you calculate ends with 0x20. That is, the same value as a space character. - Trailing spaces are trimmed from values stored in VARCHAR columns. - That means when you retrieve the value, it's 15 bytes long, and is no longer a legal encrypted value. - AES_DECRYPT() returns NULL for illegal encrypted values.
Can you instead use CHAR(16) instead of VARCHAR(16)? No, because the trailing space would still be trimmed *when the value is retrieved*, and you'd still get NULL from AES_DECRYPT().
Use a TINYBLOB instead. Trailing spaces won't be trimmed when the value is stored, or when it is retrieved. AES_DECRYPT() will work.
In general, you shouldn't try to use CHAR/VARCHAR for encrypted values or other forms of binary data. Use BLOB columns instead.
--------------------------------------+Please reply to the list, not to me directly, so that others can follow this discussion. Thanks.
At 14:54 -0800 11/6/03, Herb Rubin wrote:Paul,
Yes, I get the same, now try and decrypt it, it will turn out to be NULL. So, you cannot insert this into a NOT NULL column. It will reject it.
Your message (below) appears to be reporting a problem with AES_ENCRYPT(). It states that you get a NULL return value from that function.
You now appear to be saying something else.
I don't understand what problem you're trying to report.
My result from decryption:
mysql> select AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561'); +--------------------------------------------------------------+ | AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') | +--------------------------------------------------------------+ | k\ÎúVÀàÿxû÷Ò | +--------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> select AES_DECRYPT(AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561'), -> '0bf251c9aaf007deaf1143ca1492b561'); +---------------------------------------------------------------------------------------------------------------+| AES_DECRYPT(AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561'), '0bf251c9aaf007deaf1143ca1492b561') | +---------------------------------------------------------------------------------------------------------------+| 551850040 | +-------------------------------------------------------------------------1 row in set (0.07 sec)>>> k\ÎúVÀàÿxû÷Ò |
Are you reporting that you get NULL only in the context of inserting and retrieving the value from the id column?
If so, please try using TINYBLOB rather than VARCHAR and see what happens.
we are on 4.0.14 Herb
At 14:03 -0800 11/6/03, Herb Rubin wrote:Hi,
I am trying to use aes_encrypt and I get a NULL value with a specific string:
INSERT INTO test SET `id` = AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561');
my field 'id' is VARCHAR(16) NOT NULL
If I change the value or the encryption string it works. But this combination turns out to be null and it refuses to insert.
With MySQL 4.0.14, 4.0.16, and 4.1.1, I get:
mysql> select AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561'); +--------------------------------------------------------------+ | AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') | +--------------------------------------------------------------+ |>> >>Herb+--------------------------------------------------------------+
What version are you using?
Help!
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
