Decrypt data in a PostgreSQL query which was encrypted with C#

Question

There is some code we have in C# which encrypts and decrypts data for storing in a postgresql database. The code for decrypting is as follows:

public string Decrypt(string val)
{
    var sb = new StringBuilder();
    string[] split = val.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

    foreach (string s in split)
    {
        sb.Append(Encoding.UTF8.GetString(Decode(Convert.FromBase64String(s))));
        sb.Append(" ");
    }
    sb.Remove(sb.Length - 1, 1); // Remove last space

    return sb.ToString();
}

private static byte[] Decode(byte[] encodedData)
{
    var symmetricAlgorithm = Aes.Create();
    symmetricAlgorithm.Key = HexToByteArray("<aes key>");

    var hashAlgorithm = new HMACSHA256();
    hashAlgorithm.Key = HexToByteArray("<hash key>");

    var iv = new byte[symmetricAlgorithm.BlockSize / 8];
    var signature = new byte[hashAlgorithm.HashSize / 8];
    var data = new byte[encodedData.Length - iv.Length - signature.Length];

    Array.Copy(encodedData, 0, iv, 0, iv.Length);
    Array.Copy(encodedData, iv.Length, data, 0, data.Length);
    Array.Copy(encodedData, iv.Length + data.Length, signature, 0, signature.Length);

    // validate the signature
    byte[] mac = hashAlgorithm.ComputeHash(iv.Concat(data).ToArray());

    if (!mac.SequenceEqual(signature))
    {
        // message has been tampered
        throw new ArgumentException();
    }

    symmetricAlgorithm.IV = iv;

    using (var memoryStream = new MemoryStream())
    {
        using (var cryptoStream = new CryptoStream(memoryStream, symmetricAlgorithm.CreateDecryptor(), CryptoStreamMode.Write))
        {
            cryptoStream.Write(data, 0, data.Length);
            cryptoStream.FlushFinalBlock();
        }
        return memoryStream.ToArray();
    }
}

private static byte[] HexToByteArray(string hex)
{
    return Enumerable.Range(0, hex.Length).
        Where(x => 0 == x % 2).
        Select(x => Convert.ToByte(hex.Substring(x, 2), 16)).
        ToArray();
}

The requirement I have now is that we want to be able to decrypt within an SQL query.. I have discovered the PGP_SYM_DECRYPT function, as well as some others like Encode()/Decode() for base64 strings and a decrypt_iv() function as well. Only I am uncertain how to use these to decrypt data.

Any crypto experts that could help me out here?

Alternatively, is there some equivalent of MSSQL's CLR functions for Postgres?

JensV · Accepted Answer · 2020-08-12 08:09:37Z

2

+100

So what I'm inferring from the decryption code is the following:

Your encodedData is split up into three parts

Initialization vector (IV)
Ciphertext
Signature

You are using AES to encrypt/decrypt the data and use HMACSHA256 for the signature.

The AES block size in C# is fixed to 128 bits.
The signature is 256 bits in length (thus SHA256) (ref)
The IV is blockSize / 8 bytes long -> 16 bytes
Signature is 32 bytes

That means your encoded data is split up as follows:

[IV 16 bytes][Ciphertext n bytes][Signature 32 bytes]

To decrypt this with postgres, you need to have the pgcrypto module enabled. Lets say we have a table Foo with the field data of type bytea which contains the encrypted data. By using the Raw Encryption Functions of pgcrypto you should be able to decrypt this utilizing binary string operators to extract the parts.

(octet_length(data) - 16 - 32) should be the ciphertext length
substring(data from 0 for 16) should get the IV
substring(data from 16 for (octet_length(data) - 16 - 32)) should get the ciphertext
substring(data from (octet_length(data) - 32) for 32 should get the signature.

This results in:

SELECT decrypt_iv(
    substring(data from 16 for (octet_length(data) - 16 - 32)),
    "Decryption Key as bytes"::bytea,
    substring(data from 0 for 16),
    'aes'
)
FROM Foo

The signature is disregarded in this example, but you should be able to verify it in a similar way with General Hashing Functions. If the decryption key is wrong, you will probably just get garbage data.

answered Aug 12, 2020 at 8:09

JensV

4,6692 gold badges22 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Over a year ago

I appreciate the great response, but I am getting an error as follows: "decrypt_iv error: Data not a multiple of block size". Also, I am not 100% sure which decryption key you are referring to. I assume the AES one, but there is also a hash key.. so please confirm. In any case, I tried both and got the same error message. Would appreciate any assistance you can provide.

JensV Over a year ago

@Matt I mean the AES one. If you supply the key as a hex string, use the format "\xDEADBEEF". The hash key is ingored (part of the signature, see last paragraph). If possible, could you provide an example data payload with a decryption key? (without sensitive data ofc)

JensV Over a year ago

@Matt providing the encryption code could also help if available

Matt Over a year ago

Thanks. Please see my answer for the final solution.. but I have rewarded you the +100 bounty, as you helped get us there.. with such a thorough explanation. Thanks

Matt · Accepted Answer · 2020-08-17 06:05:09Z

Thanks to @JensV for his answer.. we finally came up with the following:

CREATE OR REPLACE FUNCTION aes_cbc_mix_sha256_decrypt(data text, key text) RETURNS text
language plpgsql
AS
$$
DECLARE
  res text;
  dataHex text;
  iv bytea;
  aes_key bytea;
  encrypted_data bytea;
BEGIN
  SELECT decode(key, 'hex') INTO aes_key;
  SELECT encode(decode(data, 'base64'), 'hex') INTO dataHex;
  SELECT decode(SUBSTRING(dataHex FROM 1 FOR 32), 'hex') INTO iv;
  SELECT decode(SUBSTRING(dataHex FROM 33 FOR (SELECT LENGTH(dataHex) - 32 - 64)), 'hex') INTO encrypted_data;
  SELECT encode(decrypt_iv(encrypted_data, aes_key, iv, 'aes-cbc'), 'escape') INTO res;
  RETURN res;
  
    EXCEPTION WHEN others THEN
        RETURN data;
END;
$$

Collectives™ on Stack Overflow

Decrypt data in a PostgreSQL query which was encrypted with C#

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related