How to force SSL mode when connecting to MySQL through PHP

mabali

My MySQL server is hosted in Azure and requires SSL. As the database also has an IP filter on it, I need to access the database from the webserver (so I need server side processing).
My connection code looks like this:

    $sql_details = array(
        'user' => 'MyUserName',
        'pass' => 'MyPassword',
        'db'   => 'MyDatabase',
        'host' => 'MyAzureDatabase.mysql.database.azure.com',
        "port" => "3306",
        "dsn" => "charset=utf8",
        "sslmode"=> "require",
        "pdoAttr" => array(
        PDO::MYSQL_ATTR_SSL_KEY    => __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem',
        PDO::MYSQL_ATTR_SSL_CERT=>  __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem',
        PDO::MYSQL_ATTR_SSL_CA    =>  __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem'
    )

But my browser just returns:

{"error":"An error occurred while connecting to the database. The error reported by the server was: SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry."}

What should I do to build the SSL connection?

colin

This thread should help, it's asking the same thing.

Cheers,

Colin

mabali

I do not have a file php/Database/Driver/Mysql/Query.php; my PHP runs on a Windows server, all I have is a php_mysqli.dll. Not much editing I can do there.
Besides, the thread you mention is four years old and the answer states the issue will be resolved in the next release (which has probably been released by now ;-))

allan

php/Database/Driver/Mysql/Query.php;

Is a file from our PHP libraries. php_mysqli.dll is part of the PHP distribution. We actually make use of PDO rather than mysqli.

Can you tell me how you would normally connect to your database (obviously masking out the user / pass and any other confidential information).

Thanks,
Allan

mabali

Hi Allan,

My normal PHP connection (using the builting MySQL dll) looks like this:

$MySqlServer = "MyServername";
    $MySqlUser = "MyUsername";
    $MySqlPassword = "MyPassword";
    $MySQLSSLCert="c:\inetpub\wwwroot\SSL\BaltimoreCyberTrustRoot.crt.pem";
    $MySQLDatabase="MyDatabase";

    $MySqlConn=mysqli_init();
    mysqli_ssl_set($MySqlConn,NULL,NULL, $MySQLSSLCert, NULL, NULL);
    mysqli_real_connect ($MySqlConn, $MySqlServer, $MySqlUser, $MySqlPassword, $MySQLDatabase, 3306, MYSQLI_CLIENT_SSL);

For using DataTables I have only downloaded the ssp.class.php from this site and use that before I make the connection:

        require( 'ssp.class.php' );
     
    echo json_encode(
        SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
    );

Should I download more files (and include those in my code)?
Thanks!
Martijn

allan

Oh I see - just to confirm, you aren't using the Editor libraries here, but rather the SSP class?

This is where the code for the SSP class would need to be modified to add SSL support. Specifically the PDO options array on line 407 will need the three SSL attributes you added above.

Note - don't try to use your mysqli_ initialisation here, as the SSP class uses PDO, not mysqli. If you want to use mysqli you'd need to modify all database interaction in the class (doable, but I'd suggest just getting the PDO setup working - it will be a lot less work).

Allan

mabali

Hi Allan,

A snippet from the ssp.class file after I modified it:

static function sql_connect ( $sql_details )
    {
        try {
            $db = @new PDO(
                "mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
                $sql_details['user'],
                $sql_details['pass'],
                array( 
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION , 
                    PDO::MYSQL_ATTR_SSL_KEY => __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem',
                    PDO::MYSQL_ATTR_SSL_CERT=>  __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem', 
                    PDO::MYSQL_ATTR_SSL_CA    =>  __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem'
                )
            );
        }

I have removed the PDO statements from my own code so the config part after which I build the connection now looks like this:

    $sql_details = array(
        'user' => 'Mysuername',
        'pass' => 'MyPassword',
        'db'   => 'MyDatabase',
        'host' => 'MyDBHost.mysql.database.azure.com',
        "port" => "3306",
        "dsn" => "charset=utf8"     
    );
     
    require( 'ssp.class.php' );
     
    echo json_encode(
        SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
    );

However, I get a very generic SQLSTATE[HY000] [2002] error now.

allan

The sql_connect function there doesn't make use of the port. Perhaps:

static function sql_connect ( $sql_details )
    {
        try {
            $db = @new PDO(
                "mysql:host={$sql_details['host']};port={$sql_details['port']};dbname={$sql_details['db']}",
                $sql_details['user'],
                $sql_details['pass'],
                array( 
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION , 
                    PDO::MYSQL_ATTR_SSL_KEY => __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem',
                    PDO::MYSQL_ATTR_SSL_CERT=>  __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem', 
                    PDO::MYSQL_ATTR_SSL_CA    =>  __DIR__ . './SSL/BaltimoreCyberTrustRoot.crt.pem'
                )
            );
        }

However, 3306 is the default port for MySQL, so I don't think that is the issue here.

Try removing the @ from the new PDO(...) statement. The @ will suppress error messages, so hopefully any error messages will point us in the right direction...!

Regards,
Allan

mabali

I have it fixed!
I altered the sql_connect function to look like this:

static function sql_connect ( $sql_details )
    {
        try {
            $db = new PDO(
                "mysql:host={$sql_details['host']};port={$sql_details['port']};dbname={$sql_details['db']}",
                $sql_details['user'],
                $sql_details['pass'],
                array(
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ,
                    PDO::MYSQL_ATTR_SSL_CERT=> 'c:/inetpub/SSL/BaltimoreCyberTrustRoot.crt.pem',
                    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
                )
            );
        }

and now I have data.
Thanks for helping me out in this!