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)
The key goes in the second argument for both functions. Assuming the key is "007", your second statement has two errors:
- It puts the password column in quotes ("password"), thus
causing to be treated as a literal string, not a column
value.
- It mixes up the password value and key value.Try this for the second statement:
SELECT * FROM user_profile WHERE userid="james" AND AES_DECRYPT(password,"007")="bond";
When faced with this kind of situation, you might try try selecting the AES_DECRYPT() value to see what you're getting, rather than just trying to figure out the right format in the WHERE clause. For example:
mysql> SELECT userid, AES_DECRYPT(password,"007") FROM user_profile; +--------+-----------------------------+ | userid | AES_DECRYPT(password,"007") | +--------+-----------------------------+ | James | bond | +--------+-----------------------------+
Another thing: Make sure your password column is at least 16 bytes long. AES_ENCRYPT() produces a 128-bit value (if memory serves), so if you have a column shorter than 16 bytes, you'll have problems.
-- 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]
