MySQL Bugs: #70976: It is possible to connect to server using IP 127.0.0.10

Bug #70976	It is possible to connect to server using IP 127.0.0.10
Submitted:	21 Nov 2013 19:53	Modified:	25 Nov 2013 23:55
Reporter:	Olivier de Sylva	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	Ver 14.14 Distrib 5.5.34	OS:	Linux (Ubuntu 12.04)
Assigned to:		CPU Architecture:	Any

Description:
It is possible to connect to address 127.0.0.1 or 127.0.0.10.
The last 0 seems to get truncated.

On the other hand, it is not possible to connect using the address 192.168.56.10.
It will be translated as 192.168.56.1.
Trying with address 192.168.56.100 will try to connect to 192.168.56.100 as expected.

How to repeat:
Using MySQL Workbench, create a connection through SSH tunnel.
Fill all the parameters correctly aside MySQL Hostname field.
Far that one, use IP address 127.0.0.10 instead.
Test the connection and it will show as successful.

Connect to IP address 127.0.0.10 successful

Attachment: Screen Shot 2013-11-21 at 19.51.31.png (image/png, text), 29.25 KiB.

Last 0 of IP address is truncated

Attachment: Screen Shot 2013-11-21 at 19.58.24.png (image/png, text), 30.41 KiB.

Also I can connect to local servers (all I have: 5.0.96, 5.1.71, 5.5.34 and 5.6.14) specifying host as "127.0.0.10" on Windows (7/64).

Peter
(non a MySQL/Oracle person).

Thank you for the report.

Please provide output of SELECT user, host FROM mysql.user

@Sveta .. why does that matter?  This is not at all about authentication.  Did you try it yourself? Like this (using MySQL 5.0 for the reason that this one listens on prot 3306, but it is the same with MySQLK 5.1, 5.5 and 5.6)

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --host 127.0.0.10 -uroot -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.96-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2011, 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>

And my hosts file is completely empty - it reads

# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

# localhost name resolution is handled within DNS itself.
#	127.0.0.1       localhost
#	::1             localhost

Also "127.0.0.1" and "127.0.0.100" connects to "localhost" as in

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --host 127.0.0.100 -uroot -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.96-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2011, 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> exit
Bye

.. but ""127.0.0.500" does not:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --host 127.0.0.500 -uroot -p
Enter password: ********
ERROR 2005 (HY000): Unknown MySQL server host '127.0.0.500' (11004)

C:\Program Files\MySQL\MySQL Server 5.0\bin>

This is the same in command line client and programs compiled with the C-API.

And for completenes you will get

SELECT USER, HOST FROM mysql.user

/*
user    host       
------  -----------
peter   localhost  
root    localhost  
sup     localhost  
*/

-- Frankly I think you are completely confused about *connect* and *authentication*.  There is NOTHING in this discussion that has the SLIGHTEST with authentication to do.

Peter,

thank you for the feedback. You are absolutely correct: all these 127.0.0.1-127.255.255.254 (http://en.wikipedia.org/wiki/Localhost#Name_resolution) numbers are usually synonyms for localhost. This is not a bug.

So 192.168.56.10 and 192.168.56.1 are localhost too then, but 192.168.56.100 isn't.

Ok, it makes sense now. I got my head around it. Thanks.

Olivier, also, do not mistake the *destination* ip you provide to a client to connect to a mysql instance with the *source* client ip, mysqld uses to lookup in the grant tables for the authentication.
The source ip is taken from the network connection the client has made.

For the same reason, Sveta, it doesn't matter how the client gets to the instance, the authentication is made using the tuple (user,host,password),
while 'user' and 'password' are provided by the user,
'host' has nothing to do with any parameter provided by the user, it is taken* from the network packet (specifically layer 3 envelope).

* from the packet is taken the 'ip' then (if name resolution is enabled by omitting --skip-name-resolve option) also a reverse lookup is done.