1

I'm connecting to my mysql database like this:

$mysqli = new mysqli('76.65.87.64', 'user', 'my_password', 'my_db');

I have 3 certificates to use for a secure SSL connection.

How do I use them with this connection?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Amy Neville
  • 10,067
  • 13
  • 58
  • 94
  • I'm not sure there is a scenario where connecting to a *local* database using SSL makes sense, are you really sure you need to do this? For general advice on using SSL with mySQL, there is https://dev.mysql.com/doc/refman/5.5/en/using-secure-connections.html (which also is the first Google result for `mysql ssl`, just sayin') – Pekka Jan 26 '16 at 00:31
  • 1
    I'm connecting through an IP to a remote sql instance. I apologise the localhost was a bit misleading. – Amy Neville Jan 26 '16 at 00:34
  • Ah, ok... for remote connections, there appears to be http://php.net/manual/en/mysqli.ssl-set.php – Pekka Jan 26 '16 at 00:35
  • I don't see any examples of how to write it in an object oriented style. And also they seem to be using a different connection function? – Amy Neville Jan 26 '16 at 00:36
  • 1
    Yeah, it seems like to do that, you have to use `mysql_init()` to create the `$mysqli` object (example [here](http://php.net/manual/en/mysqli.real-connect.php)), then prepare the cert (using `$mysqli->ssl_set(...)`) and then initiate the connection using `$mysqli->real_connect()`! – Pekka Jan 26 '16 at 00:40
  • What is the difference between $mysqli->real_connect() and $mysqli = new mysqli() though? I think that's what is throwing me... – Amy Neville Jan 26 '16 at 00:46
  • There doesn't seem to be any difference other than that with the former, you can do things like set options and add certificates before connecting. That's syntactically impossible with the `new mysqli()` method. – Pekka Jan 26 '16 at 09:50

1 Answers1

1

I am putting this answer here because I also encounter the same problem but already deep in the rabbit hole...

In my case, I am maintenance a legacy PHP server that uses mysqli in oop format like OP did.

$mysqli = new mysqli('123.123.123.123', 'user', 'my_password', 'my_db');

And now the requirement is to add SSL/TLS for such connection.

I have searched through the internet for mysqli oop format and ssl capability, sadly it seems like is not possible to do.

To add SSL/TLS, there are a few options to choose ( I choose option 1):

  1. Change the mysqli from oop format to procedual format, and add the SSL that way
    • From

      $mysqli = new mysqli('123.123.123.123', 'user', 'my_password', 'my_db');

    • To
            $mysqli  = mysqli_init();
            $mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
            $mysqli->ssl_set(NULL, NULL, "xxx.crt", NULL, NULL);
            $mysqli->real_connect('123.123.123.123', 'user', 'my_password', 'my_db');
  1. Change to use PDO
  2. Don't use SSL (not the best options)

I think the ideal method is to migrate to PDO (optoins 2), but based on the size of the project and time given we need to go with option 1.

To do that, we can use Regex for mass search and replace ( I used Visual Studio Code for the search and replace ):

  • Search:

    (\$[^-]+)->real_escape_string(([^)]+))

  • Replace:

    mysqli_real_escape_string($1,$2)

  • Search:

    (\$[^-]+)->query(([^)]+))

  • Replace:

    mysqli_query($1,$2)

One can also follow the above format to change up the other mysqli oop function used to mysqli procedural. Hope this helps someone, thanks.

Ng Sek Long
  • 4,233
  • 2
  • 31
  • 38
  • BTW, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT has no effect. See: https://stackoverflow.com/a/54414900/12818504 – acanbiler Aug 11 '22 at 19:55