I have come across a web application that a company has asked me to rebuild. After all the implementation is said and done I will deploy on a private server, and the database will be on its own private server. I will manage the communication between the two with firewalls and the both of the servers data and web will be behind a load balancer. My question is this, the data that is being collected is extremely sensitive data, do I pick out the columns of sensitive information and encrypt them, because before now they have not been done so, or would this just be overkill. I want to securely store the information however I can be a bit intense when it comes to my concerns.

If the answer is no, that's great and I'm glad I asked. However if the answer is yes, does anyone know how I would go about updating the information to the newly necessary encryption.

The steps would need to be selecting all of the information, converting the columns from varchar to varbinary, encrypting the data, and finally inserting it back into the database.

The development stack for code to data I'm using is Java/MySQL.

  • 3
    Can you clarify what sort of sensitive data you're dealing with? Credit cards, bank account numbers, PII (Personally Identifiable Information)? Always good to know if there's a regulatory framework you need to fit into.
    – gowenfawr
    Jan 11, 2015 at 17:42
  • Personally identifiable information. Jan 11, 2015 at 17:43

2 Answers 2


I completely support the encryption of sensitive information while it is in storage. Encrypting sensitive information in the database prevents an attacker from gathering useful information if he/she has obtained the database. However, as always, how you implement the encryption system is critically important.

First we need to compare and contrast two situations in which the database is compromised:

  1. Situation one occurs when the database has been compromised, but the file system is still secure.
  2. Situation two occurs when both the database and the file system are compromised.

Situation one is when encryption is the most useful. This kind of attack occurs when the attacker has used a method like SQL injection. This is ideal because the attacker does not have a decryption key/method and he/she would have to bruteforce the decryption key to get to the data (this should be infeasible).

Situation two is far less favorable. This kind of attack occurs when the attacker has obtained access to the server (e.g., physical access, FTP, SSH, etcetera). Because the attacker has gained access to the file system, he/she likely has the decryption key which means he/she can just decrypt the database and it's game over.

However, you have an advantage here because you have both a frontend server and a backend server. This means you can implement a more secure design to protect against situation two. You can encrypt/decrypt on both servers which means the attacker would have to compromise both server file systems. Here's what I mean: the frontend server can encrypt the sensitive data using its own key; send that data to the backend server; the backend server then encrypts the encrypted data using a different key; and finally the backend server stores that double-encrypted data. The decryption process is just the reverse of this: the backend server pulls out the double-encrypted data; the backend server decrypts the first layer of encryption and sends it off to the frontend server; the frontend server then removes the second layer of encryption; finally the frontend server displays the decrypted, sensitive data to the user.

This all said, like Kevinze's answer states, there are other protections which should be in place. You should be using HTTPS (SSL/TLS) for communications between the user and the frontend server. This would prevent MiTM (in most cases) and help protect the communications between the client and the server. You should also consider other protections like full disk encryption. I cannot give you much information about this, but it would of course add protection to the file system.

In your question, you asked about updating your database and converting it to an encrypted form. If I was in your position, I would construct a new database to store the encrypted information and then write a program to read all entries from the old database, iterate over them, encrypt the sensitive columns, and insert them into the new database. Here's some pseudo-code:

All_Entries[][] = Fetch_Array_All_Entries(FROM_OLD_DATABASE); // Multidimensional Array

for(int i = 0; i < count(All_Entries); i++){
    thisEntry = All_Entries[i];
    encryptedEntry = new Array();
    encryptedEntry["Some Insensitive Column Name"] = thisEntry["Some Insensitive Column Name"];
    encryptedEntry["Sensitive Column 1"] = encryptColumn(Backend_Key, encryptColumn(Frontend_Key, thisEntry["Sensitive Column 1"]));
    encryptedEntry["Sensitive Column 2"] = encryptColumn(Backend_Key, encryptColumn(Frontend_Key, thisEntry["Sensitive Column 2"]));
    // ... Encrypt all sensitive columns and assign them to the encryptedEntry ...

    // Once all encryption is done:
    Insert_Array_Entry(TO_NEW_DATABASE, encryptedEntry);
} // end entry loop

Once the new database has been updated with encrypted entries, simply replace the old database with the new one.

Your question mentions switching from VARCHAR to VARBINARY. This is really your own choice, but you could continue to use VARCHAR if you convert the bytes to a string format. Usually encrypted data is displayed as a Base64 string. Again, you have to make that decision for yourself, but that's how I would likely do it.

  • You covered every part of my question with an exceptional answer I thank you very much and am very grateful Jan 12, 2015 at 21:17
  • No problem. Glad it was useful. As an additional note, you might need to extend the column length. Most encryption methods use block ciphers which could cause the encrypted form of the data to be longer than the original form.
    – Spencer D
    Jan 12, 2015 at 22:35
  • @SpencerDoak Sorry but SQL injection does not rely on finding any decryption or encryption keys. If your web server is exploitable, the web server will willingly handover any information as if it was servicing a legitimate request, i.e. the data will be handed over in a decrypted state. The encryption of the data on the disk storage only mitigates physical attacks. The encryption of data as it travels from one place to another prevents sniffers from reading it along the channel.
    – Kevin Lee
    Jan 13, 2015 at 9:00
  • Also I don't see the benefit of your double encryption scheme as you have suggested. With just one layer of encryption, an attacker that compromises the backend will still see encrypted data. With a well chosen cipher and key, it is infeasible to brute force even just one layer of encryption in the first place.
    – Kevin Lee
    Jan 13, 2015 at 9:43
  • That being said, the marginal benefit of storing the encrypted data on the database backend server prevents direct attacks on the database server from leaking sensitive information since the key is on the front end. However, if someone can actually do this, they can also wipe out the database, which is even worse than a DDOS attack. Therefore, the database server should not be exposed directly to the internet. Also, like I have alluded to, encryption does not mitigate online attacks on the front end.
    – Kevin Lee
    Jan 13, 2015 at 10:24

The communication channel between the browser and your server (basically any channel across the internet) should be encrypted with TLS. This encrypts and provides a secure, authenticated channel.

After the data has safely reached your database server, you should employ encryption in the disk storage since you have a responsibility to protect client sensitive information. You could explore full disk encryption (FDE) for your server as it should be more secure than just encrypting the database only (the OS itself should be encrypted). Add in a layer of physical security (door locks, security guards), and you have a professional environment. Encryption mainly protects against offline attacks, e.g. the server is physically compromised, but the data remains unreadable.

After implementing FDE, you probably don't need to further encrypt the individual database columns because there will not be much more benefit. In a realistic setup, your database doesn't just encrypt only. It should also decrypt the information on demand when it is needed. A malicious attacker can exploit your web server somehow to fool your backend into producing the decrypted information anyway. Things like sql injections, cross site scripting, and other "online" attacks which are arguably more common and more deadly. Check out the top 10 threats here from The Open Web Application Security Project.


  • Thank you for this answer, if I may ask for a little clarification on a couple of things, with fde how does this effect my interaction with the server as far as ssh, and external queriesor does it at all Jan 12, 2015 at 20:00
  • And please forgive my ignorance, I'm on an extreme learning curve here, I have been trying to cram this down my throat by the book full, the last thing I want is to jeopardize personal information Jan 12, 2015 at 20:05
  • Sure. I would say FDE only provides local encryption to the server or computer. If it is properly set up with proper passwords for server admins, then you can be confident that someone who physically steals your server will not be able to decrypt the files. Think of FDE as a background service that automatically encrypts and decrypts local data on the disk. In the application layer, FDE should be totally transparent, regardless of the kind of programs you run or how your server works. Bitlocker is an example. FDE does not substitute for external communication protocols like SSH!
    – Kevin Lee
    Jan 12, 2015 at 20:20
  • ANY program that can execute on your server can either write to disk or read from disk. FDE should automatically encrypt data before writing it to the disk, and FDE should automatically decrypt data before passing it to the requesting program to read. From the application point of view, it runs the same way regardless whether FDE is enabled or not. That's why its super important to understand that encryption mainly protects against physical attacks, and not online attacks. As long as malware can run on your server, it can still read the data regardless. Encryption is not a magic bullet.
    – Kevin Lee
    Jan 12, 2015 at 20:32

Not the answer you're looking for? Browse other questions tagged .