PHP MySQL over SSL. Peer certificate did not match
<p>I'm trying to use Google Cloud SQL over SSL from GCE(Google Compute Engine) instance. My problem is that I cannot connect to Cloud SQL instance over SSL.</p>

<p>mysql command works normally. I can connect to Cloud SQL instance with certification files.</p>

<pre><code>mysql -uroot -p -h [IP Address] --ssl-ca=/home/user/.cert/server-ca.pem --ssl-cert=/home/user/.cert/client-cert.pem  --ssl-key=/home/user/.cert/client-key.pem
</code></pre>

<p>However, I got warning and fatal error as followings when I access from PHP program.</p>

<pre><code>&lt;?php
$pdo = new PDO('mysql:host=[IP Address];dbname=testdb', 'root', 'test', array(
    PDO::MYSQL_ATTR_SSL_KEY    =&gt;'/home/user/.cert/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT=&gt;'/home/user/.cert/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA    =&gt;'/home/user/.cert/server-ca.pem'
    )
);
$stmt = $pdo-&gt;query("SHOW TABLES;");
$row = $stmt-&gt;fetch(PDO::FETCH_ASSOC);
print_r($row);
?&gt;　
</code></pre>

<p>　</p>

<pre><code>PHP Warning:  PDO::__construct(): Peer certificate CN=`[GCP project name]:[Cloud SQL instance name]' did not match expected CN=`[IP Address]' in /tmp/mysql.php on line 7
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] ' in /tmp/mysql.php on line 7
</code></pre>

<p>I got same error when I used mysqli.</p>

<pre><code>$mysqli = mysqli_init();
mysqli_options($mysqli, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

$mysqli-&gt;ssl_set('/home/user/.cert/client-key.pem',
                 '/home/user/.cert/client-cert.pem',
                 '/home/user/.cert/server-ca.pem',
                  NULL,NULL);

$mysqli-&gt;real_connect('[IP Address]', 'root', 'test', 'testdb', 3306, NULL, MYSQLI_CLIENT_SSL);
</code></pre>

<p>　</p>

<pre><code>PHP Warning:  mysqli::real_connect(): Peer certificate CN=`[GCP project name]:[Cloud SQL instance name]' did not match expected CN=`[IP Address]' in /tmp/mysql3.php on line 30
Warning: mysqli::real_connect(): (HY000/2002):  in /tmp/mysql3.php on line 30
</code></pre>

<p>This question looks be relevant to my case but there is no answer yet. <a href="https://stackoverflow.com/questions/34478265/ssl-self-signed-certifications-to-connect-with-mysql-with-php">SSL self-signed certifications to connect with Mysql with PHP</a></p>

<p>Does anyone know about solutions?</p>

<hr>

<p>Update 1</p>

<p>The bug is reported. <a href="https://bugs.php.net/bug.php?id=71003" rel="noreferrer">https://bugs.php.net/bug.php?id=71003</a></p>

<p>Very similar question here. <a href="https://stackoverflow.com/questions/29260464/google-cloud-sql-ssl-fails-peer-certificate-validation">Google Cloud SQL SSL fails peer certificate validation</a></p>

<p>My PHP version is 5.6.14. I will update to 5.6.16 to use MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT.</p>

<hr>

<p>Update 2 </p>

<p>Fixed it when I use mysqli</p>

<p>What I did are as followings:</p>

<p>1 I updated my PHP to 5.6.20</p>

<pre><code>sudo apt-get install php5
</code></pre>

<p>2 I put the <strong>MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT</strong> option like this.</p>

<pre><code>$mysqli-&gt;real_connect('[IP Address]', 'root', 'test', 'testdb', 3306, NULL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);
</code></pre>

<p>My application uses both mysqli and PDO for some reasons. I'm now looking for PDO's solution.</p>

<hr>

<p>Update 3</p>

<p>This bug report shows about PDO's case. Sounds not fixed yet. </p>

<p><a href="https://bugs.php.net/bug.php?id=71845" rel="noreferrer">https://bugs.php.net/bug.php?id=71845</a></p>

<p>This is also related. <a href="https://groups.google.com/forum/?utm_medium=email&amp;utm_source=footer#!msg/google-cloud-sql-discuss/4HNvmq7MpU4/kuSjhkS2AwAJ" rel="noreferrer">https://groups.google.com/forum/?utm_medium=email&amp;utm_source=footer#!msg/google-cloud-sql-discuss/4HNvmq7MpU4/kuSjhkS2AwAJ</a></p>

<p>As far as I understand, there is no way to resolve for PDO.</p>

<hr>

<p>Update 4</p>

<p>Some people blame google's design of CN names (I agree with them actually..)</p>

<blockquote>
  <p>What's worst is you guys use impossible CN names (:) . If the CN was without colon maybe I can map the ip and the CN in my hosts file so that when peer validation is done it can pass. With the colon, php thinks  is host and  is port</p>
</blockquote>

<p>and Google's staff? understand the problem.</p>

<blockquote>
  <p>I understand the current situation when connecting by IP is not ideal.</p>
</blockquote>

<p>But it seems they provide a solution called 'proxy'.</p>

<p><a href="https://groups.google.com/forum/#!topic/google-cloud-sql-discuss/gAzsuCzPlaU" rel="noreferrer">https://groups.google.com/forum/#!topic/google-cloud-sql-discuss/gAzsuCzPlaU</a></p>

<p>I'm using Cloud SQL second generation and my applications are hosted GCE. So I think I can use the proxy way. I will try it now.</p>

<hr>

<p>Update 5</p>

<p>Setup the proxy access. Solved both PDO and mysqli access.</p>

<p>Install the proxy on Ubuntu</p>

<pre><code>$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64
$ mv cloud_sql_proxy.linux.amd64 cloud_sql_proxy
$ chmod +x cloud_sql_proxy
$ sudo mkdir /cloudsql; sudo chmod 777 /cloudsql
$ ./cloud_sql_proxy -dir=/cloudsql -instances=&lt;project name&gt;:us-central1:mydb 
</code></pre>

<p>PDO</p>

<pre><code>&lt;?php
$pdo = new pdo('mysql:unix_socket=/cloudsql/&lt;project name&gt;:us-central1:mydb;dbname=testdb','root','test');
$stmt = $pdo-&gt;query("SHOW TABLES;");
$row = $stmt-&gt;fetch(PDO::FETCH_ASSOC);
print_r($row);
?&gt;
</code></pre>

<p>mysqli</p>

<pre><code>$mysqli = mysqli_connect('localhost', 'root', 'test', 'testdb', 3306, '/cloudsql/&lt;project name&gt;:us-central1');
$sql = "SELECT id FROM users";
if ($result = $mysqli-&gt;query($sql)) {
    while ($row = $result-&gt;fetch_assoc()) {
        echo $row["id"] . "\n";
    }
    $result-&gt;close();
}
$mysqli-&gt;close();
</code></pre>

<p>refs (Japanese)</p>

<p><a href="http://blog.hrendoh.com/connecting-to-google-cloud-sql-using-mysql-client/" rel="noreferrer">http://blog.hrendoh.com/connecting-to-google-cloud-sql-using-mysql-client/</a>
<a href="http://blog.hrendoh.com/google-appengine-php-using-cloud-sql/" rel="noreferrer">http://blog.hrendoh.com/google-appengine-php-using-cloud-sql/</a></p>