Can't connect with PDO using ssl but mysqli with ssl works

Question

We set up mysql with SSL by creating the certificates, updating the my.cnf, creating users with right privileges and requiring ssl, restarting the service, and verified it works server side and client side (via mysql command line) by connecting remotely. I've also verified PDO works properly with the exact same setup but disabling the "require ssl" on the user account in mysql because it just fails silently and uses a non-ssl connection.

However when using the PHP application to connect it does not work using PDO forcing ssl but does work using mysqli with force ssl. I thought they used the same drivers and both should work fine. The error message I get is 'Failed connecting to database [SQLSTATE[28000] [1045] Access denied for user', but the user exists and this connection works with mysqli (force ssl) and the connection works for PDO only if I remove 'require ssl' from the user in mysql.

php 5.5.9 CentOS release 6.5 (Final) pdo_mysql 5.5.31

Let me know if I can provide any additional information. Below are connection examples,

//mysqli
$conn=mysqli_init();
mysqli_ssl_set($conn, $clientkey, $clientcert, $sharedca, NULL, NULL);
if (!mysqli_real_connect($conn, $host, $user, $pass, $db))
{
    die("Failed connecting to ssl mysql via mysqli");
}

$res = mysqli_query($conn, "SHOW STATUS like 'Ssl_cipher'");
print_r(mysqli_fetch_row($res));
mysqli_close($conn);

//pdo
$options = array_merge($options, array(
PDO::MYSQL_ATTR_SSL_KEY           => $sslkey,
PDO::MYSQL_ATTR_SSL_CERT          => $sslcert,
PDO::MYSQL_ATTR_SSL_CA            => $sslca,
));

try
{
    $pdo = new PDO("mysql:dbname={$db};host={$host}", $user, $pass, $options);  
}
catch( PDOException $e )
{
  die("Failed connecting");
}

Is there anything I should know about how to properly connect using SSL with PDO? Or am I forced to switch to mysqli because PDO support for SSL may be problematic?

Check this, it may help http://stackoverflow.com/questions/9738712/connect-to-remote-mysql-server-with-ssl-from-php — Rizwan Yahya, Apr 30 '14 at 13:44
Referenced this post already, the examples illustrate how I am already connecting and there's no proof of concept, just something pulled from docs likely. I am thinking that underneath it's not properly connecting via ssl at all which is why I'm getting blocked, maybe I will look at source and try something like Xdebug if possible. — fr332lanc3, May 01 '14 at 18:04
I know it's old but.. I am stuck with the exactly same issue ! Command line + mysqli work great but PDO returns "Access denied". Please, tell me that you found a solution and that you remembered it... — Delphine, Nov 04 '16 at 12:37
Also stuck and similar questions on SO remain unanswered. The answer below is not an answer. — a coder, Jun 06 '17 at 15:40

Desislav Kamenov · Answer 1 · 2019-10-09T07:46:50.987

The actual issue is server certificate CN validation (mismatch) but the error reported is PDOException: SQLSTATE[HY000] [2002]

There are numerous bugs in PHP logged for that issue like: 71845 71003 and Github PR

The solutions is this undocumented attribute available after these PHP version 5.6.22 (not sure), 7.0.18 (verified) and 7.1.15 (not sure)

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT

possible values: true, false default value: true

so your code should look like

$pdo = new PDO('mysql:host=XXXXXX;dbname=XXXXXX', 'XXXXXX', 'XXXXXX', array(
    PDO::MYSQL_ATTR_SSL_KEY    =>'/path/to/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT   =>'/path/to/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA     =>'/path/to/server-ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
)

);

score 0 · Answer 2 · edited Oct 09 '19 at 22:01

0

The following code will solve your problem

new PDO('mysql:host='.HOST.';dbname='.DBNAME.';charset=utf8', USER, PASSWORD,
    array(
            PDO::MYSQL_ATTR_SSL_KEY    =>'/mysqlsslcertificate1/client-key.pem',
            PDO::MYSQL_ATTR_SSL_CERT=>'/mysqlsslcertificate1/client-cert.pem',
            PDO::MYSQL_ATTR_SSL_CA    =>'/mysqlsslcertificate1/ca-cert.pem'
    )
);

edited Oct 09 '19 at 22:01

Dharman

30,962
25
85
135

answered Jan 25 '16 at 05:58

Elby

1,624
3
23
42

How is this different then the coding example supplied above? Looks identical...please clarify. – fr332lanc3 Feb 26 '17 at 19:50
@user3581488 We needs path of ssl pem file not just name of the file – Elby Feb 27 '17 at 11:50
Both of our coding examples require full paths, so mysqli and pdo otherwise neither solution works. So this changes nothing on the original post/code example supplied. – fr332lanc3 Feb 27 '17 at 21:46

Can't connect with PDO using ssl but mysqli with ssl works

2 Answers2

Linked