how to encrypt a column in mysql database and further decrypt it using C# code.

Question

0.00/5 (No votes)

See more:

I have MySql database and want to encrypt the 'password' column of 'users' table, so that if any one enters to database it is not visible to him.I am trying to do it but could not succeed.
What i am doing in query is.

C#

insert into users(id,password) values('09678M',AES_ENCRYPT('secretkey'));

but it returns:The query could not be executed Where i am making mistakes.

Please guide me the correct syntax.

Thanks.

Posted 21-Nov-13 22:12pm

Member 9822231

Updated 21-Nov-13 22:14pm

Thanks7872

v2

Add a Solution

3 solutions

Solution 3

In addition to Solution 1 and 2:

The hash function you use for password storage and authentication should be cryptographic hash function: http://en.wikipedia.org/wiki/Cryptographic_hash_function[^].

The implementations are readily available, come with .NET Framework FCL. Please see my past answers:
i already encrypt my password but when i log in it gives me an error. how can decrypte it[^],
Decryption of Encrypted Password[^],
storing password value int sql server with secure way[^],
TCP Connection with username and password[^].

—SA

Posted 22-Nov-13 5:24am

Sergey Alexandrovich Kryukov

Comments

Thanks7872 22-Nov-13 12:36pm

Agreed.

Sergey Alexandrovich Kryukov 22-Nov-13 12:47pm

Thank you, Rohan.
—SA

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thanks7872 · Accepted Answer · 2013-11-21T22:21:00

Solution 1

The way you are trying to implement this is wrong at first place. Never ever store password either in plain text or in encrypted format.

The correct way is to store hash of the password. That is,try using one way cryptographic hash function like SHA256 to generate hash from password,store it in database. You should also use salt.See this link : Beginners guide to a secure way of storing passwords[^]

Whenever user tries to login,generate hash from the entered password using the same algorithm and compare it with stored one. So now,your are comparing hash to hash,not password to password.

Posted 21-Nov-13 22:21pm

Thanks7872

Updated 22-Nov-13 6:33am

v4

Comments

[no name] 22-Nov-13 4:47am

thanks for great suggestion.
does this code work for datatime datatype?

Thanks7872 22-Nov-13 4:50am

Date time? Why?

[no name] 22-Nov-13 4:55am

because i have a table application_validity(validity) and a record with deadline date.
during login it will be used to validate if current date of system exceds it user will not be able to login.
this is all for personal use regarding payment.

Thanks7872 22-Nov-13 5:00am

No need to use this approach in case of date. Simply do it once user is verified successfully.

[no name] 22-Nov-13 5:04am

ok sir, thank you.

Sergey Alexandrovich Kryukov 22-Nov-13 11:25am

You should have mentioned "cryptographic hash function" instead of just "hash". Also, it would be good to point out available implementations. And, finally, it's important to note that some implementations are broken (MD5, SHA-1), should not be used where security is a concern.
I voted 4, added my answer to clarify things, please see.
—SA

Thanks7872 22-Nov-13 12:39pm

Yes. I updated my solution. I think SHA256 is good to use where security is crucial part. What you think?

Hamassss · Accepted Answer · 2013-11-21T22:33:00

Solution 2

XML

AES_ENCRYPT should be like
<pre>insert into users(id,password) values('09678M',AES_ENCRYPT('actualpassword','secretkeyusedtoencrytp'));</pre>

But Rohan is correct, sha256 is prob too fast hash algorithm but it is still better then AES or plain text.

Posted 21-Nov-13 22:33pm

Hamassss

Comments

[no name] 22-Nov-13 4:57am

thanks for great suggestion.
but sir i want to know does this AES technique work for datatime datatype?

Hamassss 22-Nov-13 5:05am

I think it only works on string, then some type conversions should be must, if that is the case

Sergey Alexandrovich Kryukov 22-Nov-13 11:27am

You are right, but some clarifications would be needed.
I voted 4, added my answer to clarify things, please see.
—SA