3

Changing user auth plugin in MySQL to 'sha256_password':

ALTER USER 'user'@'N.N.N.N' IDENTIFIED WITH 'sha256_password' BY 'password';

test.php:

#!/usr/bin/env php
<?php
$my = new mysqli('M.M.M.M', 'user', 'password');
echo "OK\n";
$my->close();
?>

Doesn't work with 'sha256_password':

php test.php
Warning: mysqli::__construct(): (HY000/1045): Access denied for user 'user'@'hostname' (using password: YES) in /home/user/test.php on line 3

Changing user auth plugin back to 'mysql_native_password':

ALTER USER 'user'@'N.N.N.N' IDENTIFIED WITH 'mysql_native_password' BY 'password';

After changing auth plugin back to 'mysql_native_password' connection works:

php test.php
OK

Software versions:

php --version
PHP 7.2.25-1+ubuntu16.04.1+deb.sury.org+1 (cli) (built: Nov 28 2019 07:41:59) ( NTS )

dpkg -l | grep libmysqlclient20
ii  libmysqlclient20:amd64                5.7.28-0ubuntu0.16.04.2

mysqld -V
mysqld  Ver 5.7.26-29-57 for debian-linux-gnu on x86_64 (Percona XtraDB Cluster (GPL), Release rel29, Revision 03540a3, WSREP version 31.37, wsrep_31.37)

Any ideas how to fix this?

UPDATE :

I also checked that it works with python MySQLdb connector:

import MySQLdb
con = MySQLdb.connect(host='127.0.0.1',user='user', passwd='password')
cur = con.cursor()
cur.execute('select 1')
print(cur.fetchall())
con.close()
Selivanov Pavel
  • 338
  • 2
  • 11
  • 25
  • 1
    Not sure the answer but the issue appears to be the same as https://stackoverflow.com/questions/54177511/how-do-you-connect-to-mysql-using-phps-mysqli-when-using-sha256-password-acces (`mysqli` needs to be instructred to use the `sha_256`.) – user3783243 Dec 19 '19 at 21:06
  • @user3783243 Do you know how to instruct mysqli to use sha_256? I can't find it in that question or in mysqli docs. – Selivanov Pavel Dec 19 '19 at 21:29
  • Although, in your case you see a message saying that MySQL couldn't log you in. This usually happens when you made a mistake in the username, password or didn't grant the privileges. If it was PHP fault you should see a message saying that the client can't recognize the authentication method – Dharman Dec 19 '19 at 21:35
  • @Dharman I thought so, but the same php code works fine with the same user and password, if auth plugin is 'mysql_native_hashing'. – Selivanov Pavel Dec 19 '19 at 21:38
  • I actually don't know an answer to your question. I have found this useful links: https://www.php.net/manual/en/mysqli.options.php and https://dev.mysql.com/doc/refman/5.6/en/sha256-pluggable-authentication.html – Dharman Dec 19 '19 at 21:48
  • @Dharman `#ifdef MYSQLND_HAVE_SSL` seems that it works only with SSL connections, while mysql client works with sha256_password without SSL as well. – Selivanov Pavel Dec 19 '19 at 22:13
  • Here is a [tutorial](http://blog.ulf-wendel.de/2012/mysql-5-6-sha256-secure-password-support-for-php-mysqlnd/) how to use it. Unfortunately I don't have this mysqlnd plugin installed, so I can't reproduce your issue. – Dharman Dec 19 '19 at 22:26
  • @SelivanovPavel Nope, but I've bountied the other question. – user3783243 Dec 19 '19 at 22:30
  • @Dharman I am using mysqlnd( phpinfo() says "mysqlnd => enabled" ), and native mysql client works fine with my sha256_password auth, but php doesn't. – Selivanov Pavel Dec 20 '19 at 12:45
  • @user3783243 Thanks, that's nice of you. – Selivanov Pavel Dec 20 '19 at 12:45
  • @SelivanovPavel Note that `#ifdef MYSQLND_HAVE_SSL` only checks whether mysqlnd has SSL support itself, not whether an SSL connection is actually used. – NikiC Dec 30 '19 at 09:12

1 Answers1

0

So it looks that PHP 7.2.25(version from 21 Nov 2019) doesn't support MySQL 'sha256_password' authorization for connections without SSL.

Selivanov Pavel
  • 338
  • 2
  • 11
  • 25