Post reply

Select and encrypted data in the query

  • June 5, 2013 at 10:46 pm

    #146065

    Hi.....

    I have a table with one column fully encrypted and that is the foriegn key also.. that column in another table that is in master it is not encrypted. just for security we did like that.

    But the problem is i have to decrypt that column in SQL query so that the inner join works. Is there any way to do that.

    Eg Master Table

    id name

    1 sam

    2 abc

    Detail table

    id age

    #@# 12

    @56 23

    Here in detail table id column is encrypted.

    select * from detail inner join master master.id = detail.id

    its not working

    Thanks and Regards

    Sankar

  • June 5, 2013 at 11:53 pm

    #1621596

    I would suggest you consider decrypting that column. There's no security if the value is encrypted in one place and decrypted in another and having that partial encryption is going to make joins much slower while giving you no protection.

    But to answer the question, you decrypt the column to join it, so, depending on how it's encrypted, something roughly like

    JOIN detail on DecryptByKey(masterid) = detailid

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply