RE: Data store/extract help almost there ,still error's - jbon...@sola.com.au - com.mysql.lists.win32

2 messages in com.mysql.lists.win32RE: Data store/extract help almost th...

From	Sent On	Attachments
Stout, Jeff	20 Sep 2003 07:08
jbon...@sola.com.au	21 Sep 2003 16:50

Subject:	RE: Data store/extract help almost there ,still error's
From:	jbon...@sola.com.au (jbon...@sola.com.au)
Date:	09/21/2003 04:50:48 PM
List:	com.mysql.lists.win32

I think your second query should read

SELECT * FROM user_profile WHERE userid="james" AND AES_DECRYPT(password,"007")="bond";

When you are reading back, you are taking the encrypted password which you stored in the table and decrypting it using the encryption key, which was the second parameter to the AES_ENCRYPT, and comparing the result to the unencrypted password (presumably supplied by the user logging in).

John Bonnett

-----Original Message----- From: Stout, Jeff [mailto:jeff...@csgsystems.com] Sent: Saturday, 20 September 2003 11:39 PM To: jo...@hopkinsit.com Cc: win...@lists.mysql.com Subject: RE: Data store/extract help almost there ,still error's

Thanks John, I'm trying various syntax changes but still getting Empty set

mysql> INSERT INTO user_profile (userid,password) -> VALUES ("James",AES_ENCRYPT("bond","007")); Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user_profile WHERE userid="james" AND -> AES_DECRYPT("password","bond")="007"; Empty set (0.00 sec)

-----Original Message----- From: John Hopkins [mailto:john...@msn.com] Sent: Friday, September 19, 2003 6:38 PM To: Stout, Jeff Subject: RE: Data store/extract help almost there

I've been following with interest. As I understand the previous messages, you are indeed almost there

Try this:

mysql> INSERT INTO user_profile (userid,password) -> VALUES ("joeblow",AES_ENCRYPT("spit","swallow")); Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user_profile WHERE userid="joeblow" AND -> AES_DECRYPT("password","swallow")="spit";

I don't have MySQL running anywhere right now, can't test it. The point is you need to decrypt what's in *password*, and compare that to the unencrypted password (entered by the user?).

Hope this helps,

-----Original Message----- From: Stout, Jeff [mailto:jeff...@csgsystems.com] Sent: Friday, September 19, 2003 4:31 PM To: PF: MySQL; win...@lists.mysql.com Subject: RE: Data store/extract help almost there

Almost there, here is the error

mysql> INSERT INTO user_profile (userid,password) -> VALUES ("joeblow",AES_ENCRYPT("spit","swallow")); Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user_profile WHERE userid="joeblow" AND -> password=AES_DECRYPT("spit","swallow"); Empty set (0.01 sec)

-----Original Message----- From: PF: MySQL [mailto:ml.m...@in-genius.com] Sent: Friday, September 19, 2003 4:48 PM To: win...@lists.mysql.com Subject: RE: Data store/extract help !!

Woops, Sorry, Phone call distracted me....

AES_ENCRYPT(string,key_string) AES_DECRYPT(string,key_string)

These functions allow encryption/decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is usually secure enough. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL. As AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated as 16*(trunc(string_length/16)+1). If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key are invalid. You can use the AES functions to store data in an encrypted form by modifying your queries:

INSERT INTO t VALUES (1,AES_ENCRYPT("text","password"));

You can get even more security by not transferring the key over the connection for each query, which can be accomplished by storing it in a server side variable at connection time:

SELECT @password:="my password"; INSERT INTO t VALUES (1,AES_ENCRYPT("text",@password));

AES_ENCRYPT() and AES_DECRYPT() were added in version 4.0.2, and can be considered the most cryptographically secure encryption functions currently available in MySQL.