Bug #15756 incorrect ip address matching in ACL due to use of latin1 collation
Submitted: 14 Dec 2005 19:25 Modified: 4 Mar 2006 1:19
Reporter: Deomid Ryabkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.14 ... 4.1.17-BK OS:Linux (Linux, Windows, FreeBSD)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[14 Dec 2005 19:25] Deomid Ryabkov
Description:
whan matching an ACL, some addresses are consideret equal while they are not.
the problem arises from the use of latin1 charset in hostname_cache:

hostname_cache=new hash_filo(HOST_CACHE_SIZE, offset,
                                     sizeof(struct in_addr),NULL,
                                     (hash_free_key) free,
                                     &my_charset_latin1)

this leads to (my own debug statements, but should be clear enough):

   hostname.cc:   108: | | | info: ZZ entry 0xebf24450 added to cache: hux.rbc.ru
and later:
   hostname.cc:   175: | | | info: ZZ entry 0xe8f24450 found in cache: hux.rbc.ru, 80.68.242.235

essentially, ip addresses 0xebf24450 (80.68.242.235) and 0xe8f24450 (80.68.242.232) are considered equal due to the fact that 0xeb and 0xe8 have equal sort order in latin1.

this issue is bound also has security implications...

How to repeat:
should be obvious

Suggested fix:
do not use collation when comparing in_addr-type values.
[17 Dec 2005 18:08] Valeriy Kravchuk
Thank you for a bug report. This weird bug was the reason for some really misterious reports like bug #13659 (I'll mark it as a duplicate of this one, although it contains some interesting details). But you not only reported about the bug, but pinpointed a reason for it. This code:

  if (!(hostname_cache=new hash_filo(HOST_CACHE_SIZE, offset,
                                     sizeof(struct in_addr),NULL,
                                     (hash_free_key) free,
                                     &my_charset_latin1)))

is still in the latest 4.1.17-BK (ChangeSet@1.2461, 2005-12-15 18:48:08+03:00), sql/hostname.cc, line 61. And, I believe, all the versions after 4.1.14... 

The problem is obvious from your description and code review, but here is the set of steps to repeat it for anybody:

1. I setuped a primary machine, say, 192.168.0.1, and installed MySQL 4.1.x (x >=14) on it.

2. I connected as root and executed:

mysql> grant all on test.* to `user1`@`redhat9`;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on test.* to `user1`@`redhat9c`;
Query OK, 0 rows affected (0.00 sec)

redhat9 is the name (in local hosts file) for machine with IP = 192.168.0.235,
redhat9c is the name (in local hosts file) for machine with IP = 192.168.0.232, accordingly.

3. Then I setuped redhat9 and redhat9c with the appropriate IP addresses, and Installed MySQL clients (4.1.15, but that does not metter) on them.

4. Then from each of the machines I connected to MySQL server at 192.168.0.1 as user1 without password.

5. On main machine, 192.168.0.1, I've got:

mysql> show processlist;
+----+-------+----------------+------+---------+------+------------------+------------------+
| Id | User  | Host           | db   | Command | Time | State            | Info            |
+----+-------+----------------+------+---------+------+------------------+------------------+
| 13 | root  | localhost:3710 | test | Query   |    0 | NULL             | show processlist |
| 24 | user1 | redhat9c:32789 | NULL | Connect |  101 | Reading from net | NULL            |
| 25 | user1 | redhat9c:32788 | NULL | Connect |    9 | Reading from net | NULL            |
+----+-------+----------------+------+---------+------+------------------+------------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

But:

C:\work>netstat -a
...
  TCP    creator:3306           redhat9c:32789         ESTABLISHED
  TCP    creator:3306           redhat9:32788          ESTABLISHED
...

So, it proves that there is a bug! And, surely, it influences not only SHOW PROCESSLIST!
[24 Jan 2006 9:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1554
[24 Jan 2006 10:02] Ramil Kalimullin
Deomid, thanks a lot for your help! Nice catch!
[2 Mar 2006 13:24] Alexander Barkov
The patch looks ok to push
[3 Mar 2006 8:32] Ramil Kalimullin
fixed in 4.1.19
[4 Mar 2006 1:19] Paul Dubois
Noted in 4.1.19 changelog.

Security improvement: In grant table comparisons, improper use
of a <literal>latin1</literal> collation caused some hostname
matches to be true that should have been false. Thanks to
Deomid Ryabkov for finding this bug and proposing a solution.
(Bug #15756)
[31 Mar 2006 20:09] Marc Bejarano
the changelog should also talk about the effect this has on "show processlist" (and therefore mysql query browser).  see bug 18557.
[16 Mar 2009 7:19] Mikiya Okuno
While no relevant bug is filed for MySQL 5.0, it appears that the bug is fixed on MySQL 5.0.20.