The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.


In my latest project, I've got to store encrypted data within a database. The table will have several fields, but only one needs to be encrytped. This will allow searching of the remaining fields without having to decrypt every row.

The problem I have is how to encrypt the data. I've only previously had to encryt small individual files where it is obvious to use the user defined password during the encryption. If the user changes their password, the file is decrytped with the old password and re-encrytped with the new password.

Is this still the correct method for my new database project. Do I have to select all the records, decrypt the relevant field and then re-encrypt that field with the new password before doing a bulk update?

The only other solution I can think of is to enrypt with a secret password hidden within my application. This seems very dangerours as a single brute force attack against one installation would instantly compromise every other installation.

Any suggestions?
Thursday, February 23, 2006
Does your DB support encryprion?
I know Oracle has some features for encrypting data...
Honu Send private email
Thursday, February 23, 2006
I think you have two topics going on here ... first is just the encryption part, the second is key management. 

As far as the encryption goes, you will almost certainly want to have a "secret" key somewhere (need not be "in" accessible code, and in fact probably should not be).  There are lots of suggestions on where to store the key ... a quick google search should get you ideas from which you can pick for you particular situation (web app / desktop / server app / other). 

Once you have that in place, you can maintain two columns in your db, an initialization vector (IV) and the actual encrypted data.  An IV is just a seed hash that can be public, but helps obscure the data even more.  The IV by itself apparently does not assist in "cracking" the encryption, but rather makes it more unbreakable over large amounts of data.  Using a decent algorithm like the Rijndael cipher with a 128 bit plus key and this concept will pretty much make your data unbreakable.

As for key management, I already touched on that a bit w/ bit on key storage.  Beyond that, if you anticipate (and you should at least have a plan for) your key being compromised, you absolutely must have a procedure for unencrypting / re-encrypting data.  This may be manual or automated, but you need a plan or utility to do this.

Enough rambling ... let me know if this helps or not.  If this is off the mark, fire away and I'll try again :-)
brad Send private email
Thursday, February 23, 2006
On Honu's note ... my concern with using database level encryption (and I haven't, just experimented, so not an expert on field level stuff) is that it works great at protecting data from unauthorized DB users, but anyone who was legally allowed to see any of the data could see all of the data.  Using an extenternal routine as described above can allow authorized users to select / manipulate the "regular" data but the encrypted stuff just looks like garbage.  Is this a misconception on my part or is this figured in to stuff like Oracle / Sql 2005 (which apparentl has field level encryption)
brad Send private email
Thursday, February 23, 2006
Adrian, what need is being addressed here? Tell us exactly why you want this data encrypted at the data field layer, then maybe we can figure out how best to handle key management in this case.

Any halfway decent database engine will have access control built in. If you're encrypting that means you want some protection over and above this. Protection from whom? What attack scenarios are you guarding against?
Thursday, February 23, 2006
If it is something like a credential vault, where it is storing my password for use elsewhere, look at how unix encrypts passwords (or Google "credential vault").

You will need a way to reset it at some point if this is what you are planning. 

I am with Rowland, a little detail would go a long way.  Changing, viewing, etc. would all be handled through security so this sounds different.
Thursday, February 23, 2006
Passwords are usually "encrypted" with a one-way hash function, and not a traditional encryption algorithm.  This is so if someone steals the password file/database, they can't reverse engineer the password values.

Also - you probably want to make sure this DB column isn't indexed.  ;-)
example Send private email
Thursday, February 23, 2006
SQL Server 2005 has a scheme with certificates where you can protect the database, even from the dba. I think there is an MSDN magazine article with more details

I don't know if you can encrypt single fields (with the certificates), as I only noticed the stuff in passing. If you're looking to protect, say, passwords, you might want to use a hash. Run the pwd through the hash and compare that. Unix only did this sort of thing, oh, say, like 25+ years ago. But since everyone wants to reinvent the wheel themselves, we're only starting to see databases with encrypted columns.

If you need something reversible, for maybe protecting columns like SSN or credit card numbers, if you're using sql server 2005, you can do some odd things that look interesting, such as making CLR (.net) objects, procedures or triggers in, oh, maybe c# or

Different things need different protection schemes.
Thursday, February 23, 2006
I'm still digesting what some of you have suggested but whilst I'm trying to get up to speed on some of the terms/principals, I'll clarify a few points.

1) The database provider is unknown. Potentially it will be anything from Access, SQL Server, Oracle, iSeries. The connection is via ODBC therefore we are restricted to features that are supported by ODBC.

2) The easiest way to explain what is required is to imagine a table storing processed orders. It might have the following fields:

Company policy (the men in grey suits) have dictated that the CreditCardNumber column must be encrytped but we still want to be able to "Select * Where CustomerName='Adrian'"
Thursday, February 23, 2006
For credit-card numbers you'll want reversible encryption because there's a chance you'll need to handle charge-backs within the 90-day period from your merchant account provider.
example Send private email
Thursday, February 23, 2006
Databases are Hash-ed not encrypted. Better yet you should hash with a salt. You should also study little bit about 'Iteration count'( which can be found at PKCS# 5,see below).
Either get a copy of Applied Cryptography OR read online at RSA Labs.
Further i suggest you should study PKCS # 5: Password-based Cryyptography standard, which can found here
0xff Send private email
Thursday, February 23, 2006
If it is Credit cards, you need a way for the system to decrypt the card number for use, while keeping the actual number away from anyone viewing the field.  I suggest looking here first:

If you Base64 encode all the binary data, you can store the string in the database.  Key storage can go in several places, the easiest being in a compiled component and the most secure being a credential vault.
Thursday, February 23, 2006
Without knowing how much you know about security in general and encryption in specific: Security is a process, not a product. If you don't want to be the next company mentioned in the press for a million credit card number's leak, definitely ask someone who knows the field - even when you have to pay a bit more. If you aren't this someone (if you would you wouldn't ask here), then your homegrown security scheme is doomed to fail.
Friday, February 24, 2006
>>Security is a process, not a product

Couldn't agree more. As they say 'Security is a journey, not a destination'. So don't don't try to be a snake oil, first read about it in both depth and breath OR bring in someone who is well-versed and respected among his peers in security arena and can do it for you.
0xff Send private email
Friday, February 24, 2006
I don't think I've explained myself very well. brad is the only one who's even touched on the issue I have.

>As far as the encryption goes, you will almost certainly want to have a "secret" key somewhere

This seems to confirm that for a desktop application, a secret key must be embedded within the compiled application which is used for the encryption/decrytption of the data. The user is then just authenticated against a one-way hash of their password.
Friday, February 24, 2006
"This seems to confirm that for a desktop application, a secret key must be embedded within the compiled application which is used for the encryption/decrytption of the data."

Please don't even think about doing this. The "secret" in "secret key" is there for a reason. If a user who is not expected to know it has direct access to the secret key (regardless of the form of storage), it should not be considered secret anymore. The Internet is full of stories of hardcoded keys that were recovered and cracked. DeCSS, anyone?
Friday, February 24, 2006
The actual storage and/or encryption of the key itself is a real problem.  You really really really really don't want to compile this into your code ... there's all manner of tricks to hiding a unique key "in the system".  If it's a single box install, you can use some of the (if it's windows?) crypto goodies and bury an encrypted copy of key in registry.  Base 64 encode and bury in an obscure file somewhere, etc. etc.  I don't think there are any really truly bullet proof solutions for this, as your code HAS to get it at some point to retrieve the encrypted data (so not oneway only like pwd hash).  If someone is really dedicated and monitors the registry / file system / database / TCP/IP / call stack, etc. they're going to crack it.  The trick is to really analyze your infrastructure and try to find the most innocuous place you can find to squirrel it away ...

How about this ... since it is, in fact, credit card data and it seems as if this is a desktop app, don't store the credit card number at all once the transaction is complete.  By the rules of CISP (yeah, not called that anymore, but I can't remember new name) you shouldn't be storing enough information to reproduce transaction anyway.  Best secret keeping is to have not secrets.
brad Send private email
Friday, February 24, 2006
Encrypting data is easy, the challenging part is to develop a protocol for decrypting it....

Relative to your post, it seems there are two separate themes here: 1) is the encryption of the data itself and 2) is the protocol needed to facilitate this effort.

Do you actually have to write an encryption algorithm for the data in question, or are you just required to use a protocol that contains an existing encryption scheme for which you must deploy?

There are different layers of implementation here depending on the answers to these questions...
Brice Richard Send private email
Monday, February 27, 2006
I went through a similar excercise about a year ago.  We implemented AES client side using one of the AES c libraries out there.

The issue, for us, was that customer did not want sensitive data going over the wire so the data had to be encrypted/decrypted client side before hitting the database.

And as far as keeping credit card numbers on file, its been my understanding this law varies from state to state in the US.
Eric (another ISV guy with his company)
Tuesday, February 28, 2006
I still don't fully know the situation you're working from (a single end-user app that talks directly to your database?  or to a programmable server under your control?)

It sounds like only your company, hosting the server, would need to retrieve the credit card data, so the obvious answer sounds like one-way(asymmetric) public key encryption.  Examples include DSS and RSA. The client encrypts thier CC data with a public key (which by definition can't be compromised) and then sends the encrypted result to your database.  When your company needs to access the credit card data again you decrypt it with the private key (the compromise of which would destroy security for all your client apps until you give them a new secret key...maybe the client would download the public key from the server once per session so that they could be globally revoked).
Crypto Keeper Send private email
Friday, March 03, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz