MySQL Bugs: #18634: ENCODE function does not accept variables

Bug #18634	ENCODE function does not accept variables
Submitted:	29 Mar 2006 22:58	Modified:	28 Oct 2019 23:31
Reporter:	reasmei soeung	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.22-BK, 5.0.12	OS:	Linux (Linux, Windows 2000 Server)
Assigned to:		CPU Architecture:	Any

Description:
I cannot use variables inside of any of the encryption/decryption functions in a stored procedure. Plus I can't even use variables inside GRANT statements.

How to repeat:
BEGIN

DECLARE key_str VARCHAR(10);
SET key_str = 'mykey';

SELECT ENCODE('teststring', key_str);

/* or */
SELECT AES_ENCRYPT('teststring',key_str);

/* or */
DECLARE myname VARCHAR(25);
SET myname = 'myfirstname@%';

GRANT ALL PRIVILEGES ON *.* TO myname IDENTIFIED BY 'mypassword';

END

Thank you for a bug report. I was able to repeat the behaviour you described for ENCODE function only with 5.0.21-BK (ChangeSet@1.2120, 2006-03-30 08:13:49+02:00) on Linux:

mysql> delimiter //
mysql> create procedure penc7()
    -> BEGIN
    ->
    -> DECLARE key_str VARCHAR(10);
    -> SET key_str = 'mykey';
    ->
    -> SELECT ENCODE('teststring', key_str);
    -> END;
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_str);
END' at line 5
mysql> create procedure penc7() BEGIN DECLARE key_str VARCHAR(10); SET key_str
= 'mykey'; SELECT AES_ENCRYPT('teststring', key_str); END;//
Query OK, 0 rows affected (0.00 sec)

mysql> call penc7()//
+------------------------------------+
| AES_ENCRYPT('teststring', key_str) |
+------------------------------------+
/                   |
+------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.0.21    |
+-----------+
1 row in set (0.00 sec)

So, AES_ENCRYPT works, and GRANT works also, but not as you might expected.

Grant does not work when you place a variable after "IDENTIFIED BY".
For example:

DECLARE n VARCHAR(25);
DECLARE p VARCHAR(25);

SET n = 'mylogin@%';
SET p = 'mypassword';

GRANT SELECT ON portal.* TO n IDENTIFIED BY p;

I get the same error as mentioned above.

Thanks,
Reas

ENCODE still can not be used in 5.0.22-BK. It is a bug. 

As for GRANT, looks like it is intended behaviour that should be documented. You may create a new bug report about this documentation issue.

ENCODE doesn't work with user variables as well, and it doesn't work with parameter markers. 
 
mysql> select encode("abc", @a);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@a)' at line 1
mysql> select decode("abc", @a);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@a)' at line 1
mysql> select decode("abc","pwd");
+---------------------+
| decode("abc","pwd") |
+---------------------+
¹d                 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select encode("abc","pwd");
+---------------------+
| encode("abc","pwd") |
+---------------------+
| ~                 | 
+---------------------+
1 row in set (0.00 sec)
mysql> prepare stmt from "select encode("abc", ?)";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'abc", ?)"' at line 1
mysql>

Posted by developer:
 
ENCODE function has been removed from MySQL, so this feature request is no longer applicable.