Connect to remote MySQL server with SSL from PHP

by

“Unfortunately I can’t use mysqli lib because have too many working adapters for pdo_mysql.”

You’re using the old MySQL extension (“mysql_connect”), which is no longer under development (maintenance only). Since you’re using PHP 5, you may want to use MySQLi, the MySQL Improved Extension. Among other things, it has an object-oriented interface, support for prepared/multiple statements and has enhanced debugging capabilities. You can read more about converting to MySQLi here; more about the mysqli class itself here.

Here is some sample code that may help you get started:

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

If PDO_MYSQL is really what you want, then you need to do something like this:

<?php
$pdo = new PDO('mysql:host=ip;dbname=db', 'user', 'pass', array(
    PDO::MYSQL_ATTR_SSL_KEY    =>'/etc/mysql/ssl/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT=>'/etc/mysql/ssl/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA    =>'/etc/mysql/ssl/ca-cert.pem'
    )
);
$statement = $pdo->query("SHOW TABLES;");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);
?>

However, only recent versions of PHP have SSL support for PDO, and SSL options are silently ignored in (at least) version 5.3.8: see the bug report.

Good luck!

More Related Contents:

  1. PHP to MySQL SSL Connections
  2. While loop inserts three times
  3. Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in? [duplicate]
  4. regarding installation of php in windows
  5. save user images in MYSQL database by which method? [closed]
  6. How to apply bindValue method in LIMIT clause?
  7. Row count with PDO
  8. Best way to INSERT many values in mysqli?
  9. php/mysql with multiple queries
  10. Notice: Array to string conversion in
  11. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  12. Find total number of results in mySQL query with offset+limit
  13. mysql_fetch_array() expects parameter 1 to be resource problem [duplicate]
  14. PDO multiple queries
  15. How to efficiently find the closest locations nearby a given location
  16. Turn database result into array
  17. GMail fsockopen(): SSL operation failed error with Codeigniter and XAMPP
  18. How to set up entity (doctrine) for database view in Symfony 2
  19. Update query with PDO and MySQL
  20. (mysql, php) How to get auto_increment field value before inserting data?
  21. What is the type this string? a:1:{s:2:”en”;}
  22. return one value from database with mysql php pdo
  23. How can I pass an array of PDO parameters yet still specify their types?
  24. MySQL & PHP Parameter 1 as Resource
  25. How can I do a migration in laravel 5.5?
  26. Best way to store IP in database?
  27. Which is fastest in PHP- MySQL or MySQLi?
  28. Is mysql_real_escape_string enough to Anti SQL Injection?
  29. How can I list data in sections by an ID, with a while loop in PHP? [closed]
  30. find in set in laravel ? example

Leave a Comment