Connect to MySQL via SSL

[brian]

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

[patrick]

Server side connection to mysql via ssh

After further investigation - seems that connecting from PHP to MySQL server through SSH is not really a well developed solution. However - you can actually already do it by installing such tunnel on your server first that maps to local post and connect to that port. A good tool for that is autossh Read for background info:

[brian]

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

[patrick]

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.

[brian]

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

[patrick]

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
);

[brian]

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

[patrick]

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.

[brian]

Hi Patrick, Thank you - as soon I have started a new subscription, I’ll try this out.
Best regards,
Brian

[brian]

Hello Patrick,
I’ve just renewed my subscription and tried this out. Unfortunately, I am still having connection issues - sorry. I am also unable to connect via sFTP to the webserver but I can raise a separate request for that.

I have tried adding the location of the ‘rds-combined-ca-bundle.pem’ in the new SSL CA Certificate box as shown below.

I’ve tried Verify on and off but always see the error below.

The version of PHP on the local machine is 5.6.10

Thanks and best regards,
Brian

[Teodor]

Hi Brian,
That’s supported by PHP only in PHP 7.0.18+

[brian]

Hi Teodor,
That was quick Ok, so to clarify, if I update my local install to PHP 7.0.18+ I should then be able to connect to the MySQL on the host machine.

Cheers,
Brian

[patrick]

SSL is supported as of PHP 5.3.7 and the verify of the SSL Certificate is supported as of 7.0.18.

Are you sure the path to the certificate is correct?

[brian]

Hi Patrick,

It is the same path I used when connecting via a manual script before so that should be ok. I am going to update my MAMP installation and try again later.

\PDO::MYSQL_ATTR_SSL_CA => ‘/Users/brian/rds-combined-ca-bundle.pem’,

Thanks and best regards,
Brian

[patrick]

It seems that the Test Connection in the Connection dialog uses a different script to test the connection and the SSL params weren’t added there. Will update it for the next Wappler update.

[brian]

Thank you, Patrick.

[brian]

Hi Patrick,
Could you confirm if the Test Connection script was updated, please as I still seeing the same error.

Cheers,
Brian

[patrick]

Yes, the connection script did have an update in Wappler 2.1.0.

Is the error still Access denied?

[brian]

Hi Patrick,
Firstly, I just noticed that I had local selected rather than remote so that was the main issue - doh.

I now think the database connection scripts being uploaded cannot be run in the remote environment because PHP is too old due to some legacy apps.

I’ve just setup an additional host and the connections are working fine there so the issue is not Wappler and the SSL update did work. Thanks for helping work this through.

All the best,
Brian