How to encrypt the existing column in mysql table?

Question

I have table with password field which is a plain text. Now, I want to encrypt the field. how to do ?

Answer 1

MySQL has a wide range of built-in encryption functions, you can find a detailled overview here.

You might want to have a look at e.g. AES_DECRYPT() and AES_ENCRYPT().

INSERT INTO table (mycolumn) VALUES(AES_ENCRYPT('Hello!', 'encryption_key'));
SELECT AES_DECRYPT(mycolumn, 'encryption_key') FROM table;

Answer 2

alter table tableName  modify columnNameToBeEncrypted varbinary(200);
update tableName 
set columnNameToBeEncrypted = aes_encrypt(tableName.columnNameToBeEncrypted, 'secretyKey');

First update the column to be encrypted to be of varbinary type. This will enable saving of encrypted values.
Then update the table to store the encrypted value in place as shown above. This will save the encrypted value in the table.

To view this value, use the following query:

select *, CAST(aes_decrypt(columnNameToBeEncrypted, 'secretyKey') AS CHAR(40)) from tableName;

Above SQL queries have been tested on MySQL.

Answer 3

Take backup of your database (data only) as csv file. Use the following query to update existing fields with key:

LOAD DATA INFILE 'C:/Sample.csv' INTO TABLE myTable(myname,@mypass) SET mypass=AES_ENCRYPT(@mypass,'key:test');

Hope it will solve your problem.

Answer 4

Do an update on the password field to change the password to be hashed. You could use SHA1 for this. You want to hash the password, not encrypt.

When checking the supplied password is correct, you will need to hash the user supplied value and compare it to what is in the database field.

When hashing the password, you want to use a salt of some kind. Ideally this should be different for each user. A similar question contain good answers is: Secure hash and salt for PHP passwords

Answer 5

you can use md5 or password, but be careful of rainbow tables

mysql> select md5('a');
+----------------------------------+
| md5('a')                         |
+----------------------------------+
| 0cc175b9c0f1b6a831c399e269772661 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select password('a');
+-------------------------------------------+
| password('a')                             |
+-------------------------------------------+
| *667F407DE7C6AD07358FA38DAED7828A72014B4E |
+-------------------------------------------+
1 row in set (0.00 sec)

Some ppl just encrypt the password one way and add some dumb text, I.E

lets say that you want to encrypt "Hello" and you are going to use MD5, you will do:

mysql> select password('dumbtext Hello');
+-------------------------------------------+
| password('dumbtext Hello')                |
+-------------------------------------------+
| *1F2CE4EA3F6F689369453F090A660A9D0314AD90 |
+-------------------------------------------+
1 row in set (0.00 sec)

then if you want to validate session you just make the match between the password that the user use in the input field of your form:

if (md5("dumptext". user(password_input))== field in database, then Session ON!. if not Reject

here is the Mysql password encrypt information, there are different options, that's up to you

Answer 6

What made me stumble on your post was because I had the same problem that you had, but somehow I figured it out. I had initially created a table and inserted data into it, without encrypting it: i later discovered that there was a way of encrypting data in mysql, but it is with only with using a query and the md5(), sha(), sha1(), sha2() and maybe more.

This is what I did.

You have to create another table that is identical to your current table (but with a different table name of course) .

INSERT INTO users_new(userID, password) SELECT userID, sha1(password) FROM users;

Now note that these are two different tables (users and users_new) these tables have identical column names and characteristics. The command copies the value of the userID and an encrypted (using the sha1() function) password into the table users.

Remember to make the column password to be of type VARCHAR(40) because the decrypted character length is 40bytes(characters) long.

Hope this was helpful. Post you comment or leave a reply.

more Grace to you.

Answer 7

There are 2 philosophies of encrypting data:

• The 1-way philosophy encrypts or hashes the data with an algorithm so that the original string cannot be obtained back. This can be done using MD5 or SHA1.

  insert into users(user,password) values ('userLogin', md5('myPassword') );

  update users set password = sha1('myOtherPassword') where user='userLogin';

The way of validating this, is by applying the method (sha1 or md5) on the password the user gives and the resulting hash is compared with the one stored in the DB.

select id from users where user='userLogin' and password = sha1('passwordFromUser')

• The Bidirectional philosophy encrypts the data with an algorithm which be can encoded or decoded. This can be done with AES_ENCRYPT() and AES_DECRYPT().

  insert into users (user,password) values ('userLogin', AES_ENCRYPT('myDecodeablePass', 'myEncryptionKey'));

The way of validating this, is by using the decryption method on the stored string and comparing the result with the password the user provided

select AES_DECRYPT(password, 'myEncryptionKey') as myDecodedPassword from users 
where user='userLogin';

Please note in this way a man-in-the-middle attack could compromise the encryption key and therefore risking all the passwords, unless it's a per-user key.

Note: To avoid rainbow tables (or precomputed hash tables) it is recommended inserting a random-non-dictionary word to the string to be encrypted... since a lot of users tend to use easy passwords like 'password' or '123456'