I use MeekroDB to connect to MySQL (PHP, on Linux shared web hosting) I have just tried MeekroDB 3.1.3 today (server running PHP 8.2.26)
I can connect to my localhost MySQL just fine using Meekrdob (which doesnt require SSL certificate) - ie the MySQL my web host provides. All working fine!
I am trying to connect to an external Azure MySQL - i.e. one hosted by Microsoft, not my web host. (Which I can connect to via MySQL Workbench just fine and straight up PHP PDO just fine, with SSL) but cannot work out why MeekroDB isn't working...
My PHP includes
require_once 'db.class.php'
Then
$certloc = CERTIFICATES . 'DigiCertGlobalRootCA.crt.pem';
$myhost = 'MYSERVER.mysql.database.azure';
$myport = '3306';
$mydbName = 'MYDB';
$myuser = 'MYUSER';
$mypassword = 'MYPASSWORD';
This DOES work (PDO direct):
$options = array( PDO::MYSQL_ATTR_SSL_CA => $certloc );
$db = new PDO('mysql:host=' . $myhost . ';port=' . $myport . ';dbname=' . $mydbName, $myuser, $mypassword, $options);
$query = 'SELECT * FROM TheTable';
$stmt = $db->prepare($query);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
This DOES NOT work (Meekrob static method):
DB::$host = $myhost;
DB::$port = $myport;
DB::$dbName = $mydbName;
DB::$user = $myuser;
DB::$password = $mypassword;
$connect_options = [ PDO::MYSQL_ATTR_SSL_CA => $certloc ];
DB::$connect_options = $connect_options;
DB::query("SELECT * FROM TheTable");
The error I get is
Fatal error: Uncaught MeekroDBException: Unable to connect to MySQL server!
If I turn off "require_secure_transport" on my Azure portal, the connection works fine!
So it's something to do with the SSL and MeekroDB....
Does anyone have any insights?
I use MeekroDB to connect to MySQL (PHP, on Linux shared web hosting) I have just tried MeekroDB 3.1.3 today (server running PHP 8.2.26)
I can connect to my localhost MySQL just fine using Meekrdob (which doesnt require SSL certificate) - ie the MySQL my web host provides. All working fine!
I am trying to connect to an external Azure MySQL - i.e. one hosted by Microsoft, not my web host. (Which I can connect to via MySQL Workbench just fine and straight up PHP PDO just fine, with SSL) but cannot work out why MeekroDB isn't working...
My PHP includes
require_once 'db.class.php'
Then
$certloc = CERTIFICATES . 'DigiCertGlobalRootCA.crt.pem';
$myhost = 'MYSERVER.mysql.database.azure.com';
$myport = '3306';
$mydbName = 'MYDB';
$myuser = 'MYUSER';
$mypassword = 'MYPASSWORD';
This DOES work (PDO direct):
$options = array( PDO::MYSQL_ATTR_SSL_CA => $certloc );
$db = new PDO('mysql:host=' . $myhost . ';port=' . $myport . ';dbname=' . $mydbName, $myuser, $mypassword, $options);
$query = 'SELECT * FROM TheTable';
$stmt = $db->prepare($query);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
This DOES NOT work (Meekrob static method):
DB::$host = $myhost;
DB::$port = $myport;
DB::$dbName = $mydbName;
DB::$user = $myuser;
DB::$password = $mypassword;
$connect_options = [ PDO::MYSQL_ATTR_SSL_CA => $certloc ];
DB::$connect_options = $connect_options;
DB::query("SELECT * FROM TheTable");
The error I get is
Fatal error: Uncaught MeekroDBException: Unable to connect to MySQL server!
If I turn off "require_secure_transport" on my Azure portal, the connection works fine!
So it's something to do with the SSL and MeekroDB....
Does anyone have any insights?
I have tried your code and got the same error Change your code to connect using the certificate:
$options = [
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
PDO::MYSQL_ATTR_SSL_CA => true,
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
];
...
Refer this link to how to set up remote mysql connection with ssl.
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => 'false'
Below is the code for MeekroDB with SSL to connect to Azure MySQL:
$connect_options = [
PDO::MYSQL_ATTR_SSL_KEY => '',
PDO::MYSQL_ATTR_SSL_CERT => '',
PDO::MYSQL_ATTR_SSL_CA => '',
PDO::MYSQL_ATTR_SSL_CAPATH => '',
];
DB::$connect_options = $connect_options;
$db = new MeekroDB($dsn, $user, $password, $connect_options);
Output:
Refer to this Devolutions documentation for generating self-signed server and client certificates with OpenSSL.
Alternatively, you can use ssl_ca only:
PDO::MYSQL_ATTR_SSL_CA => $ssl_ca,
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
DB::$ssl = array('key' => '', 'cert' => '', 'ca_cert' => $certloc, 'ca_path' => '', 'cipher' => ''); DB::$connect_options = array(MYSQLI_OPT_CONNECT_TIMEOUT => 10);works... – Chris Lydon Commented Jan 11 at 21:40