Category: PHP and MySQL Web Development

Example Remote MySQL SSL Connections Using PHP: MySQLi and PDO

   |   By  |  No comments

Please understand that this was part of an exploration, and I never implemented this option. I was exploring remote access to MySQL. However, this may be helpful to others, and I wanted to share a bit of my experience and PHP code. I will most likely use autossh and SSH tunneling as the final solution. Here is a follow up post on how to use autossh and ssh tunneling for creating the connection.

Links That Were Helpful

Setup and understanding the integration with the remote MySQL server:

Speed comparisons between remote MySQL using SSH versus SSL. Understand that your performance will suffer:

Using ProxySQL for remote connections improves speed of MySQL using SSL.

MySQL Shell for Testing Connection Via Mac Terminal

Notes
I used OpenSSL to generate the certificates on the MySQL remote server.

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

// THE DATABASE USER SHOULDN'T BE THE ROOT USER, THIS IS NOT ALLOWED BY DEFAULT
$db_host = '';
$db_user = '';
$db_password = '';
$db_database = '';

//ALL PEM FILES ARE COPIED FROM THE REMOTE MYSQL SERVER AND CREATED WITH OPENSSL

// THE PDO VERSION
$pdo = new PDO('mysql:host=;dbname=', '< USER >', '< PASSWORD> ', array(
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
    PDO::MYSQL_ATTR_SSL_KEY    =>'/home/vagrant/client-ssl/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT=>'/home/vagrant/client-ssl/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA    =>'/home/vagrant/client-ssl/ca.pem'
));
$query = $pdo->prepare("SELECT * FROM wp_posts LIMIT 0, 10");
$query->execute();
$result = $query->fetchall(); // or you can just $result = $query as hakre proposed!
var_dump($result);

// THE MYSLQI VERSION
$db = mysqli_init();
if (!$db) {
    die('mysqli_init failed');
}

// THIS OPTION, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, HAS THE REMOTE SERVER VALIDATE THE CERTIFICATE
mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
// THESE ARE FROM THE MYSQL SERVER.
$db->ssl_set(
    '/home/vagrant/client-ssl/client-key.pem',
    '/home/vagrant/client-ssl/client-cert.pem',
    '/home/vagrant/client-ssl/ca.pem',
    null,
    null
);

if (!$db->options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
    die('Setting MYSQLI_INIT_COMMAND failed');
}
if (!$db->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5)) {
    die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}

// You may want to verify the certificate and not use the option: MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT
if (!$db->real_connect($db_host, $db_user, $db_password, $db_database, '3306', null, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT)) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

$result = mysqli_query($db, "SELECT * FROM wp_posts LIMIT 0, 10");
while ($row = mysqli_fetch_assoc($result)) {
    var_dump($row);
};

 

FeLion Studios

   |   By  |  No comments

Amplitude Design worked with FeLion Studios to develop a site that was both social and functional.

FeLion Studios, a Madison base art studio, was interested in selling their cast-iron art, and creating a social following for their regional events.

The success of the site led to top listings on Pinterest.com, Awesomer.com, Thrillist.com, WhyImBroke.com and other top blogging sites. To top it all off FeLion Studios was invited to participate on the Martha Stewart Show.

If you are interested in speaking with a web development or design expert click here.

Pediatrics PC

   |   By  |  No comments

Amplitude Design, Inc. is pleased to announce the launch of www.PediatricsPC.com. Pediatrics PC is a pediatric clinic located in Omaha, Nebraska. The site contains general office information as well as a means to notify patients of related childcare news.

The Site in Brief:

  • Content Management System (WordPress)
  • Email List Management
  • Feeds for Related Pediatric News
  • Billing and Administrative Functionality

Culligan 2011

   |   By  |  No comments

This is our second Culligan development project based upon the success of our prior work. Over five years ago Kennedy Communications attracted more then 100 Culligan dealers to use a template we designed specifically for Culligan. Five years later it was time to take a fresh look at the web site and work-flow process.

Amplitude Design, Inc. developed a new system to handle the day-to-day management, incorporating Kennedy Communications’ specific requirements, and created a more efficient architecture to address the accounts growth.

Portions of the projects included:

  • A Content Management System to handle the various web sites.
  • Consolidate the various web sites into one environment.
  • Develop a Custom Dealer Finder with Google Maps Integration
  • Develop a Custom Interface to Handle Online Communications

Example Web Sites:

www.minnesotaculligan.com

www.culligannorthernillinois.com

www.culliganchicago.com

This is one of many stories where our close client relationships have help grow businesses. If you could benefit from web site development please click here to contact us?

Woodland Futures

   |   By  |  No comments

Woodland Futures, LLC worked with Amplitude Design, Inc. to launch their first web site. The company focuses on offsetting carbon pollution through planting trees.

The site provides informational content and a variety of packaged options to promote carbon neutrality. As an adjunct to the system, the  site provides businesses with a Carbon Calculator, which calculates the appropriate number of trees to offset operations.

Amplitude Design, Inc. used WordPress to build the site. The site also uses PayPal for credit card processing.

After working with other web development companies, Woodland Futures, LLC chose Amplitude Design, Inc. because of our experience.

If you are interested in working with an experienced web development company click here to contact us?

USChampionCheese.org

   |   By  |  No comments

Amplitude Design, Inc. was pleased to work with the Wisconsin Cheese Makers Association on the development of the new site for the United States Championship Cheese Contest (www.uschampioncheese.org).

The site includes many capabilities designed to display all the associated information for a contest. This includes associated documents, information, contest results, news, sponsors, etc… We also developed an Authorize.net credit card checkout for contest entry payment. The user is provided a multi-page form on an SSL server, and is directed to Authorize.net for credit card checkout. This provides the user with a secure checkout and gives the Wisconsin Cheese Makers Association the information they require to host a successful event. The Executive Director of the Wisconsin Cheese Makers Association said after the event, in reference to this year’s paperless scoring, “it was a challenge for some of our judges and a joy in the end.”

To view photos of the 2011 United States Championship Cheese Contest click here.
To view the video from the 2011 United States Championship Cheese Contest click here.

If you could benefit from e-commerce solutions please don’t hesitate to contact us?