Symfony2 doctrine connect to database via SSL

Question

Trying to connect to my MySQL database via SSL, I have successfully established the connection from my webserver via ssh with the following command line:

mysql -h my.host.here --port=5454  -v --ssl-ca=/etc/apache2/ssl/mysql/ca-cert.pem --ssl-cert=/etc/apache2/ssl/mysql/client-cert.pem --ssl-key=/etc/apache2/ssl/mysql/client-key.pem -u user -p

However, trying to set up the same connection in symfony2 and doctrine, all I keep getting is an "SSL error"

    $params = array(
        'driver'   => 'pdo_mysql',
        'user'     => 'user',
        'password' => 'pass',
        'host'     => 'my.host.here',
        'dbname'   => 'media',
        'port'     => '5454',
    );

    if($this->container->hasParameter('media_ca') && $this->container->hasParameter('media_cert') && $this->container->hasParameter('media_key')) {
        $params['driverOptions'] = array(
            PDO::MYSQL_ATTR_SSL_CA => $this->container->hasParameter('media_ca'),
            PDO::MYSQL_ATTR_SSL_CERT => $this->container->hasParameter('media_cert'),
            PDO::MYSQL_ATTR_SSL_KEY => $this->container->hasParameter('media_key'),
        );
    }

/* Using this instead with only the ca_cert gives me the same error
    if($this->container->hasParameter('media_ca')) {
        $params['driverOptions'] = array(
            PDO::MYSQL_ATTR_SSL_CA => $this->container->hasParameter('media_ca'),
        );
    }
*/
    $connectionFactory = $this->container->get('doctrine.dbal.connection_factory');
    $conn = $connectionFactory->createConnection($params);
    return $conn;

In my log:

[2013-10-01 15:23:30] request.CRITICAL: Uncaught PHP Exception PDOException: "SQLSTATE[HY000] [2026] SSL connection error" at /var/www/mysite/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php line 36 {"exception":"[object] (PDOException: SQLSTATE[HY000] [2026] SSL connection error at /var/www/mysite/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:36)"} []

I have doublechecked that the webserver user (www-data) has access to the certificate files, and that the path to those cert files are correct (defined in the symfony2 parameters).

I can not think of anything else that is different between my command line connection and the one I have specified with doctrine/symfony2.

Fraunhofer

Mattias, take a look at this answer, it might be helpful #4500468 – Love

Answer 1

You are wrong with retrieving parameters. You need getParameter($param) method instead of hasParameter($param). These lines are correct.

PDO::MYSQL_ATTR_SSL_CA => $this->container->getParameter('media_ca'),
PDO::MYSQL_ATTR_SSL_CERT => $this->container->getParameter('media_cert'),
PDO::MYSQL_ATTR_SSL_KEY => $this->container->getParameter('media_key'),

Hailey

Answer 2

Just to record the full example how I ended up solving the issue:

//Create a connection to another public database.
 private function videoDatabase() {

    //Create a connection to pub-DB.

    $params = array(
        'driver'   => $this->container->getParameter('media_database_driver'),
        'user'     => $this->container->getParameter('media_database_user'),
        'password' => $this->container->getParameter('media_database_password'),
        'host'     => $this->container->getParameter('media_database_host'),
        'dbname'   => $this->container->getParameter('media_database_name'),
        'port'     => $this->container->getParameter('media_database_port')
    );

    if($this->container->hasParameter('media_ca') && $this->container->hasParameter('media_cert') && $this->container->hasParameter('media_key')) {
       $params['driverOptions'] = array(
                PDO::MYSQL_ATTR_SSL_CA => $this->container->getParameter('media_ca'),
                PDO::MYSQL_ATTR_SSL_CERT => $this->container->getParameter('media_cert'),
                PDO::MYSQL_ATTR_SSL_KEY => $this->container->getParameter('media_key'),
        );
   }

    $connectionFactory = $this->container->get('doctrine.dbal.connection_factory');
    $conn = $connectionFactory->createConnection($params);
    return $conn;

 }

Fraunhofer