Bug #28228 | Windows can not return AES_ENCRYPT data from function | ||
---|---|---|---|
Submitted: | 3 May 2007 22:58 | Modified: | 15 May 2007 7:17 |
Reporter: | Van Stokes | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
Version: | 5.0.37 | OS: | Windows (Server 2003) |
Assigned to: | CPU Architecture: | Any |
[3 May 2007 22:58]
Van Stokes
[3 May 2007 23:16]
MySQL Verification Team
Thank you for the bug report. Could you please show the result you got. I was not able to repeat the error message you are reporting. Thanks in advance. Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.37-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test Database changed mysql> delimiter // mysql> CREATE FUNCTION GetEncryptedText ( pText VARCHAR(255) ) RETURNS VARCHAR(255) -> MAIN: BEGIN -> -> DECLARE mRecordNotFound BOOL DEFAULT FALSE; -> DECLARE mEncryptionKey VARCHAR(256) DEFAULT "1qaz2wsx3edc4rfv"; -> -> -- **************************************************************** -> -- VALIDATION -> -> IF pText = "" THEN -> RETURN ""; -> END IF; -> -> -- VALIDATION -> -- **************************************************************** -> -> RETURN AES_ENCRYPT( pText, mEncryptionKey ); -> -> END MAIN// Query OK, 0 rows affected (0.24 sec) mysql> delimiter ; mysql> select GetEncryptedText( "test" ); +----------------------------+ | GetEncryptedText( "test" ) | +----------------------------+ | ↔ã$▒$b◄▀↕ù═~│=ƒ° | +----------------------------+ 1 row in set (0.06 sec)
[4 May 2007 21:01]
Van Stokes
I receive the following error message: ERROR 1366 Incorrect string value: '\xC6$\xB1$b\x11...' for column 'GetEncryptedText( "test" )' at row 1 When running this statement: select GetEncryptedText( "test" ); in "MySQL Query Browser 1.2.11" or "mysql" from the windows command line. NOTE: Here is my SHOW CREATE DATABASE command: CREATE DATABASE `origins` /*!40100 DEFAULT CHARACTER SET utf8 */
[4 May 2007 21:09]
Van Stokes
Screen Shot from Windows command line: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -u root -p origins Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 467 Server version: 5.0.37-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select GetEntryptedText( "test" ); ERROR 1305 (42000): FUNCTION origins.GetEntryptedText does not exist mysql>
[4 May 2007 23:47]
Van Stokes
*sigh* let me do that again but this time, with the command spelled correctly: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -u root -p origins Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 468 Server version: 5.0.37-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select GetEncryptedText( "test" ); ERROR 1366 (HY000): Incorrect string value: '\xC6$\xB1$b\x11...' for column 'Get EncryptedText( "test" )' at row 1 mysql>
[7 May 2007 16:14]
MySQL Verification Team
Thank you for the feedback. Could you please verify if on your side Linux gives a warning, i.e: mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.42-debug | +--------------+ 1 row in set (0.00 sec) mysql> select GetEncryptedText( "test" )\G *************************** 1. row *************************** GetEncryptedText( "test" ): 1 row in set, 1 warning (0.01 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1366 Message: Incorrect string value: '\xC6$\xB1$b\x11...' for column 'GetEncryptedText( "test" )' at row 1 1 row in set (0.00 sec) mysql>
[8 May 2007 14:28]
Van Stokes
Per your request, here is the LINUX system information: (Reminder: Linux works fine, Windows does not work). $ cat /etc/redhat-release Red Hat Enterprise Linux ES release 3 (Taroon) $ cat /proc/version Linux version 2.4.21-4.EL (bhcompile@daffy.perf.redhat.com) (gcc version 3.2.3 2 0030502 (Red Hat Linux 3.2.3-20)) #1 Fri Oct 3 18:13:58 EDT 2003 $ mysql -u root -p test Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 46 Server version: 5.0.37-community MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select version(); +------------------+ | version() | +------------------+ | 5.0.37-community | +------------------+ 1 row in set (0.00 sec) mysql> select GetEncryptedText( "test" ); +----------------------------+ | GetEncryptedText( "test" ) | +----------------------------+ | F$1$b_M~3=x | +----------------------------+ 1 row in set (0.00 sec) mysql>
[8 May 2007 14:33]
Van Stokes
*continued from previous post* mysql> select GetEncryptedText( "test" )\G *************************** 1. row *************************** GetEncryptedText( "test" ): F$1$b_M~3=x 1 row in set (0.00 sec) mysql> show warnings\g Empty set (0.00 sec) mysql>
[11 May 2007 18:55]
Sergei Golubchik
That could be charset issues. Check SHOW VARIABLES LIKE 'character%'; on both your hosts, may be you have utf8 on Windows but not on Linux ? AES_ENCRYPT generates a binary string, no wonder that it may contain bytes that do not form valid utf8 characters. Then the correct solution is to fix your function, you should declare it to return binary data, as it's what it really returns (use CHARACTER SET BINARY).
[14 May 2007 21:07]
Van Stokes
OK, I changed the function to this: --- CUT AND PASTE --- CREATE FUNCTION GetEncryptedText( pText VARCHAR(255) ) RETURNS BINARY MAIN: BEGIN DECLARE mRecordNotFound BOOL DEFAULT FALSE; DECLARE mEncryptionKey VARCHAR(256) DEFAULT "1qaz2wsx3edc4rfv"; -- SQL Error Checking Definition DECLARE CONTINUE HANDLER FOR NOT FOUND SET mRecordNotFound = TRUE; -- **************************************************************** -- VALIDATION IF pText = "" THEN RETURN NULL; END IF; -- VALIDATION -- **************************************************************** RETURN AES_ENCRYPT( pText, mEncryptionKey ); END MAIN; Which then yields this when running the commands on a WINDOWS server: mysql> SELECT GetEncryptedTextTest( "Test" ); Error 1406 Data too long for column 'GetEncryptedText( "Test " )' at row 1 mysql> SHOW VARIABLES LIKE 'character%'; 'character_set_client', 'utf8' 'character_set_connection', 'utf8' 'character_set_database', 'utf8' 'character_set_filesystem', 'binary' 'character_set_results', 'utf8' 'character_set_server', 'utf8' 'character_set_system', 'utf8' 'character_sets_dir', 'C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\' I will post the Linux results in a bit.
[15 May 2007 7:17]
Sergei Golubchik
BINARY means BINARY(1). Certainly your data are too long for that. You used VARCHAR(255), so use BINARY(255) now. And anyway, this is starting to go out of the scope of bug database, if you have any more questions please use our forums or mailing lists: http://forums.mysql.com/ http://lists.mysql.com/