locked
Problem with data conversion after decryption.... RRS feed

  • Question

  • Hi Friends,
                   This is first time I'm asking a question here. I'm trying to encrypt data using symmetric key and certificate in SQL server 2005 (Developer edition). The encryption and decryption works fine. The problem is with float data conversion after decryption. The error is,
    'Arithmetic overflow error converting varbinary to data type numeric.'

    I'm pasting here the demo code,

    print '----- FLOAT DEMO -----'
    declare @f float
    declare @a varbinary(100)
    declare @b varbinary(100)
    set @f=123.456

    OPEN SYMMETRIC KEY Encrypt_Sold_SymmKey1
    DECRYPTION BY CERTIFICATE Encrypt_Sold_Cert1 WITH PASSWORD = 'zingalala'

    set @a=ENCRYPTBYKEY(KEY_GUID('Encrypt_Sold_SymmKey1'), cast(@f as varbinary)) 
    set @b=(DECRYPTBYKEY(@a))
    CLOSE SYMMETRIC KEY Encrypt_Sold_SymmKey1

    print 'Value of @f' 
    print @f
    print 'varbinary value of @f' 
    print cast(@f as varbinary)
    print 'encrypted value of @f' 
    print @a
    print 'decrypted varbinary value of @f' 
    print @b
    print 'original float value' 
    print cast(cast(@b as decimal(10, 5)) as float) 

    The result is,

    Value of @f
    123.456
    varbinary value of @f
    0x405EDD2F1A9FBE77
    encrypted value of @f
    0x0018A542CC685F4BAD92E3C702A5D03301000000F94CFE673A691B19ACA5C9163FD66A8E02DFACF57DE0E4793207917F2E3EB48E
    decrypted varbinary value of @f
    0x405EDD2F1A9FBE77
    original float value
    Msg 8115, Level 16, State 6, Line 19
    Arithmetic overflow error converting varbinary to data type numeric.


    I tried simple varbinary to float conversion using the error causing line,

    declare @b varbinary(100) 
    set @b=cast(12663.456 as varbinary)
    print 'original float value' print cast(cast(@b as decimal(10, 5)) as float) 

    ...and it works fine.

     What is the wrong when  @b is a varbinary value returned by decryption function???

    Any help will greatly alleviate.
    Monday, May 4, 2009 12:44 PM

All replies

  •   In the last step of your sample code, the varbinary data recovered from the decryption function is a float representation, unfortunately there is no way to recover a float from the varbinary directly.

      One workaround that should help you is to cast as decimal before encrypting, then, after decryption,  cast back to decimal and finally to float.

    print '----- FLOAT DEMO -----'

    declare @f float

    declare @a varbinary(100)

    declare @b varbinary(100)

    declare @pt varbinary(100)

     

    set @f=123.456

     

    OPEN SYMMETRIC KEY Encrypt_Sold_SymmKey1

    DECRYPTION BY CERTIFICATE Encrypt_Sold_Cert1 WITH PASSWORD = 'zingalala'

     

    -- Since float type has limited varbinary support, we explicitly cast to decimal

    set @pt = CAST(@f as decimal(10,5))

     

    --a We encrypt @pt

    set @a=ENCRYPTBYKEY(KEY_GUID('Encrypt_Sold_SymmKey1'), cast(@pt as varbinary))

    set @b=(DECRYPTBYKEY(@a))

    CLOSE SYMMETRIC KEY Encrypt_Sold_SymmKey1

     

    print 'Value of @f'

    print @f

    print 'varbinary value of @f'

    print cast(@f as varbinary)

    print 'varbinary value of @pt'

    print @pt

    print 'encrypted value of @pt'

    print @a

    print 'decrypted varbinary value of @pt'

    print @b

    print 'original float value'

    print cast(cast(@b as decimal(10, 5)) as float)

    go

     

       The reason the second part of the demo works is because there is an implicit casting from float to the best decimal representation the system can find.

    declare @b varbinary(100)

    declare @f float

    declare @d decimal(10,5)

    declare @d2 decimal(8,3)

     

    set @b = cast(12663.456 as varbinary)

    set @f = 12663.456

    set @d = 12663.456

    set @d2 = 12663.456

     

    print 'implicit decimal cast (8,3) in this example'

    print @b

    print 'explicit float cast'

    print cast(@f as varbinary)

    print 'explicit decimal(10,5) cast'

    print cast(@d as varbinary)

    print 'explicit decimal(8,3) cast'

    print cast(@d2 as varbinary)

    go

      I hope this helps.

      -Raul garcia
       SDE/T
       SQL Server Engine

    Saturday, May 9, 2009 10:03 PM