How to use mysqli connection with SSL

Question

I'm trying to make a secure connection with my database

I wrote the following code:

<?php

// form filled?
if (isset($_POST['submit'])) {
    $user = 'gebruiker';
    $pass = 'gebruiker';
    $db = new mysqli('localhost', $user, $pass, 'forum');
    if (mysqli_connect_errno()) {
        echo 'database doesnt work';
        file_put_contents('MySQLiErrors.txt', date('[Y-m-d H:i:s]') .          mysqli_connect_error() . "\r\n", FILE_APPEND);
        exit();
    } else {
        $username = $_POST['username'];
        $userspassword = $_POST['password'];
        $salt = strrev($userspassword . substr(0, 4));
        $password = hash('sha512', $userspassword . $salt);
        $statement = $db->prepare("SELECT id,username FROM user WHERE username = ? AND password = ?");
        $statement->bind_param("ss", $username, $password);
        $statement->execute();
        $result = $statement->get_result();
        $statement->close();
        $count = $result->num_rows;
        if ($count > 0) {
            session_start();
            $_SESSION["username"] = $username;
            header("Location: forum.php");
        } else {
            $_SESSION['Error'] = "Invalid username or password";
        }
    }
    $db->close();
}

I also read something about SSL connections on php.net but I don't have any idea how to implement this in this case.

http://php.net/manual/en/mysqli.ssl-set.php

My code is running on fedora 21 and it works fine but the next thing I want is a secure connection using SSL.

Answer 1

You do NOT need the client certificate and private key and in most cases you do NOT want MySQL server to verify the client certificate.

Client however MUST verify server certificate using CA certificate to prevent MITM.

<?php
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$mysqli->ssl_set(NULL, NULL, "/etc/ssl/certs/ca-bundle.crt", NULL, NULL);
$mysqli->real_connect('hostname', 'user', 'password', 'database');
$mysqli->close();
?>

Answer 2

<?php
$con=mysqli_init();
if (!$con)
  {
  die("mysqli_init failed");
  }

mysqli_ssl_set($con,"key.pem","cert.pem","cacert.pem",NULL,NULL); 

if (!mysqli_real_connect($con,"localhost","my_user","my_password","my_db"))
  {
  die("Connect Error: " . mysqli_connect_error());
  }

// Some queries...

mysqli_close($con);
?>

connection - Required. Specifies the MySQL connection to use

---

key - Required. Specifies the path name to the key file

---

cert - Required. Specifies the path name to the certificate file

---

ca - Required. Specifies the path name to the certificate authority file

---

capath - Required. Specifies the pathname to a directory that contains trusted SSL CA certificates in PEM format

---

cipher - Required. Specifies a list of allowable ciphers to use for SSL encryption


SOURCE HERE

For mysql_real_connect use

<?php
ini_set ('error_reporting', E_ALL);
ini_set ('display_errors', '1');
error_reporting (E_ALL|E_STRICT);

$db = mysqli_init();
mysqli_options ($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

$db->ssl_set('/etc/mysql/ssl/client-key.pem', '/etc/mysql/ssl/client-cert.pem', '/etc/mysql/ssl/ca-cert.pem', NULL, NULL);
$link = mysqli_real_connect ($db, 'ip', 'user', 'pass', 'db', 3306, NULL, MYSQLI_CLIENT_SSL);
if (!$link)
{
    die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
} else {
    $res = $db->query('SHOW TABLES;');
    print_r ($res);
    $db->close();
}
?>