[adsense id=”0514458240″ width=”468″ height=”60″]
How can we export data to a CSV file keeping the contents confidential. Fortunately MySQL provides sufficient tools which permits this operation to happen.
Firstly we need to export the look at encrypting the contents. We can use some of the standard encryption/decryption functions available in MySQL.
Let’s imagine we have a table with sensitive data that needs to have some field content encrypted.
mysql> select * from users; +-----+----------+------+ | uid | username | name | +-----+----------+------+ | 1 | Tom | Ron1 | | 2 | Dick | Ron2 | | 3 | testdemo | Ron3 | | 4 | testmore | Ron4 | | 5 | testmore | Ron5 | | 6 | testmore | Ron6 | | 7 | testmore | Ron7 | +-----+----------+------+ 7 rows in set (0.00 sec)
We will use the ENCODE/DECODE functions available to encrypt and decrypt the individual fields. For these functions we need to pass in a key for encryption and decryption. You need to make sure that the key is kept secret.
mysql> select username, encode(username, 'password') from users; +----------+------------------------------+ | username | encode(username, 'password') | +----------+------------------------------+ | Tom | ^?? | | Dick | ?^? | | testdemo | m5:?y?? | | testmore | m5:???5 | | testmore | m5:???5 | | testmore | m5:???5 | | testmore | m5:???5 | +----------+------------------------------+ 7 rows in set (0.00 sec)
We can see that the content is encrypted. Now we need to export the fields to a flat file:
mysql> select uid, encode(username, 'password'), name from users into outfile '/tmp/users.csv' fields terminated by ',' optionally enclosed by '"'; Query OK, 7 rows affected (0.01 sec)
This now produces a CSV type file which we will use for importing. Here is a view of the contents of this file:
$ cat /tmp/users.csv 1,"^??","Ron1" 2,"?^?","Ron2" 3,"m5:?y??","Ron3" 4,"m5:???5","Ron4" 5,"m5:???5","Ron5" 6,"m5:???5","Ron6" 7,"m5:???5","Ron7"
Now that we have this we now need to import. By using session variables we can load the contents of the encrypted string to the session variable and then decrypt the content as follows:
I have written a post which shows how we can manipulate the loaded content using the LOAD DATA INFILE which might be useful here
mysql> load data infile '/tmp/users.csv' into table usersimport fields terminated by ',' optionally enclosed by '"' (uid, @username, name)
-> set username = decode(@username, 'password');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from usersimport;
+-----+----------+------+
| uid | username | name |
+-----+----------+------+
| 1 | Tom | Ron1 |
| 2 | Dick | Ron2 |
| 3 | testdemo | Ron3 |
| 4 | testmore | Ron4 |
| 5 | testmore | Ron5 |
| 6 | testmore | Ron6 |
| 7 | testmore | Ron7 |
+-----+----------+------+
7 rows in set (0.00 sec)
[googleplus]