MySQL Bugs: #85588: MySQL does not detect pattern when using UNHEX function

Bug #85588	MySQL does not detect pattern when using UNHEX function
Submitted:	23 Mar 2017 4:23	Modified:	30 Jun 2017 10:25
Reporter:	Juan Arruti	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	Any, 5.7.17, 5.6.35	OS:	Any
Assigned to:	Martin Hansson	CPU Architecture:	Any

Description:
When using rlike operator to detect characters with hex values between 80 and FF, it does not work when converting a binary string that starts with hexadecimal '00' using unhex function.

How to repeat:
-- First query returns 1 because expresion matches with pattern.

mysql> set @text := unhex('149D5554'); 
Query OK, 0 rows affected (0.00 sec)

mysql> select @text, hex(@text); 
+-------+------------+
| @text | hex(@text) |
+-------+------------+
| ?UT  | 149D5554   |
+-------+------------+
1 row in set (0.00 sec)

mysql> select ( CONVERT(@text USING BINARY) RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')) "Match";
+-------+
| Match |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

-- Second query expresion starts with 00 and does not return a match.

mysql> set @text := unhex('00149D5554'); 
Query OK, 0 rows affected (0.00 sec)

mysql> select @text, hex(@text); 
+-------+------------+
| @text | hex(@text) |
+-------+------------+
|  ?UT | 00149D5554 |
+-------+------------+
1 row in set (0.00 sec)

mysql> select ( CONVERT(@text USING BINARY) RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')) "Match";
+-------+
| Match |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

Although '00' hex string is out of range from pattern 80 and FF, following strings of the expression should match with this pattern. For example, It works well with 01 hex string:

mysql> set @text := unhex('01149D5554'); 
Query OK, 0 rows affected (0.00 sec)

mysql> select @text, hex(@text); 
+-------+------------+
| @text | hex(@text) |
+-------+------------+
| ?UT | 01149D5554 |
+-------+------------+
1 row in set (0.00 sec)

mysql> select ( CONVERT(@text USING BINARY) RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')) "Match";
+-------+
| Match |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

Hello Juan Arruti,

Thank you for the report and test case.

Thanks,
Umesh

Contribution submitted via Github - Bug #85588: Regular expressions work incorrectly for strings having 00 byte (*) Contribution by Oleg Smirnov (Github Olernov, mysql-server/pull/146#issuecomment-305764865): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_123537234.txt (text/plain), 7.18 KiB.

Contribution submitted via Github - Bug #85588: When using rlike operator to detect characters with hex v… (*) Contribution by Oleg Smirnov (Github Olernov, mysql-server/pull/147#issuecomment-305838215): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_123738494.txt (text/plain), 6.96 KiB.

Thank you very much for your contribution. We will evaluate it for inclusion in a future milestone.

Note that we are changing the regular expression library in the next major version. The new library may not use BINARY as its internal encoding, hence the construction using CONVERT( ... UNHEX(...) USING BINARY ) will no longer work, as this circumvents character set conversion.

Hello Juan,
while we are very thankful for the work you have put in, we have decided not to include this patch. There are two reasons: Firstly, we only include critical security patches in versions as old as 5.6. Secondly, this bug is not present in the new regular expression library that we will include in the next major version.

That said, thank you very much for bringing this issue to our attention!

Regards,
Martin Hansson

Hello Martin,
Just for your attention: first patch submitted at June 2 was for version 5.7 while the next one was for version 5.6.
You may also check https://bugs.mysql.com/bug.php?id=68153. It seems to be the same issue, so maybe it's reasonable to change that bug status too.