locked
encryptbypassphrase return null RRS feed

  • Question

  • CREATE FUNCTION [dbo].[FN_ENCRYPT]
       ( @ARG_CM000 CHAR(1), @ARG_NO CHAR(20)) RETURNS VARBINARY(255)
    BEGIN
       DECLARE 
       @TMP_ENCRYPT VARBINARY(255), @TMP_PASSPHRASE CHAR(10)
       SET @TMP_PASSPHRASE = 'My Home'
       SET @TMP_ENCRYPT = ENCRYPTBYPASSPHRASE(@TMP_PASSPHRASE, @ARG_NO)
       RETURN @TMP_ENCRYPT
    END
    GO

    CREATE FUNCTION [dbo].[FN_DENCRYPT]
       ( @ARG_CM000 CHAR(1), @ARG_ENCRYPT_NO VARBINARY(255)) RETURNS CHAR(20)
    AS
    BEGIN
       DECLARE 
       @TMP_DENCRYPT CHAR(20), @TMP_PASSPHRASE CHAR(10)
       SELECT @TMP_PASSPHRASE = 'My Home'
       SET @TMP_DENCRYPT = CONVERT(CHAR(20), DECRYPTBYPASSPHRASE(@TMP_PASSPHRASE, @ARG_ENCRYPT_NO))
       RETURN @TMP_DENCRYPT
    END

    GO


    Make the above function encryption / decryption processing to, FN_ENCRYPT() shown by encrypting the value stored in the database as its value FN_DENCRYPT()
    Null values ​​when decoded back will occur.

    Questions that will be part of the solution not good.
    Tuesday, October 8, 2013 6:21 AM

Answers

  • Hi Laugh,

    According to your description, the parameter of @ARG_CM000 is useless, I recommend you can delete this parameter in your code. I guess that you copy the encrypting value and post it as the parameter value in performing FN_DENCRYPT() then you get the null values. You can refer to the following T-SQL statement.

    CREATE FUNCTION [dbo].[FN_ENCRYPT]
    
        ( @ARG_NO CHAR(20)) RETURNS VARBINARY(255)
    
     BEGIN
    
        DECLARE 
    
        @TMP_ENCRYPT VARBINARY(255), @TMP_PASSPHRASE CHAR(10)
    
        SET @TMP_PASSPHRASE = 'My Home'
    
        SET @TMP_ENCRYPT = ENCRYPTBYPASSPHRASE(@TMP_PASSPHRASE, @ARG_NO)
    
        RETURN @TMP_ENCRYPT
    
     END
    
     GO
    
    
     CREATE FUNCTION [dbo].[FN_DENCRYPT]
    
        ( @ARG_ENCRYPT_NO VARBINARY(255)) RETURNS CHAR(20)
    
     AS
    
     BEGIN
    
        DECLARE 
    
        @TMP_DENCRYPT CHAR(20), @TMP_PASSPHRASE CHAR(10)
    
        SET @TMP_PASSPHRASE = 'My Home'
    
        SET @TMP_DENCRYPT = CONVERT(CHAR(20), DECRYPTBYPASSPHRASE(@TMP_PASSPHRASE, @ARG_ENCRYPT_NO))
    RETURN @TMP_DENCRYPT
    
     END
    
     GO
    
     
    select dbo.FN_DENCRYPT(dbo.FN_ENCRYPT('aaa'))
    
    ----Or you can declare @temp to save the encrypt values.
    
    declare @temp varbinary(255)
    
     select @temp = dbo.FN_ENCRYPT('aaa')
    
     select convert(varchar(max),
    
     dbo.FN_DENCRYPT(CONVERT (varbinary(max),@temp)))
    
    ----result shows
    
    aaa 
    

    Thanks,
    Sofiya Li


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    • Marked as answer by Sofiya Li Friday, October 18, 2013 5:56 AM
    Thursday, October 10, 2013 7:34 AM