| Bug #76723 | A query digest sometimes contain backticks and sometimes not depending on CS | ||
|---|---|---|---|
| Submitted: | 16 Apr 2015 14:47 | Modified: | 13 May 2015 15:36 |
| Reporter: | Kristofer Pettersson | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Security: Firewall | Severity: | S3 (Non-critical) |
| Version: | 5.6.24 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 May 2015 15:36]
Paul DuBois
Noted in 5.6.26, 5.7.8, 5.8.0 changelogs. Identifiers in normalized statements were sometimes quoted and sometimes not, an inconsistency that caused matching failure for statement digests and digest texts. This caused problems for MySQL Enterprise Firewall and for Performance Schema aggregation by digest. Identifiers now are quoted consistently.
Description: It appears statement normalization introduces backticks when using multi-byte character sets (makes sense) but otherwise does not. How to repeat: 1. User trains their firewall using a character set such as latin1 2. Later decides to switch their connection/db to utf8 3. None of their firewall rules work anymore and they need to retrain. mysql> SET NAMES utf8; SELECT normalize_statement("SELECT field_name FROM table_name WHERE field_name=1") AS normalized_rule; Query OK, 0 rows affected (0.00 sec) +-------------------------------------------------------------+ | normalized_rule | +-------------------------------------------------------------+ | SELECT `field_name` FROM TABLE_NAME WHERE `field_name` = ? | +-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SET NAMES latin1; SELECT normalize_statement("SELECT field_name FROM table_name WHERE field_name=1") AS normalized_rule; Query OK, 0 rows affected (0.00 sec) +---------------------------------------------------------+ | normalized_rule | +---------------------------------------------------------+ | SELECT field_name FROM TABLE_NAME WHERE field_name = ? | +---------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: If possible, it's probably best if backticks are used consistently for all character sets.