How to store sensitive information in SQL Server 2008?

Question

I need to store some sensitive information in a table in SQL Server 2008. The data is a string and I do not want it to be in human readable format to anyone accessing the database.

What I mean by sensitive information is, a database of dirty/foul words. I need to make sure that they are not floating around in tables and SQL files. At the same time, I should be able to perform operations like "=" and "like" on the strings.

So far I can think of two options; will these work or what is a better option?

1. Store string (varchar) as binary data (BLOB)
2. Store in some encrypted format, like we usually do with passwords.

Answer 1

A third option, which may be most appropriate, is to simply not store these values in the particular database at all. I would argue that it is probably more appropriate to store them elsewhere, since you're probably not going to JOIN against the table of sensitive words.

Otherwise, you probably want to use Conrad Frix's suggestion of SQL Server's built-in encryption support.

The reason I say this is because you say both = and LIKE must work across your data. When you hash a string using a hash algo such as SHA/MD5/etc., the results won't obey human language LIKE semantics.

If exact equality (=) is sufficient (i.e. you don't really need to be able to do LIKE queries), you can use a cryptographic function to secure the text. But keep in mind that a one-way hash function would prohibit you from getting a list of strings "un-hashed" - if you need to do that, you need to use an encryption algo where decryption is possible, such as AES.

Answer 2

If you use rot13, then you can still use = and LIKE. This also applies to any storage method other than an SQL database, if preventing casual/accidental views (including search engine indexing, if the list is public) is that important.