Jump to content

aes

Destramic

By Destramic
in MySQL Help

 Share
Go to solution Solved by Jacques1,

Recommended Posts

Destramic Advanced Member

Destramic

Posted
Posted

i've been reading up about security for users data in my db come across aes (hopefully im on the right track for good security?).

 

now i may be over complicating things (as usual) but when a user creates a account i was planning on creating a mysql_aes_key with the users password (after it has been password hashed) creating a user content key

 

then to use my encrypt method to encrypt the user content key with a random master key, creating the user content key encryption

 

also i wanted to create a master key for each user inside a table as well as storing the user content key in the users db row.

 

 

here is what i've come up with or sourced should i say:

<?php

class AES
{
    public function random_string($length = 60)
    {
        if (!is_numeric($length))
        {
            return null;
        }
    
        $bytes = openssl_random_pseudo_bytes($length, $cryptographically_strong);
        $hex   = bin2hex($bytes);
    
        if (!$cryptographically_strong)
        {
            $this->random_string($length);
        }
    
        return $hex;
    }
    
    public function mysql_aes_key($key)
    {
    	$new_key = str_repeat(chr(0), 16);
    	
    	for($i=0,$len=strlen($key);$i<$len;$i++)
    	{
    		$new_key[$i%16] = $new_key[$i%16] ^ $key[$i];
    	}
    	
    	return $new_key;
    }
    
    public function encrypt($val, $key)
    {
    $key = $this->mysql_aes_key($key);
	$pad_value = 16-(strlen($val) % 16);
	$val = str_pad($val, (16*(floor(strlen($val) / 16)+1)), chr($pad_value));
	 $encryption = mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $key, $val, MCRYPT_MODE_ECB, mcrypt_create_iv( mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_ECB), MCRYPT_DEV_URANDOM));
     return $encryption;
    }
    
    public function decrypt($val, $key)
    {
    	$key = $this->mysql_aes_key($key);
	   $val = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $key, $val, MCRYPT_MODE_ECB, mcrypt_create_iv( mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_ECB), MCRYPT_DEV_URANDOM));
	   return rtrim($val);
    }
}


$master_key       = "354fdedcc54d356bf681571e3dcacf73dd818656ffddc779d33b925e0b3f32fae01642fe7719bb9504c80de4f4193933f36948770f2ac832b5364514";
$users_password   = "helloworldthisismypassword";


$aes = new AES;
// $aes->random_string(); // master key
$user_content_key = $aes->mysql_aes_key($users_password);

$user_content_key_encrypted = $aes->encrypt($user_content_key, $master_key);

//echo $a = $aes->encrypt('ricky', $user_content_key);
//echo $aes->decrypt($a, $user_content_key);

// inserts and selects perfect
    
    
$link = mysqli_connect("127.0.0.1", "root", "root", "test");
$insert = "INSERT INTO `users`(`password`, `encryption_key`, `name`, `age`) 
           VALUES ('" . $users_password."','" . $user_content_key . "',AES_ENCRYPT('ricky', '" . $user_content_key_encrypted . "'),AES_ENCRYPT('28', '" . $user_content_key_encrypted . "'))";
//$result = mysqli_query($link, $insert);

$select = "SELECT AES_DECRYPT(name, '" . $user_content_key_encrypted . "') AS name,
                  AES_DECRYPT(age, '" . $user_content_key_encrypted . "') AS age
          FROM users";
$result = mysqli_query($link, $select);
$rows = mysqli_fetch_array($result);

//print_r($rows);


// select 2

$select2 = "SELECT @content_key := AES_ENCRYPT(u.encryption_key, ek.encryption_key),
                   AES_DECRYPT(u.name, @content_key) as `name`
            FROM users u
            LEFT JOIN encryption_keys ek ON ek.user_id = u.user_id";

$result2 = mysqli_query($link, $select2);
$rows2 = mysqli_fetch_array($result2);

//print_r($rows2);

inserting a user and selecting works fine...but im not sure if what im trying to do on my 2nd select query is secure (although it doesnt work)....but i would like to do something like this:

SELECT @content_key := AES_ENCRYPT(u.encryption_key, ek.encryption_key),
                   AES_DECRYPT(u.name, @content_key) as `name`
            FROM users u
            LEFT JOIN encryption_keys ek ON ek.user_id = u.user_id

am i on the right track with what im trying to do here please guys?...advise would be great thank you

Link to comment
Share on other sites
Jacques1 Prolific Member

Jacques1

Posted
Posted (edited)

Hi,

 

this doesn't make a lot of sense to me, and there are many serious defects in the code.

 

First of all, what are you trying to achieve? I see a lot of keys derived from keys derived from keys but no clear concept. Who should know the data? Do you want the server to have access to all plaintext? Then you can forget about all this super-complicated user key stuff and just use a single server key. Or do you want to create a scenario where even the server doesn't know the data until the user has provided their personal key? Then you obviously musn't store the user keys in your database. You have to ask the user for their password, run the plaintext password through a key derivation algorithm like PBKDF2 and then use that key for encrypting or decrypting the data. The keys aren't stored anywhere.

 

User-provided keys probably don't make sense in your case, because you wouldn't be able to do anything with the ciphertext stored in your tables except for showing the data to the user right after they're logged in. This is only useful if the data is purely private like e-mails in a webmailer.

 

So I'll assume you want a single master key. Note that you must not use ECB mode like you currently do, or else all users with the same age, name etc. will end up with the same ciphertext. Use a proper mode like CBC. This means you'll have to generate a random initialization vector (IV) before encryption and then store the IV next to the ciphertext. So your table will look something like this:

name_iv CHAR(32)    | name TEXT    | age_iv CHAR(32)     | age TEXT
--------------------+--------------+---------------------+-------------
(random hex string) | (ciphertext) | (random hex string) | (ciphertext)
...

Encryption and decryption should happen in the application, not the database system. If you share the master key with MySQL, this increases the risk of leaking it (e. g. through the query log).

 

The pseudo-code looks like this:

Encryption:

master_key := fetch master key from configuration file

name := the plaintext username
name_init_vector := generate 16 random bytes
name_ciphertext := AES_CBC_ENCRYPT(name, name_init_vector, master_key)

age := the plaintext age
age_init_vector := generate 16 random bytes
age_ciphertext := AES_CBC_ENCRYPT(age, age_init_vector, master_key)

insert (age_init_vector, age_ciphertext, name_init_vector, name_ciphertext) into users

Decryption:

master_key := fetch master key from configuration file
select age_init_vector, age_ciphertext, name_init_vector, name_ciphertext from users

age := AES_CBC_DECRYPT(age_ciphertext, age_init_vector, master_key)
name := AES_CBC_DECRYPT(name_ciphertext, name_init_vector, master_key)

Be very careful not to confuse the encoded keys with the actual binary keys! Currently, you use the hexadecimal representation of the master key for encryption, which means each of the 16 bytes only has 4 bits set. This reduces the actual key length to only 64 bits.

 

Do you follow me so far?

Edited by Jacques1
Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

yep i'm with you...thank you for your post :)

 

although how do i use AES_CBC_DECRYPT() and AES_CBC_DECRYPT()?

 

i have the extension php_openssl.dll included but i get

 

 

Fatal error: Call to undefined function AES_CBC_ENCRYPT() in C:\nginx\html\aes.class.php on line 34

 

also is openssl_random_pseudo_bytes() fine for the 16 random bytes?

 

thank you

Link to comment
Share on other sites
  • Solution
Jacques1 Prolific Member

Jacques1

Posted
  • Solution
Posted (edited)

although how do i use AES_CBC_DECRYPT() and AES_CBC_DECRYPT()?

 

This was just pseudo-code. If you're using the OpenSSL extension, you encrypt data with openssl_encrypt() and decrypt it with openssl_decrypt().

<?php

const ENCRYPTION_ALGORITHM = 'AES-128-CBC';

// from the configuration file
$server_master_key_hex = '1b438d8bf995e4152d07fa9e9626e4ee';

// decode the master key; this is absolutely crucial, because OpenSSL expects a binary key
$server_master_key = hex2bin($server_master_key_hex);

// encryption
$plaintext = 'Hallo world.';
$iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length(ENCRYPTION_ALGORITHM));
$ciphertext = openssl_encrypt($plaintext, ENCRYPTION_ALGORITHM, $server_master_key, false, $iv);
echo "Ciphertext: $ciphertext<br>";

// decryption
$retrieved_plaintext = openssl_decrypt($ciphertext, ENCRYPTION_ALGORITHM, $server_master_key, false, $iv);
echo "Plaintext: $retrieved_plaintext";

This still needs error checking.

 

 

 

also is openssl_random_pseudo_bytes() fine for the 16 random bytes?

 

Yes.

 

However, note that there are two bugs in your current code:

  • When the output turns out to not be cryptographically secure, you call the function recursively but don't override the previous result.
  • There's no recursion limit, so you may run into a segmentation fault or exceed the XDebug recursion limit. It's better to have a while loop with a fixed limit.
<?php

$max_attempts = 10;
$attempts = 0;
do
{
    $random_bytes = openssl_random_pseudo_bytes(16, $crypto_strong);
    $attempts++;
}
while (!$crypto_strong && $attempts < $max_attempts);
Edited by Jacques1
  • Like 1
Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

brilliant thank you

 

i've changed everything you've told me :happy-04:

 

the only thing i may be a bit concerned about is storing the master keys inside another table which is relationship with the users table by the user_id

 

 

but it works brilliantly....i can use this on users address and even sessions.

<?php

const ENCRYPTION_ALGORITHM = 'AES-128-CBC';

class AES
{
    public function master_key($length = 60)
    {
        if (!is_numeric($length))
        {
            return null;
        }
        
        $max_attempts = 10;
        $attempts     = 0;
          
        do
        {
            $bytes = openssl_random_pseudo_bytes($length, $cryptographically_strong);
            
            $attempts++;
        }
        
        while (!$cryptographically_strong && $attempts < $max_attempts);

        if (!$cryptographically_strong)
        {
            return false;
        }
        
        $hex = bin2hex($bytes);
        
        return $hex;
    }
    
    public function encrypt($value, $master_key)
    {
        $init_vector = openssl_random_pseudo_bytes(openssl_cipher_iv_length(ENCRYPTION_ALGORITHM));
        $ciphertext  = openssl_encrypt($value, ENCRYPTION_ALGORITHM, $master_key, false, $init_vector);
    
        return array(
            'init_vector' => $init_vector,
            'ciphertext'  => $ciphertext
        );
    }
    
    public function decrypt($ciphertext, $init_vector, $master_key)
    {
        $plaintext = openssl_decrypt($ciphertext, ENCRYPTION_ALGORITHM, $master_key, false, $init_vector);
    
        return $plaintext;
    }
}

$aes = new AES;
$server_master_key = $aes->master_key();

// destramic
// ricky
$name = "destramic";
$encryption = $aes->encrypt($name, $server_master_key);

//print_r($encryption);

//echo $aes->decrypt($encryption['ciphertext'], $encryption['init_vector'], $server_master_key);


// mysql stuff

$link = mysqli_connect("127.0.0.1", "root", "root", "test");

//$insert = "INSERT INTO `users`(`name`, `name_init_vector`)
 //          VALUES ('" . $encryption['ciphertext'] ."', '" . $encryption['init_vector'] . "')";


//$result  = mysqli_query($link, $insert);
//$user_id = mysqli_insert_id($link);

//$insert_key = "INSERT INTO `encryption_keys` (`encryption_key`, `user_id`)
//               VALUES ('" . $server_master_key ."', '" . $user_id . "')";

//$result = mysqli_query($link, $insert_key);

$select = "SELECT u.name,
                  u.name_init_vector,
                  ek.encryption_key
          FROM users u
          LEFT JOIN encryption_keys ek ON ek.user_id = u.user_id";
$result = mysqli_query($link, $select);
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);

//print_r($rows);

$decrypted_rows = array();

foreach ($rows as $key => $array)
{
    foreach ($array as $column => $value)
    {
        $init_vector = $column . '_init_vector';
        
        if (array_key_exists($init_vector, $array) &&
            array_key_exists('encryption_key', $array))
        {
            $init_vector = $array[$init_vector];
            $master_key  = $array['encryption_key'];
            
            $decrypted_value               = $aes->decrypt($value, $init_vector, $master_key);
            $decrypted_rows[$key][$column] = $decrypted_value;
        }
    }
}

print_r($decrypted_rows);

can't honestly say how much i appropriate yours and the other guys time and help on here!!!

Link to comment
Share on other sites
Jacques1 Prolific Member

Jacques1

Posted
Posted

The class looks good, but you musn't store the keys in the database, and you should only generate a single key for the entire application.

 

In fact, the whole point of the key is that it's not in the database so that an attacker who has gained access to the data (e. g. through an SQL injection) won't be able to read it. If the key is sitting right next to the ciphertext, the whole encryption is pointless.

 

So generate a single server key, put it into some PHP configuration file and encrypt all sensitive data with that key. This is the standard procedure in many frameworks. Generating a new key for each user serves no purpose and only makes things more complicated.

 

You also need to fix your database code. Inserting raw input into queries will result in an SQL injection attack sooner or later, which is particularly bad when you're dealing with sensitive data. Use prepared statements and bind the input to the statement parameters. This will make sure they cannot cause any harm.

Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

a lot of work still to be done here but just in case someone else is learning how to encrypt db details i now have added a method to encrypt and decrypt array (mysql rows)

 

ensuring the column name init vector is called  :  {column name}_init_vector

<?php

const ENCRYPTION_ALGORITHM = 'AES-128-CBC';

class AES
{
    private $_master_key;
    
    public function __construct()
    {
        $this->_master_key = "5e2a0626516f3108e55e25e4bb6a62835c2f5d2b2b8d194c9acca63ef8beff6bfb947233bd83cfda9021e5a80bc183bcd835180c9955b733fd1a6d9d";
    }
    
    public function generate_master_key($length = 60)
    {
        if (!is_numeric($length))
        {
            return null;
        }
        
        $max_attempts = 10;
        $attempts     = 0;
          
        do
        {
            $bytes = openssl_random_pseudo_bytes($length, $cryptographically_strong);
            
            $attempts++;
        }
        
        while (!$cryptographically_strong && $attempts < $max_attempts);

        if (!$cryptographically_strong)
        {
            return false;
        }
        
        $hex = bin2hex($bytes);
        
        return $hex;
    }
    
    public function encrypt($value, $master_key)
    {
        $init_vector = openssl_random_pseudo_bytes(openssl_cipher_iv_length(ENCRYPTION_ALGORITHM));
        $ciphertext  = openssl_encrypt($value, ENCRYPTION_ALGORITHM, $master_key, false, $init_vector);
    
        return array(
            'init_vector' => $init_vector,
            'ciphertext'  => $ciphertext
        );
    }
    
    public function decrypt($ciphertext, $init_vector, $master_key)
    {
        $plaintext = openssl_decrypt($ciphertext, ENCRYPTION_ALGORITHM, $master_key, false, $init_vector);
    
        return $plaintext;
    }
    
    public function encrypt_array($array)
    {
        $encrypted_array = array();
        $master_key      = $this->_master_key;
        
        foreach ($array as $key => $data)
        {
            foreach ($data as $column => $value)
            {
                $encryption         = $this->encrypt($value, $master_key);
                $init_vector_column = $column . '_init_vector';
                
                $encrypted_array[$key][$column]             = $encryption['ciphertext'];
                $encrypted_array[$key][$init_vector_column] = $encryption['init_vector'];
            }
        }
        
        return $encrypted_array;
    }
    
    public function decrypt_array($array)
    {
        $decrypted_array = array();
        $master_key      = $this->_master_key;
        
        foreach ($array as $key => $data)
        {
            foreach ($data as $column => $value)
            {
                $init_vector = $column . '_init_vector';
        
                if (array_key_exists($init_vector, $data))
                {
                    $init_vector = $data[$init_vector];
        
                    $decrypted_value                = $this->decrypt($value, $init_vector, $master_key);
                    $decrypted_array[$key][$column] = $decrypted_value;
                }
            }
        }
        
        return $decrypted_array;
    }
}

$aes = new AES;

$data = array(
         array('name' => 'destramic', 
               'age' => '28'),
         array('name' => 'alan',
               'age' => '99')    
);

$encryption = $aes->encrypt_array($data);
print_r($encryption);
$decryption = $aes->decrypt_array($encryption);
print_r($decryption);

:happy-04: :happy-04: :happy-04: :happy-04: :happy-04: :happy-04:

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.