CODEWITHSUNDEEP
c#mysqlencryptionaes
Paweł Adamczyk
Paweł Adamczyk

Reputation: 253

AES_DECRYPT() and AES_ENCRYPT() in MySQL with Polish characters

I have a MySQL database which is configured to receive a data with polish characters (f. ex ą, ę, ó, ł, ń etc.).

Now I want to send data with these Polish characters to a db using AES_ENCRYPT(), and then get them from there using AES_DECRYPT(). My problem is that I receive a byte[] array in C# which has X elements where X is length of text I receive. And every single array element has an ASCII code of an character it represents. I could easily convert it to text using Encoding Class, but I won't get Polish characters in output text.

F. ex.:

I send AES_ENCRYPT('ąąą', '123') to db. I get AES_DECRYPT('sql command','123') and I get byte[] which has 3 elements, everyone with '97' value which represents 'aaa' - NOT 'ąąą'. How to use AES_DECRYPT/ENCRYPT in a way which allows me to send/get polish characters to my DB?! Or how to get string output from aes_decrypt() instead byte[]?

Upvotes: 2

Views: 6159

Answers (3)

rossum
rossum

Reputation: 15693

Your MySQL data is in characters, while encryption works on bytes. You need to convert your characters to bytes before encrypting them, and also convert the decrypted bytes back to characters. That means you need to explicitly specify the character encoding to be used at both ends so they match. The current standard is UTF-8, so you should specify that at each end. If UTF-8 does not work, then try some Microsoft specific character encoding at both ends.

Upvotes: 1

Tommaso Belluzzo
Tommaso Belluzzo

Reputation: 23685

Why don't you just implement encryption/decryption in code instead of in queries?

private static Byte[] Encrypt(String toEncrypt, Byte[] Key, Byte[] IV)
{
    CryptoStream streamCrypto = null;
    MemoryStream streamMemory = null;
    RijndaelManaged aes = null;
    StreamWriter streamWriter = null;

    try
    {
        aes = new RijndaelManaged();
        aes.Key = Key;
        aes.IV = IV;

        ICryptoTransform encryptor = aes.CreateEncryptor(aes.Key, aes.IV);

        streamMemory = new MemoryStream();
        streamCrypto = new CryptoStream(streamMemory, encryptor, CryptoStreamMode.Write);
        streamWriter = new StreamWriter(streamCrypto);

        streamWriter.Write(toEncrypt);

    }
    finally
    {
        if (streamWriter != null)
            streamWriter.Close();

        if (streamCrypto != null)
            streamCrypto.Close();

        if (streamMemory != null)
            streamMemory.Close();

        if (aes != null)
            aes.Clear();
    }

    return streamMemory.ToArray();
}

public static String Decrypt(Byte[] toDecrypt, Byte[] Key, Byte[] IV)
{
    CryptoStream streamCrypto = null;
    MemoryStream streamMemory = null;
    RijndaelManaged aes = null;
    StreamReader streamReader = null;
    String output = null;

    try
    {
        aes = new RijndaelManaged();
        aes.Key = Key;
        aes.IV = IV;

        ICryptoTransform decryptor = aes.CreateDecryptor(aes.Key, aes.IV);

        streamMemory = new MemoryStream(toDecrypt);
        streamCrypto = new CryptoStream(streamMemory, decryptor, CryptoStreamMode.Read);
        streamReader = new StreamReader(streamCrypto);

        output = streamReader.ReadToEnd();
    }
    finally
    {
        if (streamReader != null)
            streamReader.Close();

        if (streamCrypto != null)
            streamCrypto.Close();

        if (streamMemory != null)
            streamMemory.Close();

        if (aes != null)
            aes.Clear();
    }

    return output;
}

In your code, you encrypt your string and then you send encrypted data to the database:

Byte[] encrypted = Encrypt(yourString, Key, IV);

When you pull out data from the database, you just get back your string using:

String decrypted = Decrypt(dbData, Key, IV);

If you don't like this way, just use your queries like so:

INSERT INTO mysecrets (mysecret1, mysecret2) VALUES (AES_ENCRYPT(secret1, YOUR_ENCRYPTION_KEY), AES_ENCRYPT(secret2, YOUR_ENCRYPTION_KEY))

SELECT AES_DECRYPT(mysecret1, YOUR_ENCRYPTION_KEY) AS secret1, AES_DECRYPT(mysecret1, YOUR_ENCRYPTION_KEY) AS secret2 FROM mysecrets

Upvotes: 1

Pitsanu Swangpheaw
Pitsanu Swangpheaw

Reputation: 672

convert using encoding might help you.

select convert(aes_decrypt(aes_encrypt('ąąą', 'abcdefg'), 'abcdefg') using UTF8);

Upvotes: 4

Related Questions