MySQL SSL connection

Yii 1.1 General Discussion
1

Hi.

How do I connect to an SSL-enabled MySQL server?

In pure_php I’d do


mysql_connect ($host, $user, $pass, FALSE, MYSQL_CLIENT_SSL)

but couldn’t find how to do this in Yii!

thanks

2

According to this comment, try this:


'components' => array(

   'db' => array(

      'connectionString' => '...',

      'username' => '...',

      'password' => '...',

      'attributes' => 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/ca-cert.pem',

      ),

   ),

),

Seems like you need a relatively new PHP version.

3

I’m using 5.3.10, which was released in 2012, so according to that comment must contain ssl support. I also checked online and this was introduced in 5.3.8.

When I add the options to the connection strings I get:

I found this link about that error but ends with a misconfiguration.

I tried in plain php doing an ssl connection and works.

I noticed whatever I put into the cert strings (that is putting a wrong path to the cert files) I get the same error, seems I’m missing something in php, but cannot figure out what! :(

Any ideas?

Thanks!

4

You mean with mysql_connect or with the PDO class? The latter is used by Yii so it doesn’t really matter if mysql_connect works I think.

Since you use the latest PHP version I assume you are able to compile PHP from source on that machine? If that’s the case try to build PHP with the native mysql client library (mysqlnd), if that’s already the case try to build without mysqlnd and use the client library that ships with PHP. Maybe php.ini modification is also possible to switch between those two.

If you connect via unix socket try to connect via tcp/ip (but I guess that’s already the case).

One more thing you can do is to install latest PHP 5.4RCX.

5

I used mysql_connect :confused:

No, I installed packages from dotdeb. I’d rather avoid compile from source on this machine, and have no time to build custom tests.

Right now Im trying to avoid remote connections, so I maybe can avoid to use SSL, since it’s a local connection on a closed server.

Yeah I’m already on TCP.

Maybe I’ll wait for someone else to try newer versions :)

Or in case I need, I’ll show back!

Thank you!

ciao

6

Check to make sure your MySQL server supports SSL:




mysql> SHOW VARIABLES LIKE 'have_ssl';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| have_ssl      | YES   |

+---------------+-------+

If so, you can connect over SSL:

  1. Create a MySQL user that requires SSL, e.g.,

GRANT ALL ON database_name.* TO 'user_name'@'%' IDENTIFIED BY 'good_password' REQUIRE SSL;

(I’m not suggesting you should grant ALL, or grant access to a user from any host. I recommend being more specific.)

  1. Set the PDO SSL cipher attribute on the db connection (since PHP 5.3.7), e.g.,



'components'=>array(

    'db'=>array(

        'connectionString'=>''mysql:host=host_address;port=3306;dbname=database_name'',

        'attributes'=>array(

            PDO::MYSQL_ATTR_SSL_CIPHER=>'DHE-RSA-AES256-SHA:AES128-SHA',

        ),

        'charset'=>'utf8',

        'emulatePrepare'=>true,

        'enableParamLogging'=>true, // Set to false for production

        'username'=>'user_name',

        'password'=>'good_password',

    ),

),