PHP using mcrypt and store the encrypted in MySQL

Question

I am using Mcrypt to encrypt some strings.

After that I store them in my database, but in my database it looks like "??f??R?????h$", because many special chars are replaced by a '?'.

Do I have to use a special charset or is there another simple way?

Regards, Cr41s3

Answer 1

I think you might be saving the encrypted string's bytes directly into mysql database.

You could do something like this to solve your problem:

• Encryption: Orignal Text > MCrypt Encrypt > Base64 Encode > Store as Plain Text in MySQL

• Decryption: Load encrypted base64 encoded text from MySQL > Base64 Decode > MCrypt Decrypt -> Orignal Text

This is how I would do it. Create a class to do encryption/decryption:

<?php

class cipher
{
    private $securekey;
    private $iv_size;

    function __construct($textkey)
    {
        $this->iv_size = mcrypt_get_iv_size(
            MCRYPT_RIJNDAEL_128,
            MCRYPT_MODE_CBC
        );
        $this->securekey = hash(
            'sha256',
            $textkey,
            TRUE
        );
    }

    function encrypt($input)
    {
        $iv = mcrypt_create_iv($this->iv_size);
        return base64_encode(
            $iv . mcrypt_encrypt(
                MCRYPT_RIJNDAEL_128,
                $this->securekey,
                $input,
                MCRYPT_MODE_CBC,
                $iv
            )
        );
    }

    function decrypt($input)
    {
        $input = base64_decode($input);
        $iv = substr(
            $input,
            0,
            $this->iv_size
        );
        $cipher = substr(
            $input,
            $this->iv_size
        );
        return trim(
            mcrypt_decrypt(
                MCRYPT_RIJNDAEL_128,
                $this->securekey,
                $cipher,
                MCRYPT_MODE_CBC,
                $iv
            )
        );
    }
}

?>

Then use it like this:

// Usage
$cipher = new cipher('my-secret-key');
$orignal_text = 'my secret message';
$encrypted_text = $cipher->encrypt($orignal_text);   // store this in db
$decrypted_text = $cipher->decrypt($encrypted_text); // load $encrypted_text from db

// Debug
echo "<pre>";
echo "Orignal Text  : $orignal_text\r\n";
echo "Encrypted Text: $encrypted_text\r\n";
echo "Decrypted Text: $decrypted_text";
echo "</pre>";

This respectively outputs the following:

Orignal Text  : my secret message
Encrypted Text: Z21ifr5dHEdE9nO8vaDWb9QkjooqCK4UI6D/Ui+fkpmXWwmxloy8hM+7oimtw1wE
Decrypted Text: my secret message

Answer 2

1) To store binary stuff in mysql, use type BINARY/VARBINARY/BLOB instead of CHAR/VARCHAR/TEXT

2) When saving binary data to DB, use a method that won't apply any processing to the 'text' (you don't want to have your binary data processed as it were some string). Best bet is to use prepared statements of mysqli or PDO.

3) If you, for some reason, want to 'see binary stuff in DB', you will need to make them somewhat readable. Like using SELECT hex(binary_field) or SELECT TO_BASE64(binary_field)

4) If you refuse to use BLOB and prepared statements, well, to store binary data in mysql, first convert them using base64_encode(), so it will contain only printable characters.