how to use DES_DECRYPT() function to get data from mysql?


Problem :

At last i try Encryption and Compression Functions in mysql for secure password and username.

DES_ENCRYPT function is perfectly work for encrypt. I give the code which i use to encrypt and save in database.

MySqlConnection connection = new MySqlConnection(MyConString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = "insert into user (User_Id, Password, Branch, Upto_Date) values (DES_ENCRYPT('" + textBox1.Text + "'),DES_ENCRYPT('" + textBox2.Text + "'),'" + comboBox1.SelectedItem.ToString() + "','" + textBox4.Text + "') ";
                connection.Open();
                command.ExecuteNonQuery();
                MessageBox.Show("Record Succesfully Added", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                connection.Close();

But i have trouble to retrieve data in DES_DECRYPT function.

I try "select DES_DECRYPT(User_Id),DES_DECRYPT(Password),Branch,Upto_Date from user" code to retrieve but no use,. What i do for get the data from mysql datatable?.

My sql datable

the last record is inserted using DES_ENCRYPT() function.



Solution :

DES_DECRYPT is the way to decrypt DES_ENCRYPTed data.

Lets stick to the SQL part of it. Easy example:

/* generating test table */
CREATE TABLE `test` (
  `testField` varchar(512) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

/* adding some test data to it */
INSERT INTO test () VALUES (DES_ENCRYPT("Hello")), (DES_ENCRYPT("World"));

Now to find out how it does look encrypted...

SELECT testField FROM test;

+-------------+
| testField   |
+-------------+
| €p!˜*D°k    |
| €ü«ü€D»Ûë   |
+-------------+

...and decrypt it!

SELECT DES_DECRYPT(testField) FROM test;

+-------------+
| testField   |
+-------------+
| Hello       |
| World       |
+-------------+

Basically, if you decrypt it this way as you've described it, the database should return the plain text.

My advice:

  1. Try to start with figuring this out on the SQL side first (phpmyadmin, SQLyog, there are plenty of tools to directly work on the MySQL database).
  2. If this is working, move to your .NET program to finish this.

Another note, if you use this kind of password protection, a clear password is used between your application and the database server. Even if this connection might be SSL-"protected", I'd advise to not do it this way. It usually is safer to use Hash-Algorithms (like SHA512) for this purpose and just store the Hashes in your database to avoid clear passwords at all.


Mysql Tutorials

Mysql Howto..

How do I properly connect to a mysql database?

How to store a set of values from another table in a field in MySQL

how to ensure mysql column has 4 character

How to integrate AJAX to avoid page redirection of PHP CMS

How do I utilise longitude/latitude values efficiently in Mysql?

How to connect to mysql database using only ajax and javascript?

How to extract MySQL data monthwise and daywise to populate table to draw graph using PHP libchart?

How to select from MySQL where Table name is Variable

Mysqldump with InnoDB tables, how to import them without errors?

How to upload, extract, store .csv data to server and transfer to mysql

MySQL how to check if selected column is null before woking with him?

MySQL - How to select Minium AND Maximum in one (UNION) Query

How to get next record inside the mysql result dataset?

How to update certain column in a table based on duplication of other columns?

How can i count transactions executed in a day in mysql workbench?

How to protect form mysql injection through address bar

How do I retrieve results as multidimensional array from mySQL and PHP?

how do I use mysql real escape string?

How to store tweets from Twitter Streaming API in MySQL with SSH closed

how to connect mysql database to laravel in ubuntu

How to Properly Convert or Query Date Range for Rails / MySQL DateTime Column

How do you make a widget that uses data from mysql using php like the link below? [closed]

How to join 3 table with this condition?

How to enable LDAP connection for MySQL using java? [closed]

How to escape quotes “” characters in MySQL and Java

How to order by custom rule, e.g. how to order like 4,2,1,3

MySQL query with 2 SELECTs, UNION and LIMIT - how to count all items?

How do I get first name and last name as whole name in a MYSQL query?

How to reference a table by id

SQL - How to find index