Encyrption and change field as same time

  • barry.nielson

    SSC Eights!

    Points: 912

    Hello all.

    I am doing this to modify a field and encrypt data in that field:

    --Update EncryptedName  field with a new name
    UPDATE dbo.Tbl_Table SET EncryptedName = 'My Name'
    Where dbo.Tbl_Table .ID = 1234
    Go
    --Encrypt the field.
    OPEN SYMMETRIC KEY MySymmetricKey
    DECRYPTION BY CERTIFICATE MySymmetricCertificate;
    UPDATE dbo.Tbl_Table SET EncryptedName = EncryptByKey(Key_GUID('MySymmetricKey'), EncryptedName )
    Where dbo.Tbl_Table .ID = 1234
    Go

    It works, but I'm not very sure this is the right way to go.  The problem is I can't set the EncryptedName field at the same time to the change I want.  It doesn't seem to work like that.

    This doesn't work

    e.g UPDATE dbo.Tbl_Table SET EncryptedName = EncryptByKey(Key_GUID('MySymmetricKey'), 'My Name' )

    So what is the elegant way to do this?

  • rVadim

    Hall of Fame

    Points: 3922

    Here is what we do in the beginning of update procedure:

      DECLARE @EncryptedName VARBINARY(68);

    IF NOT EXISTS(SELECT * FROM sys.openkeys WHERE key_name = 'MySymmetricKey' AND database_name = db_name())
    OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MySymmetricCertificate

    SET @EncryptedName = EncryptByKey(Key_GUID('MySymmetricKey'), @Name)

    Then use @EncryptedName in the INSERT/UPDATE statements.

    --Vadim R.

  • barry.nielson

    SSC Eights!

    Points: 912

    Thank you for your response.

    I'm sorry to say this method seems more complicated that my original script.  I tried testing it and ended up with the error: Must declare the scalar variable "@Name".

    I'm something of an SQL newbie, so my apologies if I missed the point.

    Cheers

  • rVadim

    Hall of Fame

    Points: 3922

    @Name would be a parameter in my update procedure. Unencrypted string that needs to be encrypted. You can  replace it with 'My name' string from your example.

    When you said "This doesn't work" what do you mean? What happening? Are you getting any errors?

    This could be due to difference in data types. EncryptedName column in your table appear to be a VARCHAR. But EncryptByKey function returns VARBINARY.

    --Vadim R.

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

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