[Howto] Connect Microsoft Azure MySQL with SSL

[stonez56]

Hi all,
This is my first post!
Thanks for this great PHP user management framework. 
It saved me a lot of time to management users in my PHP application!

For about a week, I tried to figure out how to connect MS Azure MySQL with SSL.
Finally, I got it worked.  And I am very happy to share this with you!

Software versions:

• UserSpice V4.4
  
• Azure MySQL 5.7.24-log (MySQL Community Server (GPL)
  
• OS: Win64
  

1) In /users/init.php,  update correct Azure Mysql User name, password, db name, and password:

Code:

// Set config
$GLOBALS['config'] = array(
    'mysql'      => array(
'host'         => 'yoursystem.mysql.database.azure.com',
'username'     => 'mysqladm@yoursystem',
'password'     => 'your_password',
'db'           => 'your_db',
),
'remember'        => array(
 'cookie_name'   => 'ejiasdf93asdfj',
 'cookie_expiry' => 604800  //One week, feel free to make it longer
),
'session' => array(
 'session_name' => 'user',
 'token_name' => 'token',
)
);

2) Download CA certifications here (BaltimoreCyberTrustRoot.crt.pem) from here and save it in your website.
   In my case, I saved this file at d:\home\site\wwwroot\




3) Then, in /users/classes/DB.php, 
     add two lines, in PDO connects as options around LINE 31:

    PDO::MYSQL_ATTR_SSL_CA => "d:\home\site\wwwroot\BaltimoreCyberTrustRoot.crt.pem",
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false

Code:

class DB {
    private static $_instance = null;
    private $_pdo, $_query, $_error = false, $_errorInfo, $_results=[], $_resultsArray=[], $_count = 0, $_lastId, $_queryCount=0;

    private function __construct($config = []){
        if (!$opts = Config::get('mysql/options'))
            $opts = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode = ''",

            PDO::MYSQL_ATTR_SSL_CA => "d:\home\site\wwwroot\BaltimoreCyberTrustRoot.crt.pem",
            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
                            
                            );
        try{
            if($config == []){
                $this->_pdo = new PDO('mysql:host=' .
                    Config::get('mysql/host') .';dbname='.
                    Config::get('mysql/db') . ';charset=utf8',
                    Config::get('mysql/username'),
                    Config::get('mysql/password'),
                    $opts);
            }......more codes.... below

Then, you should be able to connect to Microsoft Azure MySQL with SSL enabled! 

Hope this is helpful to you!

Stonez