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:
None 
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

[30 Jun 2015 16:23] Paul Dubois
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.
[1 Jul 2015 8:26] Umesh Shastry
Hello Paul,

Thank you for the report.
Confirmed with 5.6.27/5.7.8 builds.

Thanks,
Umesh
[1 Jul 2015 8:27] Umesh Shastry
// 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>