Encryption Issue

[Guest]

Hi,

I am having some difficulty ordering a query that has been stored in the db in the encrypt(#name#, #key#) format. Is there any ways to use the ORDER BY in the query in order to order the decrypted results alphabetically? I have tried putting the query results in an array that I then ordered but the clients shared hosting account couldnt handle it and would often time out. So my question is...how can you order a encrypted db field by its decrypted result? If there is not a way, is there a good way to order the decrypted query results?

Thanks!!!

[12Robots]

If you can decrypt it at the DB level then you could do it using order by.

How are you encrypting it?  Also, now how are oyu decrypting it?  What do you have whenthe decryption is complete?  Do you still have a query with the decrypted result or is it in some other form?

There are probably a dozen ways this could be handled, some good and some bad. But without knowing anything about your code and your process we can only guess at the best way. We don;t even know what kidn of crypto algorithm or which DBMS you are using.

[Guest]

Hi,

Everything goes into the db via insert with coldfusions encrypt(#form.name#, #Key#) and is stored in the db in this encrypted format. For example, #encrypt(FORM.FirstName, key)#.. on the page that displayes the query for example, <cfoutput query="query">First Name: decrypt(#query.firstName#, key)</cfouput>. The problem is that a large list of names is in the encrypted form in the database. If I try ORDER BY in the query then it orders them by the encrypted version not the decrypted version. Is there a way to decrypt it in the select query statement so that it can order using the decrypt inside the query statement?

BTW, I am using mySql and coldfusion 9.

Thanks!

[12Robots]

Well, I see some problems. I am going to point them out, even though you did not cause I think they will afect you in the long run.

1. I think encrypting things like first name, last name, address, etc in the DB is a terrible idea and you are experiencing one of the reasons why. The other is performance. Constantly having to encrypt and decrypt information like that is going to slow down your application. If there is a need to encrypt informaiton like that (like some draconian company policy) then you should do it at the DB level. That would probably also solve the ORDER BY problem

2. The level of encryption you are using is just slightly worse then having my daughter scribble the plaintext with crayon (her handwriting is terrible). It would be less difficult to break your encryption than it woudl to decipher my daughter's scribbles.

By default, ColdFusion uses a terribly insecure algorithm called CFMX_COMPAT. And when I say it is terible, I mean, seriously, it is terrible. It should not be used for anything, ever. I have been encouraging Adobe to stop making it the default, we'll see if they decide to in the next version.

Your current set up is not good. If you *MUST* encrypt the data in the DB, then use DB level encryption and use the AES algortihm. http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html

But like I said, I think that encrypting that kind of data is silly (with some exceptions). But if you must do it, having it happen at the DB level is the way to go. Then your application doesn't need to worry about it.

[Guest]

Thank you for your thoughtful response. Well, this data must absolutly be encrypted in the db...disappointing about this non the less.

[12Robots]

Well, then, like I said, try to use the built-in DB encryption. It will solve both problems.

[insuractive]

Agreed - Database-level encryption is the way to go here.  If that is not available for some reason, your only other option is to:

1) Query the unsorted data

2) Loop over the query to decrypt the data

3) Write the data to a new columns in the original query (decr_firstname, decr_lastname, etc) or a new query object

4) Use CF Query-of-a-Query to sort the results by the new CF calculated column (remember query of a query is case sensitive!)

On second thought, it might be better to just build a custom tag that accepts a query as an argument and creates a new, decrypted query as the result.  Of course, you will be taking a MAJOR performance hit, which is why its always better to do this sort of thing at the database level (most databases are designed specifically to do this).