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
Solved

mysql Encryption with PHP

Posted on 2016-11-03
8
104 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 109

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 109

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 109

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 109

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 109

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Provide an easy one stop to quickly get the relevant information on common asked question on Ransomware in Expert Exchange.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

828 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