Paul DuBois said:
> At 15:29 -0800 11/6/03, Herb Rubin wrote:
>>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)
>>>
>>> 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') |
>>>>> +--------------------------------------------------------------+
>>>>> |
>> >>> k\ÎúVÀàÿxû÷Ò |
>>>>> +--------------------------------------------------------------+
>>>>>
>>>>> What version are you using?
>>>>>
>>>>>
>>>>>>
>>>>>>Help!
>>>>>>
>> >> >>Herb
>
>
> --
> Paul DuBois, Senior Technical Writer
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> Are you MySQL certified? http://www.mysql.com/certification/
So a char(16) binary would work fine as well?
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
