Bug #60166 SUBSTRING_INDEX() results "omit" character when used inside LOWER()

• Submitted: 18 Feb 2011 10:54
• Modified: 22 Mar 2012 18:25
• Reporter: G Qyy
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.0.91, 5.1.36, 5.1.54, 5.5.8, 5.5.9
• OS: Any (Windows 7, Linux Ubuntu 9.10)
• CPU Architecture: Any
• Tags: LOWER, SUBSTRING_INDEX

[18 Feb 2011 10:54] G Qyy

Description:
Tested on :

* Windows Seven 64 bits pro / MySQL 5.1.36 Community 32 bits
* Windows Seven 64 bits pro / MySQL 5.5.8 Community 32 bits
* VM Ubuntu 9.10 32 bits (on VMWare Server 2) / MySQL 5.5.9 Community 32 bits

When using SUBSTRING_INDEX() inside LOWER(), and if an user-defined variables is use as first argument of the SUBSTRING_INDEX(), the result can change character of the returned string into a "space".

It's actually a possible security issue because SUBSTRING_INDEX() is classically used to get the user and the host part of an USER() result.

It can cause also a lot of trouble in use on e-mail addresses.

Here is an example :

mysql> SET @user_at_host = '[e-mail]';
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));
+------------------------------------------------+
| LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1)) |
+------------------------------------------------+
| mytinyhost-pc. ocal                            |
+------------------------------------------------+
1 row in set (0,00 sec)

How to repeat:
- Connect to your mysql server in command line:

$ mysql -u root -p

- Set your user defined variable:

mysql> SET @user_at_host = '[e-mail]';

- select the result as following:

mysql> SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));
+------------------------------------------------+
| LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1)) |
+------------------------------------------------+
| mytinyhost-pc. ocal                            |
+------------------------------------------------+
1 row in set (0,00 sec)

Suggested fix:
- Use:
  mysql> SELECT SUBSTRING_INDEX(LOWER(@user_at_host), '@', -1);
  instead of:
  mysql> SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));

- Avoid using of this buggy function (see the more than one year old bug #42404 - http://bugs.mysql.com/bug.php?id=42404), and:

- Use preg_* UDF from http://www.mysqludf.org/
  But for now,This solution is unavailable on Windows / Mysql 5.5.8 because of the 8 month old bug #45549 (http://bugs.mysql.com/bug.php?id=45549)

[18 Feb 2011 11:15] Valeriy Kravchuk

Thank you for the bug report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> create trigger tr1 after insert on userp for each row set @a = 1;
Query OK, 0 rows affected (0.20 sec)

mysql> SET @user_at_host = '[e-mail]';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1));
+------------------------------------------------+
| LOWER(SUBSTRING_INDEX(@user_at_host, '@', -1)) |
+------------------------------------------------+
| mytinyhost-pc. ocal                            |
+------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT SUBSTRING_INDEX(@user_at_host, '@', -1);
+-----------------------------------------+
| SUBSTRING_INDEX(@user_at_host, '@', -1) |
+-----------------------------------------+
| mytinyhost-PC.local                     |
+-----------------------------------------+
1 row in set (0.00 sec)

[22 Mar 2012 18:25] Paul DuBois

Noted in 5.6.5 changelog.

The result of SUBSTRING_INDEX() could be missing characters when used
as an argument to conversion functions such as LOWER().