2

Lets assume I have a table with 10,000 rows. What is the fastest way to encrypt every value for a specific column with AES_ENCRYPT using the same AES password/salt?

I actually will have to do this across many tables and many columns, so I am trying to find a quick way to do this.

Assuming that I have changed the type to the city_name column below to BLOB already, is there someway I could import the data back into mysql using a query like this (I am just showing the first 10 inserts, but this table could have 10,000+ of rows):

INSERT INTO `cities` (`id`, AES_ENCRYPT(`city_name` `password`), `state_id`, `country_id`) 
VALUES
(1, 'Feyzabad', 15, 1),
(2, 'Baghlan', 17, 1),
(3, 'Nahrin', 17, 1),
(4, 'Pol-e Khomri', 17, 1),
(5, 'Cool urhajo', 18, 1),
(6, 'Farakh', 19, 1),
(7, 'Andkhvoy', 20, 1),
(8, 'Ghazni', 21, 1),
(9, 'Shahrag', 22, 1),
(10, 'Gereshk', 23, 1);
r.vengadesh
  • 1,721
  • 3
  • 20
  • 36
fakeguybrushthreepwood
  • 2,983
  • 7
  • 37
  • 53
  • Hi @Paul, how would you see an UPDATE solving this particular issue? – fakeguybrushthreepwood Jul 05 '13 at 07:48
  • 1
    `UPDATE cities SET city_name = AES_ENCRYPT(city_name, ..)`. However, I think this is probably the "wrong approach" for this - whatever it is, wanting to encrypt a bunch of columns across a number of tables seems very suspect. Unfortunately, it doesn't seem as though MySQL (natively) supports Transparent Data Encryption (TDE). – Paul Jul 05 '13 at 08:02
  • Thank you for introducing me to TDE. I would be really interested to hear why you feel this approach is suspect? – fakeguybrushthreepwood Jul 05 '13 at 08:12
  • What is the goal trying to accomplish? Do you *really* want to have to unencrypt the data at every usage? Do you want to give up any semantic length or check constraints? Do you want to give up being able to use these columns in indices? How will the key be protected such that it is safer than no encryption at all? (The key will show in standard query logs.) – Paul Jul 05 '13 at 08:25
  • Also, I'm no crypto expert, but using the same key/IV for a bunch of small values like this might be exploitable like the old (broken) WEP standard .. – Paul Jul 05 '13 at 08:32
  • Thanks @Paul, lots of important points - many I have considered, but some I definitely want to explore more. Overall, this is actually just the first step in encryption on this project that I am working on and I will build upon it in the future. – fakeguybrushthreepwood Jul 05 '13 at 08:34

0 Answers0