locked
AES encryption and MySQL RRS feed

  • Question

  •  

    Hello All,

     

    I wish to use the system.security.cryptography aes 128 bit encryption in my c#2005 application. This uses a key and an initialization vector.

     

    MySQL uses aes 128 bit encryption and decrytption with only the key and does not use the initialization vector.

     

    My question is how can I encrypt a text string using aes and have it succesfully decrypted in mySQL using only the key.

     

    If this is possible I would appreciate any help.

     

    Harry

    Monday, January 14, 2008 9:09 PM

Answers

  • haralambos,

     

    According to your question on encrypt/decrypt the text string in MySQL, I would like to provide you the suggestions as follows:

     

    1. Keep Your Data Secure with the New Advanced Encryption Standard

     

    The Advanced Encryption Standard (AES) is a National Institute of Standards and Technology specification for the encryption of electronic data. It is expected to become the accepted means of encrypting digital information, including financial, telecommunications, and government data. This article presents an overview of AES and explains the algorithms it uses. Included is a complete C# implementation and examples of encrypting .NET data.

     

    2. Walkthrough: Encrypting and Decrypting Strings in Visual Basic

     

    The Rijndael (now referred to as Advanced Encryption Standard [AES]) and Triple Data Encryption Standard (3DES) algorithms provide greater security than DES because they are more computationally intensive. For more information, see DES and Rijndael.

     

    3. Solution in this thread: How to use AES encryption and decryption

     

    AES_DECRYPT() will return a null value if the sample key did not match to the key you used it when you fired the AES_ENCRYPT. You may try again adding a user using this statement, (note: we are assuming the fields to be your fields in your code)


    strSQL = "INSERT INTO tblapplication (user, password)VALUES('user1',AES_ENCRYPT('" & textbox1.text & "','samplekey'))"

     

    Now, you must note that the word "samplekey" will be used when you want to decrypt your password. after that you could use

    strSQL = "SELECT user, AES_DECRYPT(password, 'samplekey') FROM tblapplication"

     

    Now, see that we use the "samplekey" as our key to decrypt whatever value in the password column. when you type a "SampleKey" in there, then it should matter a lot because you use uppercase letters.

     

    4. Addtional information: Code Samples: contains references to most commonly requested code samples relevant to .NET application security.

     

    Hope that can help you.

    Wednesday, January 16, 2008 6:26 AM
  • haralambos,

     

    Thanks for the reply with the specific follow up. I would like to know, what is the error message when you try to encrypt/decrypt the string in .NET development? Is the problem on IV(initialization vector) and the password?

     

    Actually there are many examples on encrypt a string using AES in .NET development. I hope the article Encrypting Without Secrets can provide you some idea:

     

    The Encryptor.Encrypt method UTF8 encodes the string you pass in, then generates a random 256-bit AES secret key and a random 128-bit initialization vector (IV). It uses that to encrypt the data you pass in via the AES class called RijndaelManaged.

     

    The Encryptor now encrypts the AES key with the RSA public key and forms the data structure shown in Figure 4, a length-prefixed, encrypted AES key followed by the IV and the encrypted data. This binary blob of data is base64-encoded into a string, to which is prepended the key name that identifies which RSA key should be used to decrypt the AES key.


    Another example in Code Project: Simple encrypting and decrypting data in C#

     

    I am using Rijndael algorithm in this sample. The reason for this is that it is 100% implemented in managed code in our libraries, so it does not rely on CryptoAPI or any encryption packs and will work everywhere. If you need performance, I would suggest replacing it with TripleDES (it is a one line change), and if you do, also do not forget to change the IV size to 8 bytes and the Key size to 16 bytes.

     

    If there is any problem on trying the examples to emcrypt the string in .NET, please feel free to reply.

     

    Thanks again for your question and follow up.

    Thursday, January 24, 2008 7:14 AM

All replies

  • haralambos,

     

    According to your question on encrypt/decrypt the text string in MySQL, I would like to provide you the suggestions as follows:

     

    1. Keep Your Data Secure with the New Advanced Encryption Standard

     

    The Advanced Encryption Standard (AES) is a National Institute of Standards and Technology specification for the encryption of electronic data. It is expected to become the accepted means of encrypting digital information, including financial, telecommunications, and government data. This article presents an overview of AES and explains the algorithms it uses. Included is a complete C# implementation and examples of encrypting .NET data.

     

    2. Walkthrough: Encrypting and Decrypting Strings in Visual Basic

     

    The Rijndael (now referred to as Advanced Encryption Standard [AES]) and Triple Data Encryption Standard (3DES) algorithms provide greater security than DES because they are more computationally intensive. For more information, see DES and Rijndael.

     

    3. Solution in this thread: How to use AES encryption and decryption

     

    AES_DECRYPT() will return a null value if the sample key did not match to the key you used it when you fired the AES_ENCRYPT. You may try again adding a user using this statement, (note: we are assuming the fields to be your fields in your code)


    strSQL = "INSERT INTO tblapplication (user, password)VALUES('user1',AES_ENCRYPT('" & textbox1.text & "','samplekey'))"

     

    Now, you must note that the word "samplekey" will be used when you want to decrypt your password. after that you could use

    strSQL = "SELECT user, AES_DECRYPT(password, 'samplekey') FROM tblapplication"

     

    Now, see that we use the "samplekey" as our key to decrypt whatever value in the password column. when you type a "SampleKey" in there, then it should matter a lot because you use uppercase letters.

     

    4. Addtional information: Code Samples: contains references to most commonly requested code samples relevant to .NET application security.

     

    Hope that can help you.

    Wednesday, January 16, 2008 6:26 AM
  • Thank You for your response.

     

    I went through all of that but still am having trouble. The issue is that I want to encrypt data using .net framework before passing it over the internet to mysql for processing.

     

    To encrypt and decrypt a string:

    .net requires an initialization vector and password.

    mysql only requires a password.

     

     

    What I want to do.

     

    (1) Encrypt a string using .net

     

    (2) Pass it over the internet to a stored procedure in a mysql database

     

    (3) The stored procedure will decrypt the string using aes_decrypt and do some other things.

     

    (4) The stored procedure will encrypt a string using aes_encrypt and pass it back to the .net client

     

    (5) The .net client written in c# will decrypt the string for further processing.

     

     

    I am looking for advice on (1) and (5). I need the .net to encrypt a string using aes locally before sending it out.

     

     

    Harry

    Wednesday, January 23, 2008 10:03 PM
  • haralambos,

     

    Thanks for the reply with the specific follow up. I would like to know, what is the error message when you try to encrypt/decrypt the string in .NET development? Is the problem on IV(initialization vector) and the password?

     

    Actually there are many examples on encrypt a string using AES in .NET development. I hope the article Encrypting Without Secrets can provide you some idea:

     

    The Encryptor.Encrypt method UTF8 encodes the string you pass in, then generates a random 256-bit AES secret key and a random 128-bit initialization vector (IV). It uses that to encrypt the data you pass in via the AES class called RijndaelManaged.

     

    The Encryptor now encrypts the AES key with the RSA public key and forms the data structure shown in Figure 4, a length-prefixed, encrypted AES key followed by the IV and the encrypted data. This binary blob of data is base64-encoded into a string, to which is prepended the key name that identifies which RSA key should be used to decrypt the AES key.


    Another example in Code Project: Simple encrypting and decrypting data in C#

     

    I am using Rijndael algorithm in this sample. The reason for this is that it is 100% implemented in managed code in our libraries, so it does not rely on CryptoAPI or any encryption packs and will work everywhere. If you need performance, I would suggest replacing it with TripleDES (it is a one line change), and if you do, also do not forget to change the IV size to 8 bytes and the Key size to 16 bytes.

     

    If there is any problem on trying the examples to emcrypt the string in .NET, please feel free to reply.

     

    Thanks again for your question and follow up.

    Thursday, January 24, 2008 7:14 AM
  • Hi Harry,

    I found this string and while old I have to ask.  If you are working with MySQL, have you heard of ezNcrypt ?   This is a Transparent Data Encryption solution that will work with any application.  It takes all the coding work out of calling the encryption function in MySQL. 

    Hope this helps!

    Byron
    Tuesday, November 3, 2009 2:36 AM
  • [Solved] have a look at following link

    http://codereflex.net/how-to-encrypt-decrypt-in-mysql/

     

     

    Wednesday, April 20, 2011 6:44 AM
  • Yeah Why it not works in VS2008 i tried query the and it gives binary data i.e Byte array

      var ds = new DataSet();
      _adapter = new MySqlDataAdapter("SELECT     AES_DECRYPT(Component, 'admin123') AS Component FROM  intab", _connection);
      _adapter.Fill(ds);
       _connection.Close();

     

    Following Query works fine in MySQL, but not in .Net Environment as we expect to get result directly.

    Comment the code.

    Tuesday, October 11, 2011 6:51 AM