Encryption in ASP.NET that can be decrypted in MySQL with AES_DECRYPT()

Question

I would like to be able to perform application-level encryption in ASP.NET, producing an array of bytes that would then be saved to a MySQL blob column. I would then like it to be an option that, if you have the encryption key, you would be able to decrypt it using MySQL's AES_DECRYPT() function. This seems like it should be possible, since AES_DECRYPT is an implementation of AES/Rijndael.

The MySQL AES_ENCRYPT/DECRYPT functions simply take a key and the string to encrypt/decrypt as parameters. The examples i've seen for encryption in ASP.NET/C#, however, involve also specifying values for Key and IV (initialization vector). How do these affect the final, encrypted byte array, and how can they be taken into account when decrypting with AES_DECRYPT)_?

Answer 1

You can do that by setting RijndaelManaged to use ECB mode.

However, ECB mode is not secure and should be avoided.

---

In general, a database is a very bad place to perform encryption.

If you are able to encrypt your data in the database, that implies that you have both the ciphertext and the key in the same place; this defeats the purpose of encryption.

You should keep the key as far away from ciphertext storage as possible; using any sort of SQL encryption function is usually indicative of a fundamental design flaw in your encryption strategy which can have disastrous consequences.

Answer 2

Encryption

In Mysql use HEX(AES_ENCRYPT('unencryptedString', 'Password'))

Example

UPDATE `secrets` SET `value`=HEX(AES_ENCRYPT('unencryptedString', 'Password')) WHERE `Id` = 2;

you will see in the database there is a value similar to this D4B5E4CAD92FFB73FCAEB5ED3B31E9EDD8FA7440E9E3F582FE5A9237DB8EE013

Now the equivalent code in C# is (Original Source:link)

public static String AES_encrypt(String Input, string key)
    {
        RijndaelManaged aes = new RijndaelManaged();
        aes.KeySize = 128;
        aes.BlockSize = 128;
        aes.Mode = CipherMode.ECB;
        aes.Padding = PaddingMode.PKCS7;
        aes.Key = mkey(key);
        aes.IV = new byte[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

        var encrypt = aes.CreateEncryptor(aes.Key, aes.IV);
        byte[] xBuff = null;
        using (var ms = new MemoryStream())
        {
            using (var cs = new CryptoStream(ms, encrypt, CryptoStreamMode.Write))
            {
                byte[] xXml = Encoding.UTF8.GetBytes(Input);
                cs.Write(xXml, 0, xXml.Length);
                cs.FlushFinalBlock();
            }

            xBuff = ms.ToArray();
        }

        return xBuff.ToHexString();
    }

Helper methods and extensions that used

Refernce Link

private static byte[] mkey(string skey)
    {

        byte[] key = Encoding.UTF8.GetBytes(skey);
        byte[] k = new byte[16] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        for (int i = 0; i < key.Length; i++)
        {
            k[i % 16] = (byte)(k[i % 16] ^ key[i]);
        }

        return k;
    }

Reference Link

public static class ByteArrayExtensions
{
    public static string ToHexString(this byte[] ba)
    {
        return BitConverter.ToString(ba).Replace("-", "");
    }
}

Decryption

in Mysql use CAST(AES_DECRYPT(UNHEX(c.value), 'Password') as char)

Example

SELECT c.*,CAST(AES_DECRYPT(UNHEX(c.`value`), 'Password') as char) FROM `secrets` as c where `Id` = 2;

Equivalent code in C# is

public static String AES_decrypt(String Input, string key)
    {
        RijndaelManaged aes = new RijndaelManaged();
        aes.KeySize = 128;
        aes.BlockSize = 128;
        aes.Mode = CipherMode.ECB;
        aes.Padding = PaddingMode.PKCS7;
        aes.Key = mkey(key);
        aes.IV = new byte[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

        var decrypt = aes.CreateDecryptor();
        byte[] encryptedStr = Input.FromHex2ByteArray();

         string Plain_Text;

        using (var ms = new MemoryStream(encryptedStr))
        {
            using (var cs = new CryptoStream(ms, decrypt, CryptoStreamMode.Read))
            {
                using (StreamReader reader = new StreamReader(cs))
                {
                    Plain_Text = reader.ReadToEnd();
                }
            }
        }

        return Plain_Text;
    }

Helper methods and extensions that used

Reference Link

private static byte[] mkey(string skey)
    {

        byte[] key = Encoding.UTF8.GetBytes(skey);
        byte[] k = new byte[16] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        for (int i = 0; i < key.Length; i++)
        {
            k[i % 16] = (byte)(k[i % 16] ^ key[i]);
        }

        return k;
    }

Reference Link

public static byte[] FromHex2ByteArray(this string hex)
    {
        if (hex.Length % 2 == 1)
            throw new Exception("The binary key cannot have an odd number of digits");

        byte[] arr = new byte[hex.Length >> 1];

        for (int i = 0; i < hex.Length >> 1; ++i)
        {
            arr[i] = (byte)((GetHexVal(hex[i << 1]) << 4) + (GetHexVal(hex[(i << 1) + 1])));
        }

        return arr;
    }
    private static int GetHexVal(char hex)
    {
        int val = (int)hex;
        //For uppercase A-F letters:
        //return val - (val < 58 ? 48 : 55);
        //For lowercase a-f letters:
        //return val - (val < 58 ? 48 : 87);
        //Or the two combined, but a bit slower:
        return val - (val < 58 ? 48 : (val < 97 ? 55 : 87));
    }