mysql SSL connection

stenberg

Apologies if this is already in here somewhere, but I can't seem to find documentation on how to setup config file for a standard mysql SSL connection using certificates to encrypt the connection.

EDIT: Just to be clear we already use SSL connections on our database exclusively so I am not asking how to set that up, just how to pass certificates in config file.

allan

Excellent question! There isn't actually a way to configure that for the PDO connection that Editor creates at the moment - but let me get back to you on that tomorrow morning. I'll add it in as I think this is a useful thing to have and I'll post details of the patch (it will be relatively simple I think).

Regards,
Allan

stenberg

Thanks very much! I was just starting to dig in to the PDO connection and came to the same conclusion. Really look forward to the patch.

Regards
Steve

allan

Hi Steve,

Here we go: in php/Database/Driver/Mysql/Query.php replace the connect static method with:

    static function connect( $user, $pass='', $host='', $port='', $db='', $dsn='' )
    {
        if ( is_array( $user ) ) {
            $opts = $user;
            $user = $opts['user'];
            $pass = $opts['pass'];
            $port = $opts['port'];
            $host = $opts['host'];
            $db   = $opts['db'];
            $dsn  = isset( $opts['dsn'] ) ? $opts['dsn'] : '';
            $pdoAttr = isset( $opts['pdoAttr'] ) ? $opts['pdoAttr'] : array();
        }

        if ( $port !== "" ) {
            $port = "port={$port};";
        }

        try {
            $pdoAttr[ PDO::ATTR_ERRMODE ] = PDO::ERRMODE_EXCEPTION;

            $pdo = @new PDO(
                "mysql:host={$host};{$port}dbname={$db}".self::dsnPostfix( $dsn ),
                $user,
                $pass,
                $pdoAttr
            );
        } catch (\PDOException $e) {
            // If we can't establish a DB connection then we return a DataTables
            // error.
            echo json_encode( array( 
                "sError" => "An error occurred while connecting to the database ".
                    "'{$db}'. The error reported by the server was: ".$e->getMessage()
            ) );
            exit(0);
        }

        return $pdo;
    }

Then all you need to do is add a pdoAttr option to your $sql_details array in config.php with the PDO attributes you want to use - e.g.:

$sql_details = array(
    "type" => "Mysql",
    "user" => "...",
    "pass" => "...",
    "host" => "...",
    "port" => "",
    "db"   => "...",
    "dsn"  => "",
    "pdoAttr" => array(
        PDO::MYSQL_ATTR_SSL_KEY    =>'/etc/mysql/ssl/client-key.pem',
        PDO::MYSQL_ATTR_SSL_CERT=>'/etc/mysql/ssl/client-cert.pem',
        PDO::MYSQL_ATTR_SSL_CA    =>'/etc/mysql/ssl/ca-cert.pem'
    )
);

And that should do it

Allan

stenberg

TL;DR The patch works fine!

For the benefit of anybody using this I had to go a little further down the rabbit hole. First, only the certificate authority seems to be needed.

Even then, if you point it to a bogus path it still works, so in other words the mere presence of PDO::MYSQL_ATTR_SSL_CA pointing to a random string is enough also. That particular bit of weirdness is because apparently there is no checking of the certificate done; the driver just needs to know you want to use SSL.

But the bottom line is, after creating a user "REQUIRE SSL" (to be certain) and putting in a SHOW STATUS LIKE 'Ssl_cipher' in the connection code as a test, its definitely working as expected.

Thanks for such an incredibly fast response!

allan

SSL certs are always good fun...!

Great to hear that does the job for you. It will be in the next release of Editor - probably a 1.6.4 release in early August.

Regards,
Allan