An issue with an encrription function

Question

An issue with an encrription function

itmasterw 60042

SSCrazy

Points: 2858
More actions
May 8, 2020 at 8:23 pm

#3750064
Hi,
I am trying to get a Microsoft function to work with a variable.
Below as you can see I am using the encryptByPassPhrase function. If I put the number in quotes as shown in the first code shown it works great. However, I would like to use a variable, as in the 2nd one, and decipher it (with the DECRYPTBYPASSPHRASE function)
it only returns the first charter. I know the whole number is in there because I checked it with the len function and all the numbers are there.
Any ideas why this works with '2225504600042' this and not with a variable @CardNumber; and what I may be able to do to get the variable to work.
Thank you
```
DECLARE @CardNumber nvarchar(20) = '2225504600042'

 UPDATE [dbo].[CreditCard]
      SET [CardNumber] = encryptByPassPhrase('A test phrase!', '2225504600042' )
  WHERE CreditCardID = @CreditCardID

------This does nto work----

   UPDATE [dbo].[CreditCard]
      SET [CardNumber] = encryptByPassPhrase('A test phrase!',@CardNumber )
  WHERE CreditCardID = @CreditCardID
```

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

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

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 1

So I did a quick test of this by running the following:

DECLARE @test NVARCHAR(20) = '2225504600042'
SELECT CAST(DECRYPTBYPASSPHRASE('a test phrase!',ENCRYPTBYPASSPHRASE('a test phrase!',@test)) AS NVARCHAR(20))
SELECT CAST(DECRYPTBYPASSPHRASE('a test phrase!',ENCRYPTBYPASSPHRASE('a test phrase!','2225504600042')) AS VARCHAR(20))
SELECT CAST(DECRYPTBYPASSPHRASE('a test phrase!',ENCRYPTBYPASSPHRASE('a test phrase!',N'2225504600042')) AS NVARCHAR(20))

All 3 of them work as expected. If I had to guess why it isn't working with @CardNumber, With your first UPDATE you are passing in a CHAR value whereas in the second one you are passing in an NVARCHAR value. These are not interchangebale, so when you decrypt it you will need to cast it to NVARCHAR or NCHAR for the second case and to VARCHAR or CHAR for the first one. My GUESS is that if you change the data type of @CardNumber to VARCHAR(20), you will have better luck.

That being said, I personally wouldn't use a passphrase to encrypt a credit card number; I'd use a certificate. Brute forcing a passphrase is a LOT easier to do than brute force a certificate... Just my 2 cents.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

Hi thanks that worked.

But now that you mentioned the certificates, where would I be able to learn about that.

What would I look for.

Thanks

Again I apricate the help

Thank you