| Bug #77567 | Firewall SP definitions are sensitive to sql_mode setting | ||
|---|---|---|---|
| Submitted: | 30 Jun 2015 16:23 | Modified: | 1 Jul 2015 8:26 |
| Reporter: | Paul DuBois | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Security: Firewall | Severity: | S3 (Non-critical) |
| Version: | 5.6.24, 5.6.27, 5.7.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Jul 2015 8:26]
MySQL Verification Team
Hello Paul, Thank you for the report. Confirmed with 5.6.27/5.7.8 builds. Thanks, Umesh
[1 Jul 2015 8:27]
MySQL Verification Team
// 5.6.27
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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.
mysql>
mysql> SET GLOBAL sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock < share/linux_install_firewall.sql
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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.
mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING');
ERROR 1054 (42S22): Unknown column 'RECORDING' in 'field list'
mysql>
// 5.7.8
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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.
mysql> SET GLOBAL sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8: bin/mysql -uroot -S /tmp/mysql_ushastry.sock < share/linux_install_firewall.sql
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.8-rc-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, 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.
mysql>
mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING');
ERROR 1054 (42S22): Unknown column 'RECORDING' in 'field list'
mysql>
Description: MySQL Enterprise Firewall has two installation scripts: linux_install_firewall.sql win_install_firewall.sql Both define stored procedures using double quote characters to quote strings. Example: IF arg_mode = "RECORDING" THEN SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE userhost = arg_userhost; END IF; If sql_mode includes ANSI_QUOTES when the installation script is run, double quoted strings are interpreted at stored procedure invocation time as identifiers, and the procedure fails: mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING'); ERROR 1054 (42S22): Unknown column 'RECORDING' in 'field list' How to repeat: Set the server sql_mode to 'ANSI_QUOTES'. SET GLOBAL sql_mode = 'ANSI_QUOTES'; Run the firewall invocation script. Example: mysql -u root -p mysql < linux_install_firewall.sql From mysql, invoke one of the stored procedures. CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING'); Suggested fix: Quote all strings using single quote.