Symfony : how to set SSL parameters in Doctrine DBAL configuration (YAML)?

Question

I'd like to add my SSL cert and key files to Doctrine DBAL configuration but I don't see how to achieve that.

In PHP, I just have to write something like :

$databaseHandler = new \PDO(
    'mysql:host=my_host;dbname=my_db',
    'username',
    'password',
    array(
        \PDO::MYSQL_ATTR_SSL_KEY   => '.../client-key.pem',
        \PDO::MYSQL_ATTR_SSL_CERT  => '.../client-cert.pem',
        \PDO::MYSQL_ATTR_SSL_CA    => '.../ca-cert.pem'
    )
);

I understand there is a Custom Driver Option driverOptions, and I saw this answer but I'm not sure about how to translate that into YAML.

I have the feeling I should write something close to :

doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        driverOptions:
            PDO::MYSQL_ATTR_SSL_CA: '.../client-key.pem'
            PDO::MYSQL_ATTR_SSL_CERT: '.../client-cert.pem'
            PDO::MYSQL_ATTR_SSL_CA: '.../ca-cert.pem'

But double colons won't really please YAML...

Just use the actual values of the constants. php -r "echo PDO::MYSQL_ATTR_SSL_CA;" yields 1009 so replace PDO::MYSQL_ATTR_SSL_CA with 1009. Bit of a hack perhaps but the numbers won't change. — Cerad, Apr 27 '15 at 18:34
Oh you learn me something : each property of each PHP class and its extensions has a unique id ? — Ivan Gabriele, Apr 27 '15 at 18:40
All constants have a value. And while it's possible that the value may change with newer versions of PHP it's very rare. — Cerad, Apr 27 '15 at 18:42
Ok :) Thank you very much ! Meanwhile somebody may know a "cleaner" way, I will do like that. — Ivan Gabriele, Apr 27 '15 at 18:53
its bad idea to use constant values instead of constant names. Right now php -r "echo PDO::MYSQL_ATTR_SSL_CA;" shows 1012 in php 5.5.9 — Andrew Zhilin, May 13 '16 at 09:59

score 10 · Accepted Answer · edited Jul 20 '21 at 14:37

10

Since Symfony 3.2 this became a lot easier:

doctrine:
    dbal:
        <other configs>
        options:
            !php/const:PDO::MYSQL_ATTR_SSL_CA: %ca_cert%
            !php/const:PDO::MYSQL_ATTR_SSL_KEY: %private_key%
            !php/const:PDO::MYSQL_ATTR_SSL_CERT: %public_cert%

edited Jul 20 '21 at 14:37

Ivan Gabriele

6,433
5
39
60

answered Nov 20 '17 at 12:02

Robert

116
1
2

score 8 · Answer 2 · answered Oct 19 '16 at 22:46

8

I found a much easier way than the rest. Make the following settings in app/config/config.yml:

# Doctrine Configuration
doctrine:
    dbal:
        driver:   pdo_mysql
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        # Options for SSL connection
        options:
            MYSQL_ATTR_SSL_CA : %ca_cert%
            MYSQL_ATTR_SSL_KEY : %private_key%
            MYSQL_ATTR_SSL_CERT : %public_cert%

Then in your app/config/parameters.yml file:

parameters:
    ...
    # SSL Info
    private_key: /etc/my.cnf.d/certs/client-key.pem
    public_cert: /etc/my.cnf.d/certs/client-cert.pem
    ca_cert: /etc/my.cnf.d/certs/ca-cert.pem

I tested this on Symfony3 and this works great. The paths above may be different, in particular the certs may be different depending on your distro and how you set it up.

answered Oct 19 '16 at 22:46

Alvin Bunk

7,621
3
29
45

And probably both Andrew Zhilin and @Cerad will be happy because there is no need to specify constant values! – Alvin Bunk Oct 19 '16 at 22:49
Tested on Symfony 3.2 with PHP 5.6.20 and it does not work for me. As you specify strings, and as PDO expects associated numbers to PDO constants, it can't work. I prefer @Samuel Scheiderich solution. Tested and approved. – Delphine Nov 04 '16 at 13:46
Hi @Delphine. I had originally tried/used Samuel's (and also Valentas) solution, but then I came up with my own. I suspect you may have had a problem with your yaml file or something else, because I believe my solution should work - you may want to double-check. Your statement about 'can't work' does not seem correct. – Alvin Bunk Nov 04 '16 at 15:33
I understand your point of view but my PDO string receives only "MYSQL_ATTR_SSL_CA" and not a conversion to associated number. Maybe we can configure something in php.in or mycf.ini to accept both.. I said that it does not work for me and I explain that, for me, PDO received string and not number equivalent. Maybe I was not so clear.. – Delphine Nov 04 '16 at 16:31
1

It doesn't work: 1) Use `tcpdump`, you'll see the connection isn't encrypted. 2) Try: `SHOW STATUS WHERE Variable_name = 'Ssl_cipher'`, you'll see the variable is empty. – Luc PHAN May 16 '19 at 12:04
Hi there @NielasAran. My configuration is basically cut and paste from a working Production system. The names of the pem files are obfuscated. If it's not working for you, it's possible you didn't create the SSL correctly. That's a lot of work - believe me, so I can't help you with that. – Alvin Bunk May 16 '19 at 16:21
1

My configuration is correct: It works if I use Robert's solution. I checked with `tcpdump` and `Ssl_cipher`. The fact you're able to connect doesn't mean the connection is encrypted. Another way to check is to reject non-encrypted connection with `require_secure_transport` or with `REQUIRE SSL`. So, 4 ways to check so far. – Luc PHAN May 17 '19 at 13:08

score 4 · Answer 3 · answered Jan 21 '16 at 16:56

Symfony configuration via yaml (and possibly xml), doesn't allow the keys to be dynamically set, which means you can't use the constants. To get around this, you can create an extra PHP config file that just handles making a key out of the constants.

The solution in a Gist is here: https://gist.github.com/samsch/d5243de3924a8ad10df2

The two major features that this utilizes are that a PHP config file can use any string value for the key, including variables, constants; and that you can use parameters as values for other parameters (something I didn't know until I tried it recently.)

So, you add the PHP config file in config.yml:

imports:
    - { resource: parameters.yml }
    - { resource: pdo-constants.php }

pdo-constants.php is this:

<?php
$container->setParameter("pdo_options", [
    PDO::MYSQL_ATTR_SSL_CA => "%pdo_ca_file%",
]);

Add any other constants you need as well.

Then in parameters.yml, you just need the values for your constants:

parameters:
#...
    pdo_ca_file: /pathtocerts/certs/mysql-ca.pem

Now, I'm guessing that working with another DB system which uses PDO constants would be similar, but I've only used this MySQL.

I was stuck with Doc2, PHP 5.6.22 and Symfony 2 ! It didn't recognize "1010" as a key and parsed it like certificate.. Obviously, it tells that it was a "bad certificate" (Oh ? Normal, it's a key..) ! If your solution works, you really will save my day ! — Delphine, Jun 20 '16 at 14:10

score 4 · Answer 4 · answered Dec 04 '20 at 10:41

Now in 2020, with Symfony > 4.2, the solution is :

# Doctrine Configuration
doctrine:
    dbal:
        driver:   pdo_mysql
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        # Options for SSL connection
        options:
            !php/const PDO::MYSQL_ATTR_SSL_CA : %ca_cert%
            !php/const PDO::MYSQL_ATTR_SSL_KEY : %private_key%
            !php/const PDO::MYSQL_ATTR_SSL_CERT : %public_cert%

Source : symfony.com/doc/4.2/service_container/parameters.html

Tested with Symfony 5.1.8

score 2 · Answer 5 · answered Apr 27 '15 at 19:58

2

Instead of PDO constats, You shoul use their values in options:

doctrine:
    dbal:
        connections:
            default:
                driver:   %database_driver%
                host:     %database_host%
                port:     %database_port%
                dbname:   %database_name%
                password: %database_password%
                charset:  UTF8
                options:
                    1010 : %private_key% 
                    1011 : %public_cert% 
                    1012 : %ca_cert%

answered Apr 27 '15 at 19:58

Valentas

2,135
1
17
21

Not sure where you got "1012", but PDO::MYSQL_ATTR_SSL_CA is 1009. – jmar Jan 20 '16 at 19:39
8

its bad idea to use constant values instead of constant names. Right now php -r "echo PDO::MYSQL_ATTR_SSL_CA;" shows 1012 in php 5.5.9. And it was 1009 some time ago. – Andrew Zhilin May 13 '16 at 09:59

score 1 · Answer 6 · answered Feb 28 '20 at 18:45

An alternative approach would be installing composer/ca-bundle to help find the path to the CA root bundle and use a custom compiler pass to automagically register that path with Doctrine:

<?php

namespace App\DepenedencyInjection\Compiler;

use Composer\CaBundle\CaBundle;
use Symfony\Component\DependencyInjection\Compiler\CompilerPassInterface;
use Symfony\Component\DependencyInjection\ContainerBuilder;

/**
 * Registers a CA root bundle with the PDO MySQL driver used by Doctrine DBAL
 *
 * This allows Doctrine to connect to MySQL instances that force SSL encryption, such as Azure's.
 */
final class RegisterCABundleWithPDOMysqlDriverPass implements CompilerPassInterface
{
    /**
     * @inheritDoc
     */
    public function process(ContainerBuilder $container)
    {
        $caPathOrFile = CaBundle::getSystemCaRootBundlePath();

        foreach ($container->getParameter('doctrine.connections') ?? [] as $connectionName) {
            $definition = $container->getDefinition($connectionName);
            $options = $definition->getArgument(0) ?? [];
            $options['driverOptions'][\PDO::MYSQL_ATTR_SSL_CA] = $caPathOrFile;
            $definition->setArgument(0, $options);
        }
    }
}

score 0 · Answer 7 · answered May 06 '22 at 19:34

I am using PHP 5.6.40 on docker and have been debugging why my local would not connect via ssl. For some weird reason it wants const 1009 instead of 1012. Dropping this answer here in case anyone stumbles upon it on the future.

doctrine:
    dbal:
        connections:
            default:
                options:
                    1001: true
                    1009: %ssl_cert_path%

Symfony : how to set SSL parameters in Doctrine DBAL configuration (YAML)?

7 Answers7

Linked