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

From what I understand, the MYSQLI_CLIENT_SSL flag is necessary to make mysqli::real_connect connect to the server using SSL.

Today I stumbled upon the documentation for mysqli::options, and noticed that it accepts MYSQLI_OPT_SSL_VERIFY_SERVER_CERT as an option, but, alas, its description is blank. So, I wonder:

  1. When do I need to add mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);?
  2. When do I need to use the MYSQLI_CLIENT_SSL flag?
  3. When will I need to set both of them?
Flux
  • 9,805
  • 5
  • 46
  • 92

1 Answers1

5

  1. MYSQLI_OPT_SSL_VERIFY_SERVER_CERT (true) used when you want to verify server certificate against well known authorities to ensure that this is connection to trusted host. Do not use it if you have self-signed certificate on server.

  2. MYSQLI_CLIENT_SSL must be always used when you need to encrypt connection.

  3. When you have on mysql-server certificate provided by authorities and want encryption + MITM-attack protection use both MYSQLI_OPT_SSL_VERIFY_SERVER_CERT and MYSQLI_CLIENT_SSL.

Alexander Yancharuk
  • 13,817
  • 5
  • 55
  • 55
  • Suppose I omit `mysqli_ssl_set(...)` and instead, I add a self-signed CA certificate to `/usr/local/share/ca-certificates/`, and run `sudo update-ca-certificates` to add my self-signed certificate to the trusted store. Can I set `MYSQLI_OPT_SSL_VERIFY_SERVER_CERT` to `true`? – Flux Jan 06 '19 at 14:45
  • You *can* verify the server certificate even if you use self-signed certs, you just need to add that cert to the correct trust store. “Well known authorities” are only well-known because they’re in that trust store by default. – deceze Jan 06 '19 at 14:53
  • @Flux You need to run `sudo update-ca-certificates` on every host where your script supposed to run. This is not good idea. – Alexander Yancharuk Jan 06 '19 at 15:05
  • 1
    Is `MYSQLI_OPT_SSL_VERIFY_SERVER_CERT` set to `true` by default? – Flux Jan 06 '19 at 15:21
  • @Flux I think this is another interesting question with a high voteup potential. Vote for answer and create another one :) – Alexander Yancharuk Jan 20 '19 at 03:16
  • @AlexanderYancharuk Follow-up question about `MYSQLI_OPT_SSL_VERIFY_SERVER_CERT`: https://stackoverflow.com/questions/54299690/ – Flux Jan 22 '19 at 00:35
  • 1
    Where did you get the information regarding `MYSQLI_OPT_SSL_VERIFY_SERVER_CERT`? – Flux Jan 24 '19 at 13:13
  • @Flux Information about `MYSQLI_OPT_SSL_VERIFY_SERVER_CERT` from PHP `mysqli` documentation https://www.php.net/manual/en/mysqli.options.php – Alexander Yancharuk Aug 03 '21 at 10:44
  • @AlexanderYancharuk Isn't that the same webpage that I already linked to in the question body? – Flux Aug 04 '21 at 05:10