0

I have a table

TestTableDestination(SerialNumber (varchar) , Name(varchar))

When I want to insert in this table , I'm inserting the encrypted data. What I want to do is to write a trigger with which I can DECRYPT previously inserted data and turn it into it's normal form . What I've written so far Is this

ALTER trigger [dbo].[DycryptColumn]
ON  [dbo].[TestTableDestination]
after insert

AS

Declare  @SerialNumber1 varbinary(Max),
         @serialnumber2 varchar(50)


Select   @SerialNumber1 = SerialNumber from inserted
Select   @serialnumber2 = convert(varchar,DECRYPTBYPASSPHRASE('KEY',@SerialNumber1))


Update [dbo].[TestTableDestination]
Set [SerialNumber] = @serialnumber2

What I need now is to hold the value of data being inserted , Decrypt it and transform it into Varchar and then insert it into the destination table how should I do that?

the trigger should be run during this statement

Insert into [dbo].[TestTableDestination] (SerialNumber,Name)

select EncryptByPassPhrase('KEY', SerialNumber) , name from TestTableSource

datatype of the column of the destination table is varchar and the output of EncryptByPassPhrase is varbinary . what should I do? How could I do this ?How could i hold the value of data while being inserted and then transform into varchar and then insert it.

closed as unclear what you're asking by Scott Hodgin, Md Haidar Ali Khan, MDCCL, Michael Green, Vérace Oct 17 '18 at 11:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Can you share the statement(s) that were used to encrypt the data? – Scott Hodgin Oct 16 '18 at 9:17
  • 1
    Are you sure you are not breaking any laws (PCI, HIPAA, etc) by decrypting the data? – Michael Kutz Oct 16 '18 at 9:46
  • 1
    It would be helpful if you would update your question and include the table definition for [dbo].[DycryptColumn]. Also, your trigger is not set up to properly handle multiple inserted rows – Scott Hodgin Oct 16 '18 at 10:42
  • 1
    The return data type of ENCRYPTBYPASSPHRASE is varbinary, so you would need to store the encrypted value in a separate column from the original SerialNumber. – Scott Hodgin Oct 16 '18 at 10:46
  • 2
    @Shideh - please update your question with code changes instead of adding comments. That way, everything related to your question is in one place. Also, please format your code examples in your questions – Scott Hodgin Oct 16 '18 at 10:55
1

Your question is very confusing to me as to what you are actually trying to achieve. Also, your trigger will not properly handle multi-row inserts.

Here is a working example of encrypting/decrypting data for you to use to compare against what you are doing. Let's create a table to hold the original SerialNumber and the encrypted SerialNumber

--demo setup
DROP TABLE IF EXISTS [dbo].[TestTableDestination];
CREATE TABLE [dbo].[TestTableDestination] (SerialNumber VARCHAR(50),SerialNumberEncrypted VARBINARY(128))
GO

--Create instead of trigger to encrypt inserted data
CREATE TRIGGER [dbo].[DycryptColumn] ON [dbo].[TestTableDestination]
INSTEAD OF INSERT
AS
INSERT INTO [dbo].[TestTableDestination]
SELECT SerialNumber
    ,EncryptByPassPhrase('KEY', SerialNumber)
FROM inserted
GO

--insert test data (SerialNumberEncrypted will be populated by the trigger
--note that we are inserting TWO rows, so the trigger needs to handle multi-row inserts
INSERT INTO [dbo].[TestTableDestination] (SerialNumber)
VALUES ('12345'),('54321')

--verify encrypted data
SELECT *
FROM [dbo].[TestTableDestination]

| SerialNumber | SerialNumberEncrypted                                                      |
|--------------|----------------------------------------------------------------------------|
| 12345        | 0x020000007C3CE69D4954ADE90BDD1B5074CE51E97B83FE2255E69F11F1BEA22A07E0D0BF |
| 54321        | 0x0200000073429643D5925B83257C3C3AF961F1B494CE1A62FD802E39F4CA41A6FD65156F |

Now, update the original SerialNumber with spaces, but leave the SerialNumber Encrypted column alone.

--update original serial number to spaces in preparation for decryption
update [dbo].[TestTableDestination] set SerialNumber = ' '

--verify serial number is blank
SELECT *
FROM [dbo].[TestTableDestination]

| SerialNumber | SerialNumberEncrypted                                                      |
|--------------|----------------------------------------------------------------------------|
|              | 0x020000007C3CE69D4954ADE90BDD1B5074CE51E97B83FE2255E69F11F1BEA22A07E0D0BF |
|              | 0x0200000073429643D5925B83257C3C3AF961F1B494CE1A62FD802E39F4CA41A6FD65156F |

Now, update the SerialNumber column with the decrypted value of SerialNumberDecrypted.

--update SerialNumber with decrypted value
update [dbo].[TestTableDestination] set SerialNumber = CONVERT(VARCHAR(50), DecryptByPassphrase('KEY', SerialNumberEncrypted))

--verify serial number has the decrypted value
SELECT *
FROM [dbo].[TestTableDestination]

| SerialNumber | SerialNumberEncrypted                                                      |
|--------------|----------------------------------------------------------------------------|
| 12345        | 0x020000007C3CE69D4954ADE90BDD1B5074CE51E97B83FE2255E69F11F1BEA22A07E0D0BF |
| 54321        | 0x0200000073429643D5925B83257C3C3AF961F1B494CE1A62FD802E39F4CA41A6FD65156F |
  • I've just added the statement to Encrypt data – Shideh Oct 16 '18 at 9:35
  • Do you know why do we use SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!'; ????? what does it do exactly during encryption?? – Shideh Oct 16 '18 at 9:36
  • 1
    @PassphraseEnteredByUser is just an example of what you are using as 'KEY' – Scott Hodgin Oct 16 '18 at 9:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.