wpdb::db_connect() does not connect to IPv6 address of mysql server

[fierevere]

There is currently no way to make WordPress to connect to MySQL server using IPv6 address

<?php
define('DB_HOST', '::1');

Error establishing a database connection

in brackets:

<?php
define('DB_HOST', '[::1]');

Error establishing a database connection

the only way it works by defining a hostname in /etc/hosts and then connecting to it, but its not available option on shared hosting when you dont have control over files in /etc/

quoting @sergeybiryukov :

wpdb::db_connect() appears to treat everything after : as a port or socket:
https://core.trac.wordpress.org/browser/tags/4.8.1/src/wp-includes/wp-db.php?marks=1517-1535#L1504

[Ipstenu]

#41721 was marked as a duplicate.

[Ipstenu]

From #41721

Hello,
I am trying to connect to a remote database using IPv6. I have configured the MySQL database bind address to that of the IPv6 address and created a database and issued all the required permissions.
When I go to install WordPress, I enter the IPv6 address and WordPress returns the following message "Error establishing a database connection". I have made sure my MySQL is working by using MySQL Client and it connects to the database without any problems.
I then did a little more digging and found that if I change the database bind address to a IPv4 address and configure the correct permissions, WordPress will install using an IPv4 address.
WordPress will not work with IPv6 address.
Thanks.

[schlessera]

Fix for parsing both IPv4 & IPv6 hosts

[schlessera]

I've provided a first patch that changes the way the host is parsed. Now, DB_HOST values like ::1 or [::1]:3306 are correctly parsed and forwarded.

I couldn't find any prior tests that check the DB_HOST values, so not sure how this could be properly tested. I could write some unit tests for the wpdb::parse_db_host() method, but without more specific data on what is acceptable to mysqli (which I couldn't find yet), I'm not sure that will tell us much.

Note: mysqli_real_connect() seems to require the IPv6 hosts to always be enclosed in square brackets, at least on my system (PHP 7.0.20 + MariaDB 10.2.6).

[birgire]

There seems to be some inconsistency how IPv6 addresses are handled by mysqlnd, that's a part of the PHP distribution since 5.3.0. (​http://php.net/manual/en/mysqlinfo.library.choosing.php ) and libmysqlclient, according the the comments here:

​https://bugs.php.net/bug.php?id=67563

[2015-04-18 13:48 UTC] bobe at webnaute dot net

There is a real problem here because mysqlnd wants the IPv6 address to be enclosed 
in brackets (illogical since mysqli_connect() or PDO wants a host, not a url).

But when using libmysqlclient, these extensions wants the IPv6 address NOT to be enclosed in brackets.

 [2015-04-18 14:30 UTC] bobe at webnaute dot net

Here is some tests:

PDO mysql + mysqlnd:
[::1] => ok
::1 => getaddrinfo failed
mysqli_connect() + mysqlnd:
[::1] => ok
::1 => getaddrinfo failed
mysql_connect() + mysqlnd:
[::1] => Failed to parse IPv6 address "[:3306"
::1 => No such file or directory

PDO mysql + libmysqlclient:
[::1] => SQLSTATE[HY000] [2005] Unknown MySQL server host '[::1]'
::1 => ok
mysqli_connect() + libmysqlclient:
[::1] => (HY000/2005): Unknown MySQL server host '[::1]'
::1 => ok
mysql_connect() + libmysqlclient:
[::1] => Unknown MySQL server host '[' (2)
::1 => Can't connect to local MySQL server through socket '1'

This would need to be verified for mysqli_real_connect().

Wonder if this should be handled by WordPress (lib-detecting) or if it should be up to the user to provide the correct ipv6 form based on the underlying lib? I think the latter would be confusing to users.

[schlessera]

Added detection for mysql drriver, as mysqlnd needs square brackets and the other doesn't

[schlessera]

Thanks, @birgire, I added detection for the mysqlnd driver and only conditionally add the square brackets as needed.

[birgire]

Here's a first draft for db host string examples:

// db hosts and the expected host, port, socket 
$data = array(
        // db_host                                              // host                                         //port          //socket
        ''                                              => array( null,                                         null,           null                    ),
        ':3306'                                         => array( null,                                         '3306',         null                    ),
        ':/tmp/mysql.sock'                              => array( null,                                         null,          '/tmp/mysql.sock'        ),
        '127.0.0.1'                                     => array( '127.0.0.1',                                  null,           null                    ),
        '127.0.0.1:3306'                                => array( '127.0.0.1',                                  '3306',         null                    ),
        'example.com'                                   => array( 'example.com',                                null,           null                    ),
        'example.com:3306'                              => array( 'example.com',                                '3306',         null                    ),
        'localhost'                                     => array( 'localhost',                                  null,           null                    ),
        'localhost:/tmp/mysql.sock'                     => array( 'localhost',                                  null,           '/tmp/mysql.sock'       ),
        '0000:0000:0000:0000:0000:0000:0000:0001'       => array( '[0000:0000:0000:0000:0000:0000:0000:0001]',  null,           null                    ),
        '::1'                                           => array( '[::1]',                                      null,           null                    ),
        '[::1]'                                         => array( '[::1]',                                      null,           null                    ),
        '[::1]:3306'                                    => array( '[::1]',                                      '3306',         null                    ),
        '2001:0db8:0000:0000:0000:ff00:0042:8329'       => array( '[2001:0db8:0000:0000:0000:ff00:0042:8329]',  null,           null                    ),
        '2001:db8:0:0:0:ff00:42:8329'                   => array( '[2001:db8:0:0:0:ff00:42:8329]',              null,           null                    ),
        '2001:db8::ff00:42:8329'                        => array( '[2001:db8::ff00:42:8329]',                   null,           null                    ),
);

Some host string examples come from:

• ​https://en.wikipedia.org/wiki/IPv6#Addressing

• ​https://dev.mysql.com/doc/apis-php/en/apis-php-function.mysql-connect.html

Here we assume the mysql native driver.

If we run it through:

foreach( (array) $data as $db_host => $expected ) {
	$actual = $wpdb->parse_db_host( $db_host );
	$this->assertEquals( $expected, $actual );
}
		

we get failed tests for the db host strings: ':3306', ':/tmp/mysql.sock' and 'localhost:/tmp/mysql.sock'

I wonder if the brackets should be handled outside of the parse_db_host() function? That way the output would be more predictable, as it wouldn't depend on the library. It would also make the above testing easier.

[birgire]

Regarding the IPv4 case, it looks like we could parse ':3306', ':/tmp/mysql.sock' and 'localhost:/tmp/mysql.sock' with:

$pattern = '#^(?<host>[^:/]*)(?::(?<port>[\d]+))?(?::(?<socket>/.+))?#'; 

instead of

$pattern = '/^(?:\[)?(?<host>[^:^\/]+)(?::(?<port>[\d]+))?(?:\/(?<socket>.+))?/'; 

but we might look into that further. The above tests run successfully through this pattern.

[schlessera]

Moved bracket logic out of parsing, added unit test for parsing and modified regexes to fit all tested host strings.

[schlessera]

Thanks for the feedback, @birgire .

I've added unit tests, and moved the bracket logic outside of the parsing method.

I've slightly tweaked your IPv4 pattern to not assume the socket starts with a /, as it might also be a relative path or a named pipe.

[fierevere]

Attachment 41722.3.diff​ added

works nicely for me on 4.8.1-multisite, with or without brackets for DB_HOST

PHP 7.1.9 mysqlnd-mysqli

[birgire]

Thanks for the update @schlessera

Glad to hear it worked for you @fierevere

Just few notes:

• If we want to take this in smaller steps, we could keep the same non-ipv6 code, to be absolutely sure that we're not exluding anything with the new patch.
• Would it make sense and be helpful for the developers reading the code, to match the most used db host strings, like localhost, 127.0.0.1 etc, before parsing with regex?

Here's a list of db host strings from the ​Codex, maybe helpful in tests?:

Hosting Company					DB_HOST Value Guess
1and1						db12345678
A2 Hosting					localhost
AN Hosting					localhost
Aruba.it					localhost or real IP provided with activation mail.
A Small Orange					localhost
AT&T						xxxxxxxx.carrierzone.com full server name found in PHP MyAdmin.
BlueHost					localhost
DreamHost					mysql.example.com
GoDaddy - Shared and 4GH Hosting		In the Databases menu go to MySQL. To the right of the database name click on Actions and Details. The hostname is at the bottom of the window.
GoDaddy - cPanel Hosting			localhost
GoDaddy - Plesk Hosting				Use the IP address shown in the Databases Section in Plesk. Do not include :3306
HostGator					localhost
ICDSoft						localhost:/tmp/mysql5.sock
Infomaniak (old admin)				mysql.yourdomain
Infomaniak					In the Dashboard of your Web Hosting, go to Databases. The host server is at the top of the window (example: xxxx.myd.sharedbox.com)
InMotion Hosting				localhost
iPage						username.ipagemysql.com
IPower						username.ipowermysql.com
Laughing Squid					localhost
MediaTemple Grid				internal-db.s00000.gridserver.com - (Replace "00000" with the actual site number)
MediaTemple DV					localhost
MegaHost					localhost
NearlyFreeSpeech.Net				username.db
NetworkSolutions				mysqlv5
one.com						example.com.mysql
pair Networks					dbnnnx.pair.com
QTH.com						localhost
Rackspace Cloud					localhost for unmanaged servers, variable for Cloud Sites like mysqlXY-AB.wcN.dfQ.stabletransit.com where X,Y,A,B,N,Q are variables
SysFix.eu Power Hosting				datapower.sysfix.eu
Site5						localhost
SiteGround					localhost
Vevida.com					mysql.example.com, where example.com is your own domain name
Yahoo						mysql
Hosts with cPanel				localhost
Hosts with Plesk				localhost
Hosts with DirectAdmin				localhost
Tophost.it					sql.your-domain-name.it

[pento]

In 41629:

Database: Add support for connecting to IPv6 hosts

IPv4 addresses are scarce, overworked, and underpaid. They're ready to retire, but we just won't let them go. If you care about their wellbeing, switch to IPv6 today.

Props schlessera, birgire.
Fixes #41722.

[markjaquith]

This broke socket connections.

My DB_HOST looks like this:

define( 'DB_HOST', ':/Applications/MAMP/tmp/mysql/mysql.sock' );

What happens is that $port ends up being an empty string, which is not a valid value.

Warning: mysqli_real_connect() expects parameter 6 to be integer, string given in /Users/mark/svn/wp-dev/trunk/src/wp-includes/wp-db.php on line 1498

And thus the connection fails.

Warning: mysqli_get_server_info(): invalid object or resource mysqli in /Users/mark/svn/wp-dev/trunk/src/wp-includes/wp-db.php on line 3329

$port should remain null, if no actual value is extracted.

[birgire]

Thanks for the heads up @markjaquith

In 41722.5.diff​

• Replace assertEquals() with assertSame()
• Added the same empty check for $host and $socket, as done in 41722-sockets-fix.diff​ for the $port.

In the first test, within parse_db_host_data_provider(), the empty string DB_HOST is expected to be parsed into a null $host. So this patch assumes that output for empty $host. It would be good to have others verifying that this is valid.

Update: we then could consider e.g.:

foreach ( array( 'host', 'port', 'socket' ) as $component ) {
    if ( array_key_exists( $component, $matches ) ) {
        $$component = ! empty( $matches[$component] ) ? $matches[$component] : null;
    }
}

[pento]

In 41820:

Database: When parsing the host, leave the port and socket as null if they're not defined.

This fixes a change in behaviour introduced by [41629].

The host is set to an empty string when it isn't defined, this continues existing behaviour. In particular, the mysqli library treats a null host as being the same as localhost, which is not always the intended behaviour.

Props birgire, markjaquith, pento.
Fixes #41722.