Solved

mysql Encryption with PHP

Posted on 2016-11-03
8
123 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 110

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
Automating Your MSP Business

The road to profitability.
Delivering superior services is key to ensuring customer satisfaction and the consequent long-term relationships that enable MSPs to lock in predictable, recurring revenue. What's the best way to deliver superior service? One word: automation.

 
LVL 110

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
 

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 110

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 110

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 110

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
The conference as a whole was very interesting, although if one has to make a choice between this one and some others, you may want to check out the others.  This conference is aimed mainly at government agencies.  So it addresses the various compli…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

688 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