CODEWITHSUNDEEP
phpmysqlalgorithmencryption
guice
guice

Reputation: 1094

MySQL & PHP cross encryption / decryption algorithm?

I'm looking for an encryption / decryption algorithm (method) that would work across MySQL (5.7.x) and PHP (5.6.x).

Problem: we have some processes via procs. and events within MySQL, one of them requires encrypting the user's id (send in email links). We then, in turn, need to grab that and decrypt within PHP for processing.

Is there a compatible algorithm that we can use that works cross platforms?

*Resolved

I found my biggest problem was MySQL is actually limited to 16char / 128bit key. All my attempts with SHA256 failed because of that. In addition, I've discovered openssl_encrypt() is compatible out of the box (no needing to pad):

$salt = substr(hash('sha256', 'My secret passphrase'), 0, 16);
$text = 12345;

print_r(array(
    'salt' => $salt,
    'aes-128-ecb' => openssl_encrypt($text, 'aes-128-ecb', $salt),
));

Results in:

Array
(
    [salt] => 760624f7a7f6e8e4
    [aes-128-ecb] => blXklI80Q+KmPH909qUyWw==
)

MySQL Response: 

set @salt = SUBSTRING(SHA2('My secret passphrase',256), 1, 16);
select @salt as salt
    , TO_BASE64(AES_ENCRYPT(12345, @salt));

Results in:

salt                TO_BASE64(AES_ENCRYPT(12345, @salt))
760624f7a7f6e8e4    blXklI80Q+KmPH909qUyWw==

Upvotes: 2

Views: 1121

Answers (2)

Akshay
Akshay

Reputation: 2229

This is something that I found doing some research :-

PHP code to encrypt:

// MySQL uses 16 bytes key for 128 encryption/decryption
$key = "ABCDEF0123456789";

$plaintext = "This string was AES-128 / EBC / ZeroBytePadding encrypted.";
// Optionally UTF-8 encode
$plaintext_utf8 = utf8_encode($plaintext);
// Find out what's your padding
$pad_len = 16 - (strlen($plaintext_utf8) % 16);
// Padd your text
$plaintext_utf8 = str_pad($plaintext_utf8, (16 * (floor(strlen($plaintext_utf8) / 16) + 1)), chr($pad_len));

// Encryption
mt_srand();
$td = mcrypt_module_open(MCRYPT_RIJNDAEL_128, '', MCRYPT_MODE_ECB, '');
mcrypt_generic_init($td, $key, false);
// Generates a warning about empty IV but it's Ok
$ciphertext = mcrypt_generic($td, $plaintext_utf8);
mcrypt_generic_deinit($td);
$ciphertext = mysql_real_escape_string($ciphertext);

// Store in MySQL
$mysqli = new mysqli("localhost", "test", "test", "test");
$mysqli->set_charset("utf8");
$mysqli->query("insert into test(content) value ('$ciphertext')");
$mysqli->close();

SQL query to search for string was:

SELECT CAST(AES_DECRYPT(content,'ABCDEF0123456789') AS CHAR) AS content
FROM test
WHERE CAST(AES_DECRYPT(content,'ABCDEF0123456789') AS CHAR) like '%string was%';

Output :-

This string was AES-128 / EBC / ZeroBytePadding encrypted.

Upvotes: 1

Felippe Duarte
Felippe Duarte

Reputation: 15131

You can do with PHP mcrypt_encrypt and mcrypr_decrypt using a simple VARCHAR column in your database. From the docs:

$key = pack('H*', "bcb04b7e103a0cd8b54763051cef08bc55abe029fdebae5e1d417e2ffb2a00a3");

$iv_size = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_CBC);
$iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);

$plain_text = 'some text blabla';

$encrypt_text = mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $key, $plain_text, MCRYPT_MODE_CBC, $iv);

echo $encrypt_text;
//should output some hash

$decrypt_text = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $key,
                                $encrypt_text, MCRYPT_MODE_CBC, $iv_dec);

echo $decryp_text;
//should output 'some text blabla'

Upvotes: 0

Related Questions