laravel 4.2 queries with an encrypted column

Question

i currently have this code in my controller which display a set of records here is my code

public function view()
{
    $title = "View Guardian Information";
    $vPa   = DB::table('dbo_guardianinformation')
                ->join('dbo_cities', 'dbo_guardianinformation.CityID', '=' , 'dbo_cities.CityID')
                ->select('dbo_guardianinformation.ParentAccountID','dbo_guardianinformation.FirstName','dbo_guardianinformation.LastName','dbo_guardianinformation.Roles',
                        'dbo_guardianinformation.Address','dbo_cities.CityName','dbo_guardianinformation.Status','dbo_guardianinformation.EmailAddress')
                ->get();
     //encrypt decrypt algo
    // $sptkey  = md5('sample_encryptkey');
    // $enPass  = rtrim(base64_encode(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $sptkey, $defPass, MCRYPT_MODE_ECB)));
    // $decPass = rtrim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $sptkey, base64_decode($enPass), MCRYPT_MODE_ECB));

    return View::make('ssims.view_parentAccount',compact('title','vPa'));
}

my problem is that the column dbo_guardianinformation.Addresscontains encrypted records i currently have no idea on where should i put the decryption code so that when the $vPa will be passed to the view it already contained the decrypted records. any ideas? thanks to anybody who would help

Answer 1

Indexing Encrypted Data

If you need to search an encrypted column in a SQL database quickly and efficiently, you need to construct a blind index of the data (i.e. store hash_hmac('sha256', $plaintext, $separate_key_here) in an additional column) and structure your select queries based on that. (The linked article explains the security requirements.)

This saves you from having to do a foreach() loop but, since HMAC-SHA256 is used, it's incredibly unlikely that an attacker with access to the database will be able to tease the plaintext out of the system.

---

That said, there's something else I would like to address:

Weak Cryptography

Please don't use the encryption code you included in your question. It's very insecure. Laravel has its own encryption class; please use that instead. It does a lot of the things right that the code snippet you included does not. For example: it provides authenticated encryption.

$sptkey = md5('sample_encryptkey');

If you want a modicum of security in your application, don't ever use md5($string) to generate a key. This is just a bad idea all around:

• md5() returns a 32-char hex string
• Most encryption functions expect a raw binary string
• MD5 is an incredibly broken hash function
• To transform a password into an encryption key, you need to use a key derivation function, i.e. Password-Based Key Derivation Function #2 with SHA-256 (PBKDF2-SHA256).

Consider, for example, this code instead:

define('MY_APP_PBKDF2_ITERATIONS', 86000);
define('MY_APP_KEY_LENGTH', 32); // or 16 for AES-128
// ...
$sptkey = hash_pbkdf2(
    'sha256',
    $your_password,
    $salt, // 32 bytes from /dev/urandom
    MY_APP_PBKDF2_ITERATIONS,
    MY_APP_KEY_LENGTH,
    true
);

I've expanded the whitespace here and left some inline-comments below:

$enPass = rtrim(                 // Unnecessary, base64_encode doesn't leave whitespace
    base64_encode(
        mcrypt_encrypt(
            MCRYPT_RIJNDAEL_256, // This isn't AES-256 by the way
            $sptkey,
            $defPass,
            MCRYPT_MODE_ECB      // ECB mode is the worst mode
        )
    )
);
$decPass = rtrim(               // Padding oracle attack
    mcrypt_decrypt(
        MCRYPT_RIJNDAEL_256,
        $sptkey,
        base64_decode($enPass), // No error checking
        MCRYPT_MODE_ECB
    )
);

Further reading on the specific issues:

• MCRYPT_RIJNDAEL_256
• MCRYPT_MODE_ECB
• Padding Oracle Attack

What to do instead (choose one):

• Use Laravel's encryption features, since you're already using Laravel.
• Use libsodium (highly recommended)
• Use Defuse Security's PHP Encryption class
• When Halite hits 1.0.0, switch to that (it's basically libsodium for novices)

Answer 2

turns out i with a little tinkering and help from João Mendes i had the code like this

public function view()
{
    $title = "View Guardian Information";
    $vPa   = DB::table('dbo_guardianinformation')
                ->join('dbo_cities', 'dbo_guardianinformation.CityID', '=' , 'dbo_cities.CityID')
                ->select('dbo_guardianinformation.ParentAccountID','dbo_guardianinformation.FirstName','dbo_guardianinformation.LastName','dbo_guardianinformation.Roles',
                        'dbo_guardianinformation.Address','dbo_cities.CityName','dbo_guardianinformation.Status','dbo_guardianinformation.EmailAddress')
                ->get();

    foreach ($vPa as $key => $dvPa) 
    {
        $sptkey  = md5('this is secret');
        $enAdd = $dvPa->Address;
        $decAdd = rtrim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $sptkey, base64_decode($enAdd), MCRYPT_MODE_ECB));

        $dvPa->Address = $decAdd;   
    }
    return View::make('ssims.view_parentAccount',compact('title','vPa'));
}