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.

since your are using your MySQL connection on the same server there is no need to use an SSL connection between php and your MySQL server ... are you looking to secure the connection between your end user and your web server? — cmorrissey, Mar 16 '15 at 18:37
you're connecting to `localhost`. that'll use a local unix-domain socket. There is no point in using ssl. SSL might be useful if you were doing a remote connection via TCP, but ssl-over-unixdomain? Rather pointless. — Marc B, Mar 16 '15 at 18:38
SHA is too fast for passwords. Use bcrypt or scrypt or PBKDFv2. — SLaks, Mar 16 '15 at 18:39
Deriving the salt from the password completely defeats the purpose of salt. — SLaks, Mar 16 '15 at 18:39
Thanks for the feedback @SLaks but the issue here is that I want to know how to make an ssl connection — NielsDePils, Mar 16 '15 at 18:54

score 25 · Answer 1 · answered Jun 23 '18 at 08:05

25

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

answered Jun 23 '18 at 08:05

hmiller

351
3
3

1

This is one of the most helpful answers I've seen - the documentation is poor (as far as I can tell - perhaps my issue with not finding what's out there). The issue of certificate validation (server) and ignoring client is spot on. And seems to answer the OP exactly. Thanks! – Mark Bradley Sep 22 '18 at 09:47
1

Is `$mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);` necessary? That option does not seem to be [documented](https://secure.php.net/manual/en/mysqli.options.php). Relevant question: https://stackoverflow.com/questions/54299690/does-mysqli-verify-server-certificates-by-default-when-using-ssl – Flux Jan 25 '19 at 09:35

Anri · Answer 2 · 2015-03-16T18:54:41.363

8

<?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();
}
?>

edited Mar 16 '15 at 18:54

answered Mar 16 '15 at 18:48

Anri

1,706
2
17
36

2

I hope you know what is key.pem and cert.pem files – Anri Mar 16 '15 at 18:49
Yes I'm familiar with those. But what about sql injection if you use mysqli_real_connect? – NielsDePils Mar 16 '15 at 18:52
where do you use the prepared statement on the $link or on the $db? – NielsDePils Mar 16 '15 at 18:58
what do you mean "where do you use" – Anri Mar 16 '15 at 19:00
The prepared statements of the query – NielsDePils Mar 16 '15 at 19:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/73099/discussion-between-themaster-and-anri). – NielsDePils Mar 16 '15 at 19:06
Your example(s) helped tremendously. Thank you. – a coder Feb 10 '17 at 19:14
Is `mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);` necessary? That option does not seem to be [documented](https://secure.php.net/manual/en/mysqli.options.php). Relevant question: https://stackoverflow.com/questions/54299690/does-mysqli-verify-server-certificates-by-default-when-using-ssl – Flux Jan 25 '19 at 09:37

score 0 · Answer 3 · answered Jun 09 '23 at 14:54

Little easy way convienance to actucaly mysqli

<?php  namespace mysqlConnect;


class mymysqli extends \mysqli {


        
        public function __construct($db_host, $db_user, $db_pass, $db_name, $persistent = true, $ssl = false, $certpublic = "") {
            //$this->close();
            parent::init();
            //parent::options(MYSQLI_OPT_CONNECT_TIMEOUT, 1);
            if($ssl) {
                parent::options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, false);
                parent::ssl_set(NULL, NULL, $certpublic, NULL, NULL);
            }
            @parent::real_connect(($this->persistent ? 'p:' : '') . $this->db_host, $this->db_user, $this->db_pass, $this->db_name);

        }
}

How to use mysqli connection with SSL

3 Answers3

Linked

Related