5

This is how I usually connect to a MySQL database using SSL:

$db = mysqli_init();
mysqli_ssl_set(
    $db,
    NULL,
    NULL,
    '/etc/ssl/my-certs/ssl-ca.crt.pem',
    NULL,
    NULL
);
mysqli_real_connect(
    $db,
    'db.example.com',
    'john',
    '123456',
    NULL,
    NULL,
    NULL,
    MYSQLI_CLIENT_SSL
);

When reading the PHP documentation for mysqli::options, I noticed the existence of the MYSQLI_OPT_SSL_VERIFY_SERVER_CERT option, which I assume is an option to make MySQLi verify the server certificate. Unfortunately, there is no description of MYSQLI_OPT_SSL_VERIFY_SERVER_CERT in the documentation. The existence of this option makes me wonder if I have been connecting to MySQL insecurely. Now I'm wondering if the proper way to connect to MySQL securely is like this:

$db = mysqli_init();
mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);  // <- Attention.
mysqli_ssl_set(
    $db,
    NULL,
    NULL,
    '/etc/ssl/my-certs/ssl-ca.crt.pem',
    NULL,
    NULL
);
mysqli_real_connect(
    $db,
    'db.example.com',
    'john',
    '123456',
    NULL,
    NULL,
    NULL,
    MYSQLI_CLIENT_SSL
);

So, my questions are:

  1. Is MYSQLI_OPT_SSL_VERIFY_SERVER_CERT set to true by default?
  2. What does MYSQLI_OPT_SSL_VERIFY_SERVER_CERT do? (citations please)
  3. What is the proper (secure) way to connect to a remote MySQL database using MySQLi?

(Note: this is a follow-up question on What's the difference between MYSQLI_CLIENT_SSL and MYSQLI_OPT_SSL_VERIFY_SERVER_CERT?)

Marcin Orlowski
  • 72,056
  • 11
  • 123
  • 141
Flux
  • 9,805
  • 5
  • 46
  • 92
  • For you second question from [MariaDb](https://mariadb.com/kb/en/library/mysql_optionsv/) knowledge base MYSQL_OPT_SSL_VERIFY_SERVER_CERT: Enable (or disable) the verification of the hostname against common name (CN) of the server's host certificate. – pmarkoulidakis Jan 26 '19 at 23:18

1 Answers1

9

Requested answer

The truth is, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT has no effect. It is an unused constant. I just verified this by scanning the source code.

So, your question remains: Are MySQLi connections checking server certificates by default?

Short answer: Yes, they are.

Long answer: Although certificates are not matched against a list of commonly trusted Certificate Authorities, the provided CA (even if self-signed) is still verified on connection establishment to mitigate MITM-attacks.

Answer from engineering perspective

When connecting to a MySQL server, I would not recommend using SSL connections at all, as they add several layers of disadvantages (encryption, bandwidth, decryption, increased memory usage, increased overall roundtrip time). A much better approach is to connect within a trusted local network or use some type of well authenticated SOAP interface to retrieve and manipulate data if the server must be outside a local network by design (in which case the design just seems wrong).

Gerald
  • 955
  • 1
  • 8
  • 18
  • "... certificates are not matched against a list of commonly trusted Certificate Authorities ..." - If `MYSQLI_CLIENT_SSL` is set in `mysqli_real_connect(...)`, and you don't call `mysqli_ssl_set(...)` to explicitly provide the certificate to mysqli, I think mysqli automatically verifies certificates using `/etc/ssl/certs/ca-certificates.crt` (i.e. a commonly trusted certificate store in Debian/Ubuntu). – Flux Jan 29 '19 at 06:40
  • @Flux `MYSQLI_CLIENT_SSL` does nothing but enabling SSL. Check http://php.net/manual/en/mysqli.real-connect.php - it describes the flag as "Use SSL (encryption)". – Gerald Jan 29 '19 at 11:30
  • 1
    "well authenticated SOAP interface to retrieve and manipulate data if the server must be outside a local network by design" or REST, or any other API; just don't unnecceserly allow MYSQL to be accessible from the internet and keep your private network safe. Great advice on the fact that SSLifiyng everything is not going to solve all the problems with security. In fact, it's not even useful in a lot of cases. – Soroush Falahati Feb 01 '19 at 01:26
  • FWIW, at my company we use TLS (aka SSL) connections even though we're doing it inside a trusted local network. With strong servers, the overhead of encryption is not significantly costly. – Bill Karwin Feb 01 '19 at 06:42
  • @BillKarwin Understood, however using SSL within a trusted network is like distrusting your own trusted network, in which case it cannot be considered a trusted network, which in turn eliminates the whole point of this thought. SSL in an SQL server is just bad. Efficiency turns out real bad once you start to scale. Just don't do it, use a propper design. – Gerald Feb 03 '19 at 11:22
  • 3
    *Eppur si muove.* We are doing this at my company, on more than 5,000 MySQL instances, as well as all applications and microservices. It's a method of **defense in depth**, because on the small chance that there's a hacker who has broken into the local network — or if there's someone employed in the company with access to the network — the traffic between hosts cannot be intercepted. – Bill Karwin Feb 03 '19 at 16:19