How We Encrypt Data In MySQL With Go

Posted by Baron Schwartz on Jul 31, 2017 10:17:00 AM

A SaaS product needs to use security measures you might not ordinarily use in an on-premises solution. In particular, it’s important that all sensitive data be secured. Encryption plays an important role in information security. At VividCortex, we encrypt data in-flight and at-rest, so your sensitive data is never exposed.

We use Go and MySQL extensively at VividCortex and thought other Go programmers might be interested to see how we’ve integrated encryption into our services layer (APIs). (And if you'd like to learn more about programming with Go in general, please take a look at our free ebook The Ultimate Guide to Building Database-Driven Apps with Go.)

Image Source

Encryption Techniques

At a high level, you can think of two kinds of data encryption inside of MySQL or any similar data store. I’ll oversimplify for purposes of illustration. You can:

  1. Store the data in MySQL as normal, but encrypt the container that holds MySQL. Usually this means storing MySQL’s data on an encrypted disk volume. The protection? Broadly speaking, if someone gains access to a backup disk, they can’t see your data.
  2. Encrypt the data before sending it to MySQL. In this case the security boundary is pushed out further: even if someone gets access to the server, and can run SQL commands, they can’t see your data.

Each of these has advantages and disadvantages. These include ease of use, programmer overhead, ability to inspect (e.g. recovering from backups), searchability and indexability, and so on. There are a lot of things to consider here. Just a few:

  • Will data be exposed if backups are unencrypted? (Our backups are encrypted, by the way.)
  • Are sensitive values possibly in cleartext in query logs?
  • Will sensitive values be visible in status commands like SHOW FULL PROCESSLIST?

At VividCortex we err on the side of safety and security, rather than favoring convenience. There’s a fairly simple question that does a pretty good job of illustrating our goal: if someone succeeds in a SQL injection attack against our databases, will they see any sensitive data in cleartext? The answer needs to be “no.” This is a higher standard than on-disk encryption. It means that someone has to get access to the keys for the particular data they’re trying to decrypt, in order to see anything other than garbage. And those keys are not present or accessible on the database servers in any form, not even in-memory.

Making It Convenient

Convenience is important. If it’s too hard to do encryption, there’s an increased risk that it won’t be done. Fortunately, Go’s elegant interfaces for the database/sql package make the burden transparent to the programmer!

We learned how to do this from Jason Moiron’s excellent blog post on the Valuer and Scanner interfaces. Please read that if you haven’t yet.

To implement transparent encryption and decryption, we created a custom data type that implements the Valuer and Scanner interfaces. The implementation is straightforward and quite similar to Jason’s example of compressing and decompressing, except that we used encryption libraries instead.

Now our code is incredibly simple to use with encrypted values. All we do is define a variable of our custom type. For example, instead of

var password string
err = rows.Scan(&password)

We simply use

var password EncryptedValue
err = rows.Scan(&password)

It’s similarly simple to insert values encrypted into the database. Magic! This is why I often say that Go’s design, although it seems minimalistic at first, is actually very advanced and powerful.

Nuts And Bolts

The code is small. The exact details of all the code are not all that important for this blog post; much of it is about things that are out of scope here. The gist of it, though, is that we store values as byte arrays:

  • The first byte is an indicator of the version of our encryption algorithm used, so there’s a clear migration path for changes.
  • The next four bytes indicate which key we used to encrypt this value, so we have 4 billion possible keys.
  • The rest is the encrypted payload.

We can even change this in the future. For example, we can switch on the first byte’s value, if we want, to determine whether the key ID is in the next 4 bytes, or if it’s something more, such as the next 8 bytes. So we can easily expand the number of keys we can indicate. We can also, if we ever hit version 255, use that to indicate that the version number continues in the next byte. This is a standard trick used, among other places, by the MySQL wire protocol.

The result is that we have a simple and future-proof way to encrypt values.

Alternative Approaches

In addition to the approaches we’ve mentioned, there are several others. There are commercial projects designed to help ease the encryption and decryption techniques you might otherwise wrap around MySQL and perhaps fumble in some ways. There are encryption functions inside of MySQL—but educate yourself about those before using them. There are others, too, but you should be able to find all you need with a search.


By using Go’s built-in interfaces, we created a solution for transparently encrypting values in our database so that it’s never in the database in cleartext, either on-disk or in-memory. The code is easy for programmers to use, which improves our security posture automatically. All sensitive data gets encrypted in-flight and at-rest, and an attacker would have to have extensive access to our systems (an SQL injection wouldn’t suffice) to be able to decrypt the data.

We highly recommend that you use the standard Go interfaces for the power they give you. And please, ask your SaaS providers, including us, hard questions about security and how it’s implemented. Every service needs to be secure to make the Internet a safer place.

If you liked this and want to learn more about Go, you might also like our webinar about developing database-driven apps with Go and MySQL. Click below to watch a recording.

Watch the Webinar

Post Updated 7/31/2017

Recent Posts

Posts by Topic

see all