how can I encrypt an entire column

Question

Hello All,

I'm setting up a mail server and I'm mysql for the passwords table. Currently it's secured using the ENCRYPT() function however I have a large CSV I'd like to import where the passwords are all plain text. Is there any way I could import these as plain text and then run a query to run ENCRYPT() on the whole column and update everything?

Answer 1

You can do this with a simple update:

UPDATE your_table SET password_col = ENCRYPT(password_col,'some salt')

Or, if you are using LOAD DATA INFILE to populate the table, you can encrypt the passwords then:

LOAD DATA INFILE '/tmp/data.csv' INTO TABLE your_table 
(col1,...,@password,...,coln) 
set password_col = ENCRYPT(@password,'some salt')