How to decrypt a string encrypted by SQL Server's EncryptByPassPhrase() in PHP?

Question

I have an encrypted string and its key, which is created with SQL Server using "EncryptByPassPhrase", how can i decrypt it in PHP?

I have read the documentation of "EncryptByPassPhrase" which states that this is Triple DES encryption of 128 Length. I tried 3DES decryption of PHP but it is not returning the expected output.

Encryption in MS SQL is done with

declare @encrypt varbinary(200) 
select @encrypt = EncryptByPassPhrase('key', 'taskseq=10000&amt=200.5' )
select @encrypt

I am decrypting it in PHP as following:

    function decryptECB($encrypted, $key) {
       $iv_size = mcrypt_get_iv_size(MCRYPT_3DES, MCRYPT_MODE_ECB);
       $iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);
       // decrypting
       $stringText = mcrypt_decrypt(MCRYPT_3DES, $key, $encrypted, 
       MCRYPT_MODE_ECB, $iv);

       return $stringText;
    }

I assume you'd use docs.microsoft.com/en-us/sql/t-sql/functions/… in the SQL as well — user3783243
On my side, i am using MYSQL, where i couldn't find this TSql decryption function. — jsdubai
@user3783243 I am not syncing from mssql, i am having a number of encrypted strings hardcoded in my PHP script. — jsdubai
Okay, so mysql isn't in use. It seems like a bad idea to store DB values in your PHP statically. That defeats the purpose of the DB (and also the decryption would be a lot easier by using the inverse function). — user3783243
@user3783243 Actually the encrypted string will contain some data which will be used in generating a specific url, upon visiting that url, the encrypted string need to be decrypted in PHP application and the data will be shown on the page. We have no other way but to use that encrypted string generated in sql server with the above mentioned function of TSQL. — jsdubai

Pieter van den Ham Pieter van den Ham · Accepted Answer · 2019-05-01T01:27:10

I took the liberty of translating this Stack Overflow answer into PHP.

This is the result:

<?php

// SQL Server's DecryptByPassphrase translated into PHP. 
function decrypt(string $data, string $password): ?string {
    // SQL Server <2017 uses SHA1 for the key and the DES-EDE-CBC crypto algorithm
    // whereas SQL Server >= 2017 uses SHA256 and AES-256-CBC. 
    // Version 1 is the SHA1 + DES-EDE-CBC version, Version 2 is the AES-256-CBC version.
    // Version is stored in the first four bytes as a little endian int32.
    $version_bytes = substr($data, 0, 4);
    $version = unpack('V', $version_bytes)[1];

    // Password must be converted to the UTF-16LE encoding.
    $passwordUtf16 = mb_convert_encoding($password, 'UTF-16LE');

    if ($version === 1) {
        // Key is hashed using SHA1, The first 16 bytes of the hash are used.
        $key = substr(hash('sha1', $passwordUtf16, true), 0, 16);
        $method = 'des-ede-cbc';
        $options = OPENSSL_RAW_DATA;
        $iv = substr($data, 4, 8); // initialization vector of 8 bytes
        $encrypted_data = substr($data, 12); // actual encrypted data
    } else if ($version === 2) {
        // Key is hashed using sha256. Key length is always 32 bytes.
        $key = hash('sha256', $passwordUtf16, true);
        $method = 'aes-256-cbc';
        $options = OPENSSL_RAW_DATA;
        $iv = substr($data, 4, 16); // iv of 16 bytes
        $encrypted_data = substr($data, 20);
    } else {
        throw new \InvalidArgumentException('Invalid version');
    }

    $decrypted = openssl_decrypt($encrypted_data, $method, $key, $options, $iv);

    if ($decrypted === false) {
        return null;
    }

    // First 8 bytes contain the magic number 0xbaadf00d and the length
    // of the decrypted data
    $decrypted = substr($decrypted, 8);

    // UTF-16 encoding should be converted to UTF-8. Note that
    // there might be a better way to accomplish this.
    $isUtf16 = strpos($decrypted, 0) !== false;

    if ($isUtf16) {
        return mb_convert_encoding($decrypted, 'UTF-8', 'UTF-16LE');
    }

    return $decrypted;
}

// A version 1 encrypted string. Taken directly from the Stack Overflow answer linked above
$s = '010000007854E155CEE338D5E34808BA95367D506B97C63FB5114DD4CE687FE457C1B5D5';
$password = 'banana';
$bin = hex2bin($s);
$d = decrypt($bin, $password);
var_dump($d); // string(6) "turkey"

// A version 2 encrypted string. Taken directly from the Stack Overflow answer linked above
$s = '02000000266AD4F387FA9474E825B013B0232E73A398A5F72B79BC90D63BD1E45AE3AA5518828D187125BECC285D55FA7CAFED61';
$password = 'Radames';
$bin = hex2bin($s);
$d = decrypt($bin, $password);
var_dump($d); // string(16) "LetTheSunShining"

Sidenote: mcrypt is deprecated as it has been abandoned for over a decade.

How to decrypt a string encrypted by SQL Server's EncryptByPassPhrase() in PHP?

3 Answers