Connect to MySQL via SSL

#1

Hello,
We have just set up a MySQL instance on Amazon Web Services and to connect to the database, I need to include a certificate (rds-combined-ca-bundle.pem). I could not see a place where I could reference this and without it, the connection isn’t accepted.

Can this be done in Wappler?

Cheers,
Brian

#2
#3

Hi Patrick,

I do have a tunnel set up for FileZilla FTP but need to reference the .pem file for MySQL otherwise connections are refused. I can connect manually in PHP by using:

$con=mysqli_init();
$con->ssl_set(NULL,NULL,’/etc/pki/tls/certs/rds-combined-ca-bundle.pem’,NULL,NULL);
$con->real_connect($hostname ,$username, $password, $database);

I’ll check that page though.

Cheers,
Brian

assigned patrick #4
#5

Ah, seems that it is different from the SSH. We use PDO and it seems that there are some attributes that needs to be set. Will investigate it.

#6

Hello Patrick,
Do you have any update on connecting to MySQL via PDO and SSL?
Cheers,
Brian

#7

I didn’t have time to work on the update, but you can try to edit it yourself. Open the file dmxConnectLib/lib/db/Connection.php.

At line 48 you have the following code:

$pdo_options = array(
  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
  PDO::ATTR_EMULATE_PREPARES => $preps,
  PDO::ATTR_STRINGIFY_FETCHES => FALSE
);

You can add here the options for the SSL. Check the PHP documentation for all the available options https://www.php.net/manual/en/ref.pdo-mysql.php.

$pdo_options = array(
  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
  PDO::MYSQL_ATTR_SSL_CA => '/etc/pki/tls/certs/rds-combined-ca-bundle.pem',
  PDO::ATTR_EMULATE_PREPARES => $preps,
  PDO::ATTR_STRINGIFY_FETCHES => FALSE
);
1 Like
#8

Hi Patrick,

I tried updating this but unfortunately, I received the same error on connecting. To test my credentials I tried connecting manually outside of Wappler and that did work as long as I pointed to the local .pem file.

$host = ‘host’;
$db = ‘database’;
$user = ‘user’;
$pass = ‘password’;
$charset = ‘utf8’;

$options = [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::MYSQL_ATTR_SSL_CA => ‘/Users/brian/rds-combined-ca-bundle.pem’,
\PDO::ATTR_EMULATE_PREPARES => false,
];
$dsn = “mysql:host=$host;dbname=$db;charset=$charset”;
try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$stmt = $pdo->query(“SELECT * FROM table LIMIT 1”);
$user = $stmt->fetch();

$results = print_r($user, true);
echo $results;

Trying the same in Wappler wasn’t successful. I wondered if it caches any code so maybe it didn’t see my changes?

On a different note, great to see 2.0 released and the new website looks really nice.

Best regards,
Brian

#9

Which PHP version do you have on your server? Did you check the PHP documentation, there are several SSL options available and the required option depends on the type of certificate you have.

Try replacing PDO::MYSQL_ATTR_SSL_CA with PDO::MYSQL_ATTR_SSL_CAPATH or PDO::MYSQL_ATTR_SSL_CERT. With self-signed certificates it is useful to not verify it, setting PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT to false could help there.