Solved

mysql Encryption with PHP

Posted on 2016-11-03
8
51 Views
Last Modified: 2016-11-13
Anyone have some good resources and basic tutorials on how to encrypt a mysql database at rest and access it via PHP?
0
Comment
Question by:Stephen Forlance
  • 5
  • 3
8 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41872626
What do you want to encrypt?  If it's passwords, there is an article here at E-E showing password hashing.
https://www.experts-exchange.com/articles/28768/Password-Hashing-in-PHP.html

If you want to encrypt for secure communications, you can use HTTPS to transport the information.

Ir you want to encrypt the information in the tables, you can use something like this.
<?php // demo/encrypt_decrypt_mcrypt.php
/**
 * Note: MCrypt is sometimes considered "too easy" in year 2015+
 * but the PHP documents are incomplete for OpenSSL
 *
 * Show how to encrypt and decrypt information
 * with binary-safe transport over the internet
 * Note: ECB may not be the "best" mode, YMMV
 *
 * http://php.net/manual/en/book.mcrypt.php
 * http://php.net/manual/en/ref.mcrypt.php
 * http://php.net/manual/en/mcrypt.ciphers.php
 *
 * https://en.wikipedia.org/wiki/Advanced_Encryption_Standard
 * https://en.wikipedia.org/wiki/Base64
 *
 * Parallel construction in the encrypt() decrypt() methods
 */
error_reporting(E_ALL);


/**
 * The Interface defines the two main data transformation activities
 */
Interface Encryption_Interface
{
    public function encrypt($text);
    public function decrypt($text);
}

class Mcrypt_Encryption Implements Encryption_Interface
{
    protected $key;

    public function __construct($key='quay')
    {
        // THE KEY MUST BE KNOWN TO BOTH PARTS OF THE ALGORITHM
        $this->key = $key;
    }

    public function encrypt($text)
    {
        // DECLOP WHITESPACE BEFORE ENCRYPTION
        $text = trim($text);

        // ENCRYPT THE DATA
        $data = mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $this->key, $text, MCRYPT_MODE_ECB);

        // MAKE IT base64() STRING SAFE FOR STORAGE AND TRANSMISSION
        return base64_encode($data);
    }

    public function decrypt($text)
    {
        // DECODE THE DATA INTO THE BINARY ENCRYPTED STRING
        $text = base64_decode($text);

        // DECRYPT THE STRING
        $data = mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $this->key, $text, MCRYPT_MODE_ECB);

        // DECLOP NUL-BYTES BEFORE THE RETURN
        return trim($data);
    }
}


// INSTANTIATE AN ENCRYPTION OBJECT FROM THE CLASS
$c = new Mcrypt_Encryption();

// INITIALIZE VARS FOR LATER USE IN THE HTML FORM
$encoded = $decoded = NULL;

// IF ANYTHING WAS POSTED SHOW THE DATA
if (!empty($_POST["clearstring"]))
{
    $encoded = $c->encrypt($_POST["clearstring"]);
    echo "<br/>{$_POST["clearstring"]} YIELDS ENCODED ";
    var_dump($encoded);
}

if (!empty($_POST["cryptstring"]))
{
    $decoded = $c->decrypt($_POST["cryptstring"]);
    echo "<br/>{$_POST["cryptstring"]} YIELDS DECODED ";
    var_dump($decoded);
}


// CREATE THE FORM USING HEREDOC NOTATION
$form = <<<FORM
<form method="post">
<textarea name="clearstring">$decoded</textarea>
<input type="submit" value="ENCRYPT" />
<br/>
<textarea name="cryptstring">$encoded</textarea>
<input type="submit" value="DECRYPT" />
</form>
FORM;

echo $form;

Open in new window

0
 

Author Comment

by:Stephen Forlance
ID: 41873009
Thanks Ray.

I noticed the comment regarding OpenSSL, does that mean I couldnt use it? Just that I saw some notes stating the mcrypt was no longer considered secure
0
 

Author Comment

by:Stephen Forlance
ID: 41873014
Also, and I suspect this is a problem for any solution, but assuming the encryption is at the application level so only the php scripts have the key, how could it be secured?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41873119
Sure, you can use OpenSSL.  I just found it hard to research at the time I wrote this teaching example.  And whether MCrypt is secure is not really a binary question.  Security is like a fire safe.  These things are rated on the basis of time and temperature.  No protection is absolute and forever.  It's just a matter of time before the contents are incinerated.  The nature of your attackers, their financial resources, their commitment to the attack, the value of the attack -- these are the kinds of things that come into play when we evaluate security risk.

With regard to encryption at the application level, I don't understand the question.  Whether or not the PHP scripts have the key, what's the attack vector you're trying to protect against?  If you lose your PHP scripts that contain the passwords or similar credentials, you've got a risk.

FWIW, IT Security is a full-time, four year college major at the University of Maryland (home to OWASP), and the University offers post-graduate studies as well.  We can answer questions here at E-E, but we can't begin to cover all the issues and nuance in a deep, wide, and ever-evolving attack surface.  OWASP is your friend!
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Stephen Forlance
ID: 41875206
Hi Ray,
Im just thinking of a last line of defence, is the database was compromised and the content stolen, preventing its easy use,

Thanks,
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41875219
I think almost any encryption will prevent easy  use.  The definition of easy  may be a moving target, and will depend on the sophistication of the attackers and their perceived value of the data.  I would not put much effort into stealing a database of bowling scores, but if I could get a large volume of detailed financial information, it might be worth trying some things that were not easy.

If you want to go with OpenSSL, it looks like the PHP docs are much better now.  Here's the anchor link.
http://php.net/manual/en/book.openssl.php

I've never done it, but if you wanted to protect the keys or other security-related secrets, you might omit this information from the PHP scripts and instead ask the client to enter this information at run time, through HTML/PHP form input controls.  Some of the literature suggests putting the keys into a file on the file system, outside of the www-root tree.  At run time you can read them into a variable with file_get_contents(), and you can use the variable in your PHP scripts to provide the keys.  Since the file with the keys is outside of the web site, it cannot be discovered by "accidental" browsing.  And even if someone knew its name, they could not use an HTTP request to get the server to disclose the contents.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41875671
I've experimented with OpenSSL a little bit this afternoon.  Here are some of the references.
http://php.net/manual/en/book.openssl.php
https://en.wikipedia.org/wiki/Advanced_Encryption_Standard
https://en.wikipedia.org/wiki/Authenticated_encryption
https://en.wikipedia.org/wiki/Galois/Counter_Mode
https://en.wikipedia.org/wiki/Base64
https://bugs.php.net/bug.php?id=67304
https://moxie.org/blog/the-cryptographic-doom-principle/

Until we can use GCM, it looks like the best approach will be Encrypt-then-MAC with OpenSSL.  I'll write an article with teaching examples on the topic for E-E and post the article link here sometime before the weekend is out.  

You might want to be selective about what you encrypt, because the encryption process seems to add at least 88 characters to the length of the original data element!  Column widths would have to change.  And the encryption is not idempotent - the same clear-text string encrypted twice will produce different crypt-text strings.  This has implications for LIKE, ORDER, GROUP and WHERE clauses, etc.  But that said, it's a process that is about as close as we can get to state of the art.  If you use it on the columns that are not needed in those clauses, you will protect the data pretty well.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41877984
Here's a link to the article.  Please let me know your thoughts and questions, thanks.
https://www.experts-exchange.com/articles/28835/Keeping-Secrets-with-PHP.html
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
php documentation 4 22
while loop in html mail format 5 34
Help cleaning out CSS 2 32
MySQL: Updating SubQuery Match Faster 9 18
Healthcare providers, insurance companies and other covered entities trust eFax Corporate to transmit their most sensitive documents. eFax Corporate can help your organization implement a HIPAA compliant cloud faxing solution.
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now