Configuring MySQL SSL in Magento (to get your HIPAA auditor off your back)

Leave a comment
mysql-magento-openssl

I’ve been asked a few times now if there is a way to use encrypted MySQL connections in Magento.  Most of the time it is when merchants are selling medical products and HIPAA requirements come into play.  I am not an expert in HIPAA, nor do I want to be, but with the cost of vulnerabilities on the rise it made sense to at least look into it and get a good answer on how to do it.

The answer, to my surprise, is that there is no way of doing it out of the box.  The reason is actually very, very simple.  The PDO options that allow you to initiate SSL on MySQL are passed in as numerical values.  For example, the value for PDO::MYSQL_ATTR_SSL_KEY is 1007.  Shouldn’t be a problem, right?  Just pass in a numerical value for the config.

That works in the Zend Framework adapter, but it does not work for Magento.  All database configurations are stored in the local.xml file and the XML specification does not allow numbers for XML node names.  So no matter how you try to slice it it looks like getting the SSL settings into the Magento adapter will not work without a code change.  The Internet seems to confirm this.

But that doesn’t mean that it can’t be done.  So  I wrote a quick Magento adapter that allows you to pass in the constant values.  The implementation is about 10 lines of code and it’s super easy to test.

  1. Download the code from github.
  2. Configure MySQL – Note that when you create the server certificate you MUST use the IP address or hostname that you will be using in your Magento configuration
  3. Configure Magento

For step 3 first copy the Github code into your Magento installation.  Then modify your configuration such as this

<config>
 <global>
   <resources>
     <default_setup>
       <connection>
         <host><![CDATA[127.0.0.1]]></host>
         <username><![CDATA[root]]></username>
         <password><![CDATA[]]></password>
         <dbname><![CDATA[magentoee_114]]></dbname>
         <initStatements><![CDATA[SET NAMES utf8]]></initStatements>
         <model><![CDATA[mysql4]]></model>
         <type><![CDATA[secure_pdo_mysql]]></type>
         <pdoType><![CDATA[]]></pdoType>
         <active>1</active>
         <secure_driver_options>
           <MYSQL_ATTR_SSL_KEY>/etc/mysql-ssl/client-key.pem</MYSQL_ATTR_SSL_KEY>
           <MYSQL_ATTR_SSL_CERT>/etc/mysql-ssl/client-cert.pem</MYSQL_ATTR_SSL_CERT>
           <MYSQL_ATTR_SSL_CA>/etc/mysql-ssl/ca.pem</MYSQL_ATTR_SSL_CA>
         </secure_driver_options>
       </connection>
     </default_setup>
   </resources>
 </global>
</config>

The main difference is the node secure_driver_options.  Internally, what the driver does is append that to PDO:: and gets the value of the constant.  Then it adds it to the driver_options configuration array (notably absent since this adapter overwrites it) using the numerical values of the constants instead of node names.

Run the test script secure.mysql.test.php in your browser (or CLI).

There you go.

Note

  1. This is not an officially sanctioned Magento code example
  2. I have not tested it for performance, security, gremlins.
  3. I have built this as a proof of concept only
  4. Use at your own risk.
  5. If you find issues in the code, or a better implementation, feel free to contribute or let me know

We don’t need better authentication

2 Comments
1423854070034

1423854070034

I saw a tweet today concerning authentication.


When reading that the first thing that came to my mind was “with what?”  When will that one be hacked and then replaced by something else, which will then be hacked and replaced by something else?  For all of its faults a good password is already stored in the most secure storage repository around; your brain.

The problem, however, is that our brain is really good at remembering concepts, abstractions, gists, it is horribly bad at fine precision details.  When it comes to identity, I know who I am and you know who I am (if you know me).  On the other hand, computers are really good with precision information but really, really bad at concepts, abstractions and gists.  And so when we identify ourselves to the computer we have to stoop to its level.  And so we need to continually improve our techniques to identify ourselves to these dumb machines.

Multi factor authentication is a good thing to examine.  However, it has a flaw.  It requires two or more means of authenticating yourself.  You need something you know (a passcode), something you have (a phone) and/or something you are (biometrics).  This is all good stuff.  It really is.  But what if you were out to dinner and you were about to pay and you realized you forgot your phone (because you were so engrossed in the conversation that you even forgot to check email or to Like your accompanying’s checkin at said restaurant).  What biometrics would you use to authenticate yourself?  Eye?  Biochip?  Anyone seen Demolition Man?


“Would you leave me alone, I’m trying to go to the bathroom here!”

Perhaps what we should be doing as well as looking to increased authentication criteria is building a system that is expected to fail.  So if a company is storing credit card information and it leaks into the public have a second methodology that invalidates fraudulent activity and resets the account.  Have the proper security, but also know that security alone is insufficient for dealing with the modern world.

In truth I don’t know what that would look like or how it would operate.  But I don’t think that the problem of identity management is going to be solved by providing more complex authentication methods.  I honestly think that we need to presume failure in security as one of the layers of defense in depth.

Note, again, that my title is link-bait.  Yes, we do need better authentication, but we also need better corruption recovery methods.


Is prevention the best security practice?

1 Comment

I read a post tweeted by Chris Cornutt today.  The basic gist of the article is that your security is only as strong as your most ethically-challenged developer.  That got me thinking that we spend so much time trying to prevent intrusions when detection might be a better priority.  Some tactics, such as SQL Injection, are useful because they protect not just against intruders but people who tend towards single-quote usage as well.  I would argue that SQL Injection is just as much about inadvertent data entry as it is about security.  Same thing with XSS.

But this also got me thinking about laws.  We tend to (wrongly) view laws as a preventative measure.  The problem is that there are always people who are willing to skirt the law, whatever that law may be.  Sometimes it’s because laws are unjust.  But who is to decide when the perceived unjust-ness of a law is sufficient to permit civil disobedience?  Or the rejection of that law by an individual?

But what if we (getting back to developers) worked under the presumption that our code would be attacked and security would be defeated?  If we presume that our software is vulnerable does it make more sense to lock it down as much as we can, or implement methods to detect, or at least collect, information in a way to make prosecution or recovery easy.  Just like you cannot write a law to prevent all people from wrongdoing you cannot guarantee that your code is 100% secure.  Given that, would it work to take an approach that focused more on detection (and recovery) in front of prevention?

Would our approach be different?

What would it look like?

Would it work?

Would it matter?

It may sound a little silly to ask but consider that banks do something like this when it comes to financial transactions.  Banks use eventual consistency to maintain financial records.  They are not ACID compliant.  It is possible to overdraw your account if you do it in a manner that beats out the eventually consistent implementation they use.  It is the only way to maintain the scale that they require.  The position of the banks is that IF a circumstance occurs where there is a discrepancy in bank records it costs them less to fix the issue than to prevent it in the first place.

Likewise, Amazon allows items to be sold when they aren’t sure about stock (just look at a recent purchase of mine).  Their presumption, presumptively, is that it will cost them more to ensure completely accurate inventory management than to send an apology letter to a waiting customer.  Is there a correlation in software development when it comes to security?

I don’t have any answers ATM, and it may be that any implementation may end up being more costly than prevention (my current thought is that it is).  I’m just thinking out loud and wondering if anyone else has given though to this.


10 “what to do’s when setting up Magento” and file inclusion attacks

Leave a comment

Found this list of things “to do” on Twitter this morning.  I went over the list and saw that there was one item that was missing, which I feel is very important to do.  I saw it in another post on Local File Inclusion for which it seems like there was a local file inclusion vulnerability in Joomla (I think.  I didn’t read that far into it).

The thing on the list that was missing was securing your local file system when installing Magento.  The default installation asks for certain directories to be writable.  This is necessary for certain things.  But what we lazy installers sometimes do is just make the whole thing writable to make installation easier.  And while I am not aware of any specific Magento vulnerability like the one noted it is definitely a good practice to deny write access to all but the necessary files.  This is done by changing the permission settings on the files but changing the file ownership so that the web server user is unable to change the permissions to something more permissive.  And for the files that you need write access to you should deny access via either .htaccess or <directory> settings in httpd.conf so they can’t be called remotely.

So, the 11th thing to do is to secure your file system by denying write access to the server user that is running your Magento code.


Generating secure cross site request forgery tokens (csrf)

42 Comments

I don’t talk much about security.  This is mostly because it’s such a moving target.  I’m also horrified that I might give bad advice and someone will be hacked because of me.

But in researching the second edition for the IBM i Programmer’s Guide to PHP Jeff and I decided to include a chapter on security since we really didn’t talk much about it in the first edition.  I’m talking about cross site request forgeries right now and I wanted to make sure that what I was going to suggest would not break the internet in some way.

I did some Google searching to see what other people were recommending.  Almost all of the pages I found for generating a CSRF token use code like this

1
$token = md5(uniqid(rand(), true));

On the pages for rand() and uniqid(), as well as looking at the C code, they specifically state that these functions should not be used for generating secure tokens.  They tend to generate predictable values.  And the documentation for md5() states that it should not be used for password hashing.  Granted we’re not hashing passwords when creating a CSRF token, but with the tooling available shouldn’t we be using functions that are more cryptographically secure?  Like this?

1
2
3
4
5
$token = hash_hmac(
    'sha512',
    openssl_random_pseudo_bytes(32),
    openssl_random_pseudo_bytes(16)
);

Am I missing something or wouldn’t something like this be a whole lot better?

[UPDATE]

padraicb validated my thought on the matter.  The goal here is the random value.  As such the hashing using hash_hmac() does not buy you a whole lot extra.  The number of possible values in a 32 byte random string is 1.1579208923731619542357098500869e+77.  That alone would seem to be enough for a CSRF prevention token.  mt_rand() returns an integer which gives you  about 4 billion possible numbers.  While that will probably protect you, the other value will offer you better protection.  There’s no sense in gambling with a smaller value if you have the ability to generate a larger value with virtually no additional cost.

So it would seem that, for generating a proper token the code that you would really need is this

1
$token = base64_encode( openssl_random_pseudo_bytes(32));

The only reason for the base64_encode() call is to make sure that the value provided will not break your HTML layout.


How to use PHP with MySQL (without SQL Injection vulnerabilities)

1 Comment

Chris Dale recently posted a horrifying article on his blog.  It is called “Why it’s easy being a hacker – A SQL injection case study“.  The most horrifying part of the post was that when you type in the Google search “How to use PHP with MySQL” a significant number of the results come back with some VERY poor examples.  Most of them were vulnerable to SQL injection.  That means that people new to PHP are getting really, really bad advice on how to connect to the database.

With that in mind I want to give a very brief tutorial on how you really should be connecting to MySQL.  It will use only native PHP code and will contain no application architecture examples.  It will also not expound upon larger security issues.  It will simply give you a good starting point for using PHP with MySQL.

My hope in writing this blog post is that enough people will link to this page so-as to increase its page rank so that when people search for “how to use php with mysql” that it (or something like it) will be pre-eminent.  In some ways, my purpose in writing this page is simply Google-bait.

With that in mind, please, please promote this page on your blog, Twitter, Google+, etc.  There are a lot of really bad examples of PHP example code which contains SQL injection vulnerabilities and the only way to negate that is to educate people on the proper way to do it.

What to use

There are three different extensions you can use.  mysql, mysqli and pdo_mysql.  The vanilla mysql extension is deprecated and will be removed.  So if you are using function calls that start with mysql_ then you are using the wrong library.  You should use either the mysqli API or the pdo_mysql API.  PDO places a database abstract layer in between your code and the database driver which makes your code more portable.  It is not perfect in that it does not take care of vendor SQL differences but it does remove the API as a consideration if you need to migrate your database, for the most part.

So for the purpose of this article I will focus only on the PDO driver.  Additionally, there are several different sources for how to connect to MySQL, create tables and execute queries.  For that reason I will focus solely on PHP as an interface and not worry too much about actual SQL.

Setting up PHP

It is recommended that you use the new mysqlnd driver for providing MySQL access.  It adds a lot of new features like lazy connections and query caching.  To compile your PHP with mysqlnd make sure that you configure your source distribution like this

1
./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd

Connecting to the database

To connect to the database you will need to create a new PDO object.  This is done with the following code.

1
2
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');

The first argument is called the DSN.  It is a connection string that defines various options for PDO, including which driver to use, in this case mysql.  The second argument is the username to connect to and the third is the password.

You should generally not hard code these values into your application.  They should be retrieved from some configuration source so you don’t need to redeploy your application or edit live code to make a DSN change.

Inserting and updating data

A database is useless without data. In many articles inserting data is done with code similar to this

1
2
3
<?php
 
mysql_query("INSERT INTO `data` VALUES ('$name', '$email', '$location')");

This is wrong.  Horribly wrong.  This code is vulnerable to SQL injection.  Me, as an attacker, can use this code to execute arbitrary SQL statements on your system.

There are two ways to do this right.  One is to use the function mysqli_real_escape_string.  The other is to use prepared statements.  In my opinion prepared statements are much preferred for the following reasons

  1. Statements can be re-used without re-parsing SQL (escaping properly is better, but not best, IMHO)
  2. Prepared statements send data outside of the SQL statement via a binary protocol

 

Using a prepared statement is very easy.  You pass the driver an SQL string but you omit the data, providing placeholders instead.  Because there is no user data being provided an attacker cannot manipulate your SQL statement.

1
2
3
4
5
6
7
8
9
10
<?php
 
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('INSERT INTO customers (name, address) VALUES (?, ?)');
$statement->execute(
  array(
    $_POST['name'],
    $_POST['address']
  )
);

When prepare() is called the statement is not actually executed.  It has no data to work with.  The data is only inserted into the database when the execute() method is called.

If you like, you can also provide named parameters.  In the previous example, the values inserted will correspond to the position in the array.  Using named parameters you can simply specify the placeholder names and not have to worry about the order.

1
2
3
4
5
6
7
8
9
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('INSERT INTO customers (name, address) VALUES (:name, :address)');
$statement->execute(
  array(
    'address' => $_POST['address'],
    'name' => $_POST['name']
  )
);

Updating is done in virtually the exact same way, except with an UPDATE query.

1
2
3
4
5
6
7
8
9
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('UPDATE customers SET name = :name WHERE name = :oldname');
$statement->execute(
  array(
    'oldname' => $_POST['oldname'],
    'name' => $_POST['name']
  )
);

Retrieving data

Retrieving data is done via a SELECT call.  To start, do the same thing that you did before by creating a prepared statement.  However, this time we are going to iterate over the result set using the fetch() method.  You have four different options when retrieving data.  You can use fetch() to fetch a row, fetchAll() to fetch all rows in a multi-dimentional array, fetchColumn() to only get data for an individual column or fetchObject() to retrieve the row as an object of a specific type, or of stdClass if no type is provided.

I will only show fetch().

1
2
3
4
5
6
7
8
9
10
11
12
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('SELECT address FROM customers WHERE name = ?');
$statement->execute(
  array(
    'Kevin Schroeder'
  ) 
);
 
while(($data = $statement->fetch()) !== false) {
  echo htmlspecialchars($data['address']) . '<br />';
}

However, there is a bit of a problem.  MySQL does not allow you to pass certain things in as a prepared statement parameter, such as a LIMIT clause.   The variables that you can provide are generally limited to data.  Because of that you may need to take some care in constructing prepared SQL statements that do require “inline” user-submitted data, like a LIMIT clause.  Thankfully, that can easily be taken care of with casting.

1
2
3
<?php
$limit = (int)$_POST['limit'];
$statement = $pdo->prepare('SELECT address FROM customers WHERE name = ? LIMIT ' . $limit);

You will note that I put the LIMIT value in a separate variable.  I do this because it makes for easier debugging.  It is tough to use a debugger if you put data transformations in a method calls since it will not show up in your variable list.

Deleting data

Deleting data is pretty much the same as selecting data, but you don’t have a record set to iterate over.

1
2
3
4
5
6
7
8
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('DELETE FROM customers WHERE name = :name');
$statement->execute(
  array(
    'name' => $_POST['name']
  )
);

Related Links

Conclusion

This blog post is not meant to give a full introduction to SQL operations on PHP, nor is it intended to showcase secure PHP programming practices.  Rather, it is intended to showcase basic PHP/SQL usage in a manner that is consistent with security best practices… unlike the other billion “PHP and MySQL” pages out there.  If there is something I’ve missed, or said incorrectly, please feel free to leave a comment.

Also, please share this page as much as possible so that its page rank is increased.  Also, if you have good links to highlight how to properly work with SQL please leave them in the comments and I will put them in the related links section.  If people really are getting lots of bad PHP/MySQL info let’s see if we can raise the tide of the good examples over the bad ones.

 


Encrypted session handler

15 Comments

[UPDATE]  Enrico Zimuel has a better version of this[/UPDATE]

A little while ago I had come upon the problem of having to store sensitive data in a user session.  The solution that I (and several others came upon) was creating a mechanism for storing encrypted data in a session.  But what we wanted to do was build something that didn’t have a single point of failure.  We also wanted to build something portable.  What we built was a simple Zend Framework session handler for storing sensitive data.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
class EncryptedSession extends Zend_Session_Namespace {
 
    const CIPHER = MCRYPT_3DES;
    const MODE = MCRYPT_MODE_CBC;
 
    private $_iv;
 
    public function __construct($namespace = 'Default', $singleInstance = false)
    {
        parent::__construct($namespace, $singleInstance); // Must be true because of iv
 
        $storeKey = __CLASS__ . '_Data' . '_' . $namespace;
 
        if (!isset($_COOKIE[$storeKey]) || !isset($this->secretKey)) {
                $this->unsetAll();
 
                $maxKeySize = mcrypt_get_key_size(self::CIPHER, self::MODE);
 
                $secretKey = '';
                while( strlen($secretKey)<$maxKeySize) {
                    $secretKey .= dechex(uniqid(mt_rand(), true);
                }
                $this->secretKey = substr($secretKey, 0, $maxKeySize);
 
                $iv_size = mcrypt_get_iv_size(self::CIPHER, self::MODE);
 
                $this->_iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);
 
            $hmac = hash_hmac('md5', $this->_iv, $this->secretKey);
            $unique = base64_encode($this->_iv);
            // The cookie has the same parameters as the session cookie
            $cookie_param = session_get_cookie_params();
            setcookie(
                $storeKey,
                $hmac.$unique,
                $cookie_param['lifetime'],
                $cookie_param['path'],
                $cookie_param['domain'],
                   $cookie_param['secure'],
                   $cookie_param['httponly']
            );
        } else {
            $hmac = substr($_COOKIE[$storeKey],0,32);
            $unique = base64_decode(substr($_COOKIE[$storeKey],32));
            $check = hash_hmac('md5',$unique,$this->secretKey);
            if($hmac !== $check) {
                 throw new Zend_Session_Exception('Invalid Session Data');
            }
            $this->_iv = $unique;
 
        }
    }
 
    public function setEncrypted($key, $value)
    {
        $this->$key = bin2hex(
            mcrypt_encrypt(
                self::CIPHER,
                $this->secretKey,
                $value,
                self::MODE,
                $this->_iv
            )
        );
    }
 
    public function getEncrypted($key)
    {
        if (isset($this->$key)) {
            $decrypt = mcrypt_decrypt(
                self::CIPHER,
                $this->secretKey,
                pack(
                    'H*',
                    $this->$key
                ),
                self::MODE,
                $this->_iv
            );
            return rtrim($decrypt, "�"); // remove null characters off of the end
        }
        return null;
    }
}

 

 

What this does is allow you to transparently store encrypted data in a session.  Because it’s encrypted, someone hacking in to the server, say via an include vulnerability, would be able to read the session data, but not decrypt it.  That’s because the initialization vector is stored in a cookie on the browser.  So in order to decrypt the session an attacker would need to do both an XSS and a remote code injection attack.