AES _Encryption in Mysql , Decryption in C#.Net

Question

Mysql :

SELECT AES_ENCRYPT('Test','pass')

AES_ENCRYPT() and AES_DECRYPT() enable encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as “Rijndael.” Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is secure enough for most purposes.

http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_aes-encrypt

I was trying to convert that Encrypted string into Decryped Strig in C#.net but i don't get the results as i expect.

http://msdn.microsoft.com/en-us/library/system.security.cryptography.rijndael.aspx#Y0

C#

static string DecryptStringFromBytes(byte[] cipherText, byte[] Key, byte[] IV)

In this method I pass ciphertext,Key value which i usedfrom Mysql and Rijndael.Create().IV for byte[] IV

I use the code but i don't get expected result. Review the code and comment Idk where made a mistake

Answer 1

After a long hours, I found a solution to this issue.

Couple of FYI's:

• MySQL as a default for AES_Encrypt uses 128 bit, with ECB mode, which does not require an IV.
• What padding mode they use is not specified, but they do say they pad it. For padding I use PaddingMode.Zeros.
• In C#, use AesManaged, not RijndaelManaged since that is not recommended anymore.
• If your Key is longer than 128 bits (16 bytes), then use a function below to create the correct key size, since the default MySQL AES algorithm uses 128 bit keys.
• Make sure you play around with the correct Encoding and know exactly what type of character encoding you will receive back when translating the bytes to characters.

For more info go here: https://forums.mysql.com/read.php?38,193084,195959#msg-195959

Code:

public static string DecryptAESStringFromBytes(byte[] encryptedText, byte[] key)
{
    // Check arguments.
    if ((encryptedText == null || encryptedText.Length <= 0) || (key == null || key.Length <= 0))
    {
        throw new ArgumentNullException("Missing arguments");
    }

    string decryptedText = null;

    // Create an AES object with the specified key and IV.
    using (AesManaged aesFactory = new AesManaged())
    {
        aesFactory.KeySize = 128;
        aesFactory.Key = AESCreateKey(key, aesFactory.KeySize / 8);
        aesFactory.IV = new byte[16];
        aesFactory.BlockSize = 128;
        aesFactory.Mode = CipherMode.ECB;
        aesFactory.Padding = PaddingMode.Zeros;

        // Create a decryptor to perform the stream transform.
        ICryptoTransform decryptor = aesFactory.CreateDecryptor();

        // Create the streams used for decryption.
        using (MemoryStream stream = new MemoryStream())
        {
            using (CryptoStream decryptStream = new CryptoStream(stream, decryptor, CryptoStreamMode.Write))
            {
                decryptStream.Write(encryptedText, 0, encryptedText.Length);
            }

            decryptedText = Encoding.Default.GetString(stream.ToArray());
        }
    }

    return decryptedText.Trim();
}

public static byte[] AESCreateKey(byte[] key, int keyLength)
{
    // Create the real key with the given key length.
    byte[] realkey = new byte[keyLength];

    // XOR each byte of the Key given with the real key until there's nothing left.
    // This allows for keys longer than our Key Length and pads short keys to the required length. 

    for (int i = 0; i < key.Length; i++)
    {
        realkey[i % keyLength] ^= key[i];
    }

    return realkey;
}

Answer 2

What you are doing is following a road of pain. Either decrypt/encrypt on MySQL and use an encrypted connection to the database (if that matters) or encrypt/decrypt on your .NET application, storing the encrypted data in a suitable column.

Mixing AES implementations is prone to mistakes and things can break more easily if you change versions of .NET or MySQL.

Now, to know what exactly is wrong we need to know if the IV is compatible between MySQL and .NET, or else find out what is MySQL's implementation IV and supply that.

And the other potential source of problems is how you have generated the byte arrays (we are not seeing that in your example). You have to consider character encoding issues in generating the arrays if the key is textual.

In the comments of this MySQL docs link there is information about the missing parameters.

Answer 3

Here is some working code for achieving the same encryption via C# as MySQL:

public byte[] AESEncrypt(byte[] plaintext, byte[] key) {
/* 
* Block Length: 128bit
* Block Mode: ECB
* Data Padding: Padded by bytes which Asc() equal for number of padded bytes (done automagically)
* Key Padding: 0x00 padded to multiple of 16 bytes
* IV: None
*/
RijndaelManaged aes = new RijndaelManaged();
aes.BlockSize = 128;
aes.Mode = CipherMode.ECB;
aes.Key = key;

ICryptoTransform encryptor = aes.CreateEncryptor();
MemoryStream mem = new MemoryStream();
CryptoStream cryptStream = new CryptoStream(mem, encryptor,
CryptoStreamMode.Write);

cryptStream.Write(plaintext, 0, plaintext.Length);
cryptStream.FlushFinalBlock();

byte[] cypher = mem.ToArray();

cryptStream.Close();
cryptStream = null;
encryptor.Dispose();
aes = null;

return cypher;
}

For details see MySQL Bug # 16713

EDIT:

Since the above is relying on officially non-documented information (though it is working) I would recommend to avoid it and use one of the options described in the answer from Vinko Vrsalovic .

Answer 4

If you run SELECT AES_ENCRYPT('Test','pass') your are sending the pass over the network unencrypted so any one can unencrypted the data.

The AES_ENCRYPT is used to store data so if the database gets hacked your data is safe, not to transmit data.

if you want data encryption over the net work connect to your mysql server using the ssl socket