Hi Herb,
You should be able to specify 255 as the PRI KEY length... I think.
CREATE TABLE table (
col TINYBLOB NOT NULL,
PRIMARY KEY (col(255))
);
Hope that helps.
Matt
----- Original Message -----
From: "Herb Rubin"
Sent: Friday, November 07, 2003 10:50 AM
Subject: Re: aes encryption bug
Paul,
TINYBLOB does seem to hold the value properly.
I can't use a TINYBLOB in a primary key. Is this not allowed?
The ALTER statement complains that I am using a column without a length
specified.
Herb
On Thu, 2003-11-06 at 19:24, Paul DuBois wrote:
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
