How do I encrypt data in my database?

[mc4wp_form id=”427"]

It’s a rough, tough world out there. There’s bad guys lurking round every street corner, just looking to break into your database and sup on the sweet, sweet data within. Mmmm, sticky, gooey records …

Most databases that get exposed tend to just have the data contents leaked. So if you’ve got anything sensitive in there, you’ll want it to be encrypted, so when the bad guy is looking through it all, he’s going to have a tough time getting at the good stuff.

Of course, there’s a balancing act here — the very act of encrypting your data in a way that’s secure adds to the time it takes to read or write the record.

The first thing to note is

If you’re thinking of writing your own encryption functions, just stop now!

This is maths that I can’t even begin to understand. There’s some of the brightest brains on the planet working on this stuff and sometimes it takes years for their mistakes (and hence weaknesses) to be noticed.

With that out of the way, we need to understand a bit about what you’re trying to encrypt.

Is it the user’s password?

If so, this is a perfect candidate for one-way encryption. The user provides their password, you pass it through a one-way cryptographic hashing function, then store the result in the database. When you want to check if the user has typed in the right password, you pass their input through the same function, then compare it to the value in the database. This way, the unencrypted password is never written to disk or stored anywhere. And as a one-way cryptographic hash function is one-way, it’s really hard to figure out the password given it’s cryptographic representation.

Or is it?

Actually, instead of trying to figure out the reverse of your hash function, people have just built huge tables of words/phrases and results — then they just look them up. So if they find the string “lsdfjlksdjfo2394uhadskjh9” in your database, they look it up in their table and realise the password is “secret”.

So to make their lives that bit harder, we add a “salt” to the password. This is basically a random prefix or suffix added to the password itself. So if your user typed in “secret” you might actually send “secret934hdsk84h” to your hash function. Unless your attacker has a table generated using that exact same salt, they’re unable to just reverse lookup your password.

Now that all sounds like a lot of work.

Luckily, like most things, Rails has made it easy (although it hides the salt part from you so you have a bit less control).

Add gem ‘bcrypt’ to your Gemfile, bundle install and then you’re halfway there. On your Person model (or User if you must call it that) add a field called “password_digest” and then add a declaration has_secure_password.

To save a password, use the following:

@person = Person.create! login_name: 'susan', password: 'securepassword99', password_confirmation: 'securepassword99'

Then to test whether Susan has provided the correct credentials:

@person = Person.find_by!(login_name: 'susan').authenticate('securepassword99')

@person will either contain Susan’s record or false if the wrong password was supplied.

Simple eh?

However, sometimes, that’s not what we need.

Instead we want to store something sensitive in the database, and we need to retrieve it again later.

For example, one of the apps I’m working on at the moment has a per-user login to a 3rd party service. The 3rd-party doesn’t support OAuth; I need to provide an actual username and password with each API call.

So I ask our visitor to supply their username and password as part of the registration process and store it, encrypted, in the database. Then when the time comes to make an API call, I retrieve those values, decrypt them, and then make the call.

How do I perform such sorcery?

Easy — with the attr_encrypted gem.

Add gem ‘attr_encrypted’ to your Gemfile, bundle install and then update your Person model. Add two fields; encrypted_api_username and encrypted_api_password and declare them in your Person model:

class Person < ActiveRecord::Base
attr_encrypted :api_username, key: 'some really long secret key'
attr_encrypted :api_password, key: 'some other really long secret key'

This can be used as follows:

@person = Person.create! login_name: 'george', api_username: 'georgio', api_password: 'moroder'

The API username and password are passed through a two-way encryption function and written to the encrypted database field.

Later when you access:


the gem retrieves the encrypted attributes, decrypts them for you and then makes them available for you. Completely transparently.

attr_encrypted uses your machine’s installation of OpenSSL, with the aes-256-cbc algorithm used by default — although it’s trivial to switch it (and research it yourself as to which is the safest to use). You can also get it to read the key at runtime — so you can store them in your server environment, rather than in your source code.

So now you have no excuse for leaving those passwords and other important bits of data in plain text. For when you get hacked, and you will get hacked one day, it’s your responsibility to keep these things safe.

[mc4wp_form id=”427"]