Paul,
Is there a limitation on having a TINYBLOB as a primary key?
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
>
>
> --
> 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]
>
--
Herb Rubin Pathfinders Software
[EMAIL PROTECTED] http://www.pfinders.com
phone: 650-692-9220 fax: 650-692-9250
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
