Skip to main content
A
Anonymous
September 18, 2011
Question

Encryption Issue

  • September 18, 2011
  • 2 replies
  • 870 views

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!!!

    This topic has been closed for replies.

    2 replies

    I
    insuractive
    Inspiring
    September 23, 2011

    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).

    12Robots
    12Robots
    Participating Frequently
    September 18, 2011

    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.

    A
    Anonymous
    September 18, 2011

    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
    12Robots
    Participating Frequently
    September 18, 2011

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices