MySQL Bugs: #86712: Virtual columns allow deprecated des

Bug #86712	Virtual columns allow deprecated des_encrypt function to be used
Submitted:	15 Jun 2017 9:50	Modified:	6 Oct 2017 13:04
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I anticipate that des_encypt will be removed  in a new version as it is currently deprecated.

In which case, I expect it to create problems with virtual columns, as tables will depend on the function to exist.

How to repeat:
create table t1( a int not null primary key, b char(255) not null, c blob as (des_encrypt(b)));
insert into t1 (a,b) values (1, 'aaaa');
insert into t1 (a,b) values (2, 'aaaa');

SELECT * FROM t1;
+---+------+-----------+
| a | b    | c         |
+---+------+-----------+
| 1 | aaaa | ?*c??|?]     |
| 2 | aaaa | ?*c??|?]     |
+---+------+-----------+
2 rows in set (0.01 sec)

Suggested fix:
Virtual columns already implement a white list of permitted functions.  It would be nice to remove des_encrypt from the white list in MySQL 8.0.

Hello Morgan,

Thank you for the report.

Thanks,
Umesh

####
umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.2-dmr-linux-glibc2.12-x86_64: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.2-dmr-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use test
Database changed
root@localhost [test]> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test]> create table t1( a int not null primary key, b char(255) not null, c blob as (des_encrypt(b)));
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test]> insert into t1 (a,b) values (1, 'aaaa');
Query OK, 1 row affected, 1 warning (0.01 sec)

root@localhost [test]> insert into t1 (a,b) values (2, 'aaaa');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> select * from t1;
+---+------+-----------+
| a | b    | c         |
+---+------+-----------+
| 1 | aaaa | ▒*c▒▒|▒]     |
| 2 | aaaa | ▒*c▒▒|▒]     |
+---+------+-----------+
2 rows in set (0.00 sec)

root@localhost [test]> \! cat docs/INFO_SRC
commit: be1ac65b0b1dcb4603511db15ece72633b4fce1e
date: 2017-06-06 07:28:12 +0200
build-date: 2017-06-06 10:12:42 +0200
short: be1ac65
branch: mysql-8.0.2-dmr-release

MySQL source 8.0.2

root@localhost [test]>

Posted by developer:
 
Fixed in 8.0.4, 9.0.0.

Generated columns no longer permit deprecated functions in the
generation expression, to avoid problems when MySQL is upgraded to a
version in which the deprecated functions have been removed.