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]