DECRYPTBYPASSPHRASE/ENCRYPTBYPASSPHRASE issue

Hello i am having trouble getting the decrypted field to work on an input.


So right now i have a field that is encrypted in database with ENCRYPTBYPASSPHRASE, this field works correctely when i try to show it in an expression but if i try to display it in an input, de value that is displayed is the encrypted one.


Both variables use the same decrypt method



SyntaxEditor Code Snippet

CONVERT(NVARCHAR(128), DECRYPTBYPASSPHRASE(@Passphrase,{Patient}.[FullName]))


and when i save this field i use:


SyntaxEditor Code Snippet

,{Patient}.[FullName] = ENCRYPTBYPASSPHRASE(@Passphrase,@FullName)


Does anyone had a similar issue?




Note: I am working in a aplication already in production, so change the encryption now is not viable.


Thank you

Hi Carlos,

What do you bind to the Form that the Input Widget is inside of? What Attribute are you using? What does the query look like that fetches the data?

Solution

Hello


I found out the issue after some extensive testing. The issue was that some of the data was saved as nvarchar and the other was as varchar. When trying to decrypt and conver nvarchar to varchar the result is that strange characters when you try to decrypt a previous encrypted varchar to nvarchar then the result is a ? between each character.


so i need to before decripting find if the value is saved in nvarchar or varchar before decrypting.

Solution

Hi Carlos,

Thanks for reporting back. NVARCHAR stores Unicode data, while VARCHAR uses a code page. Converting one to the other without taking precautions is indeed a recepi for disaster :).