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:
None 
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
Description:
Windows versions of MySQL server can not handle returning AES_ENCRYPT data from a function however, the Linux version of MySQL can return the data. The error received from MySQL running on a Windows Server is: #1366 - Incorrect String Value

How to repeat:
The following function works fine under Linux Enterprise Server 3 running MySQL Server 5.0.37. However, thus function does NOT work under Windows Server 2003 running MySQL Server 5.0.37. The error message is:

#1366 - Incorrect String Value

---- Cut and Paste of Actual Function ----

CREATE FUNCTION GetEncryptedText ( pText VARCHAR(255) ) RETURNS VARCHAR(255)
MAIN: BEGIN

	DECLARE mRecordNotFound BOOL DEFAULT FALSE;
	DECLARE mEncryptionKey VARCHAR(256) DEFAULT "";

	-- SQL Error Checking Definition
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET mRecordNotFound = TRUE;

	-- ****************************************************************
	-- VALIDATION

	IF pText = "" THEN
		RETURN "";
	END IF;

	-- Load the EncryptionKey from the ServerConfig table.
	SET mRecordNotFound = FALSE;
	SELECT KeyData INTO mEncryptionKey
		FROM ServerConfig
		WHERE KeyName = "EncryptionKey";

	IF mRecordNotFound = TRUE OR mEncryptionKey = "" THEN
		CALL ServerLogEntry( 3, "GetEncryptedStr", "VALIDATION: EncryptionKey not found or invalid." );
		RETURN "";
	END IF;

	-- VALIDATION
	-- ****************************************************************

	RETURN AES_ENCRYPT( pText, mEncryptionKey );

END MAIN;

---- Cut and Paste of Improvised Function for Testing ----

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;

To reproduce after creating the above improvised function:

select GetEncryptedText( "test" );

Suggested fix:
Unknown
[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/