0

I can successfully connect over a secure connection from the web server's cli but get an error message when testing connection through php script.

mysql status from the web server cli

mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper

Connection id:      9
Current database:   
Current user:       test@db.test.com
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.21-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:   10
Connection:     0.0.0.0 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:       3306
Uptime:         3 days 4 hours 53 min 35 sec

Threads: 1  Questions: 17  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 26  Queries per second avg: 0.000

SSL variables from web server cli

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+

Grants from web server cli

+-------------------------------------------------------------+
| Grants for test@0.0.0.0                             |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'0.0.0.0'              |
| GRANT SELECT ON `testDB`.* TO 'test'@'0.0.0.0' |
+-------------------------------------------------------------+

Character set using utf8mb4 from web server cli

+--------------------------+------------------------+
| Variable_name            | Value                  |
+--------------------------+------------------------+
| character_set_client     | utf8mb4                |
| character_set_connection | utf8mb4                |
| character_set_database   | utf8mb4                |
| character_set_filesystem | binary                 |
| character_set_results    | utf8mb4                |
| character_set_server     | utf8mb4                |
| character_set_system     | utf8                   |
| collation_connection     | utf8mb4_unicode_520_ci |
| collation_database       | utf8mb4_unicode_520_ci |
| collation_server         | utf8mb4_unicode_520_ci |
+--------------------------+------------------------+

User permissions on remote database

+------------------+-------------+-----------------------+----------+
| user             | host        | plugin                | ssl_type |
+------------------+-------------+-----------------------+----------+
| test             | 0.0.0.0     | sha256_password       | X509     |
+------------------+-------------+-----------------------+----------+

Certificate permissions on web server

apache@web:~$ ls -al /etc/mysql-client
total 20
drwxr-xr-x  2 root root 4096 Mar 24 02:30 .
drwxr-xr-x 99 root root 4096 Mar 30 20:04 ..
-r-xr-xr-x  1 root root 1107 Mar 24 02:30 ca.pem
-r-xr-xr-x  1 root root 1107 Mar 24 02:30 client-cert.pem
-r-xr-xr-x  1 root root 1679 Mar 24 02:30 client-key.pem

PHP Test Script on web server

$dsn = 'mysql:host=db.test.com;port=3306;dbname=testDB;charset=utf8mb4';
$dbuser = 'test';
$dbpass = 'sha256-hash';

$dboptions = array (
    PDO::MYSQL_ATTR_SSL_KEY    =>'/etc/mysql-client/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT   =>'/etc/mysql-client/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA     =>'/etc/mysql-client/ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false 
);

    try {
        $conn = new PDO($dsn,$dbuser,$dbpass,$dboptions);
        if($conn){
                echo "Connection established";
        }
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }

Successful network connection from web server to database server

Connection to 0.0.0.0 3306 port [tcp/mysql] succeeded!

Error Message from web server

apache@web:~$ php -f test.php
Connection failed: SQLSTATE[HY000] [2002]

The few questions I have are:

  • Should the database password used in the php script be set to the plain-text password string or the sha256 hash? I have tried both and neither work.

  • Since the certificates are in /etc with global permissions and the php script has explicit file paths, is creating a database user account on the web server necessary? For example, my "test" user on the web server is only really used to test the mysql connection from command line. I don't see how apache is dependent on the test user.

  • Any php.ini settings I should take a look at? The only one I have tested was changing "open_basedir" to include the /etc/mysql-client directory but that didn't do anything either.

  • Does anybody know if Bug #77595 has been fixed?

  • Has anyone actually gotten sha256_password authentication over SSL using PHP to actually work?

UPDATE #1

Supposedly Bug #77595 has been fixed in php7.2.3 so I fired up a new VM with ubuntu18.04 which has php7.2.3 as the default package, still no luck. The only error string that seemed useful from var_dump was

"PDO::__construct(): SSL operation failed with code 1. OpenSSL Error 
messages: error:0906D06C:PEM routines:PEM_read_bio:no start line"

mysql login from command line still works with the same certificates it is just php doesn't like them. I scp'd them from the mysql server so the chance of formatting error is unlikely.

  • Have you run var_dump($e) in your catch block? Commonly there is additional information available. – Gerald Schneider Apr 5 '18 at 6:42
0

I don't have a clear answer for this problem. My assumption is that something is going on with yaSSL. I ended up using Percona instead which implements openSSL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.