How to handle backslash(\) in ENCRYPE/DECRYPT

I m using 开发者_如何学JAVAa update Query. i.e:-

 UPDATE tbl_ecpuser  
    SET ecpuser_fullname = 'Operator', 
    ecpuser_password = encrypt(E'Op1111/1\1/1\1' , 'ENCRYPE_KEY', 'ENCRYPE_ALGORITHM'),  
    where ecpuser_key = '0949600348'

Query is Executing Successfully.

But when I m trying to retrive the value for the Column ecpuser_password, it returns with some extra character (i.e-00)

The Query for the Retrive the Password is:-

SELECT
    decrypt(ecpuser_password,'ENCRYPE_KEY','ENCRYPE_ALGORITHM') AS PASSWORD
    FROM tbl_ecpuser
    WHERE
    ecpuser_key = '0949600348'

This query returens

"Op1111/1\001/1\001" 

but it should return "Op1111/1\1/1\1" and I need this.

So can any body help me about this.

Thanks.

One place where PostgreSQL was not conforming to the SQL standard was the treatment of a backslash in string literals.

Since 8.2 a configuration property standard_conforming_strings is available that configures PostgreSQL to comply with the standard here.

If you set that to "on" '\1' is treated correctly as a string with two characters (one backslash and the character 1).

However if that is turned on, the prefix E enables escape sequences again.

So (if I understand your problem correctly) you should set standard_conforming_strings = on and specify the string literal without the leading E.

Seems like E'\1' is treated as chr(1) and returned accordingly.

You probably want: E'\\1'.