Some background:

I receive data in text files of over 16 million records (lines). I need to be able to encrypt the first column in that file so that it cannot be deciphered. This process will be carried out on two data sets with the same first column. The encryption on both these separate files will allow the two sets of data to be joined together on the encrypted column. One of these files is from our company and the other is a different company. When the join process is complete the column can be deleted. My boss wants a standalone application that can do this as it cannot be guaranteed that when we go offsite the company will have that software. He wants the encryption to be able to be carried out on the companies pc so that before we receive the data that column is encrypted.

Any help is appreciated.


It's hard for me to describe this idea to you so bare with me.

Basically it will be one application that accepts a text file as input (this is because different sources will have different software and text files are the standard that everyone knows). The user then inputs a password and/or padding string for the encryption and then the application will encrypt the specified number of charters on each line of the text file and output that to another text file.

Example of input data:

|    ID    |    FName    |    SName    |    Number    |    Pcode    | 
|5364gfyue8|    Jason    |    Young    |   65782947   |   BT753HY   | 
|8472hgiek5|     Ben     |    Tops     |   87385942   |   BT439ES   | 
|0243hgqwl4|    Philip   |   Moore     |   73894769   |   BT641II   | 

Example of output data:

|    ID          |    FName    |    SName    |    Number    |    Pcode    | 
|85hftFHDYTE*=jcg|    Jason    |    Young    |   65782947   |   BT753HY   | 
|5jgj59GUF55=hf4!|     Ben     |    Tops     |   87385942   |   BT439ES   | 
|jhIOPfd5GF^83=3c|    Philip   |   Moore     |   73894769   |   BT641II   |
  • $\begingroup$ This is off-topic here — it's a programming question. By the way it sounds like you're looking for a hash function, not an encryption function. $\endgroup$
    – hunter
    Commented Jun 26, 2014 at 11:37
  • 2
    $\begingroup$ This question appears to be off-topic because it is about programming $\endgroup$
    – hunter
    Commented Jun 26, 2014 at 11:40
  • $\begingroup$ @user3770896 - I don't get it either. The only reason you would hash and/or encrypt the column (from what I understand) is if doing so would protect the identity of the person it pertains to. However, you've stated that the column is a random string. Anyway, mysql has native hash and encryption (AES-128-ECB, be careful with that) functions. Doing joins using those functions is trivial... software for this purpose would be a bit over the top. $\endgroup$
    – hunter
    Commented Jun 26, 2014 at 12:38
  • $\begingroup$ cleaned up some old comments Thanks for updating. $\endgroup$
    – mikeazo
    Commented Jun 26, 2014 at 15:53

1 Answer 1


Here is the deal. You can do this pretty easily in SQL. Exactly how is off-topic on this site. I'd suggest one of the other SE sites. Maybe StackOverflow or DBA.

That said, I am writing this as an answer for a reason. My reason is to persuade you to not do it. Here is why, it gives your customers a false sense of security. Your approach to doing the join is to use deterministic crypto, AES in ECB mode or a SHA2 hash of the values in the column. The problem is, there is a one-to-one mapping from original plaintext to ciphertext. So, anyone who knows that mapping will be able to reverse the mapping.

So then the question is, who knows the mapping. Well, you do (and by you I mean your company). So it isn't really doing much to anonymize the data from your prospective. Also, if you are giving your encryption software to your customers so they can run it on their machines, they can easily figure out the one-to-one mapping by reverse engineering your code (this is especially true if you use something like AES with a fixed key).

So, in short, I would suggest you don't do what you are planning on doing. As to what a better solution to the problem is, I'd suggest asking that as a separate question on here. Start by defining the problem with some examples and make sure you specify exactly what you want to protect and from whom you are protecting it.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.