May 4, 2009 at 6:51 am
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-2 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-2=(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-2
print 'original float value'
print cast(cast(@b as decimal(10, 5)) as float) /* error at this line */
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-2 varbinary(100)
set @b-2=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-2 is a varbinary value returned by decryption function???:crying:
Any help will greatly alleviate.
May 4, 2009 at 7:41 am
Doesn't work for me as well. I'll have to play with it more later, but it seems as though there might be an issue with the case.
If I cast it to char first, then encrypt. It works.
print '----- FLOAT DEMO -----'
declare @f float
declare @a varbinary(100)
declare @b-2 varbinary(100)
declare @C varchar(10)
set @f=123.456
OPEN SYMMETRIC KEY Encrypt_Sold_SymmKey1
DECRYPTION BY CERTIFICATE Cert1_Sales;
select @C = CAST( @f as varchar)
set @a=ENCRYPTBYKEY(KEY_GUID('Encrypt_Sold_SymmKey1'), cast(@c as varbinary))
set @b-2=(DECRYPTBYKEY(@a))
CLOSE SYMMETRIC KEY Encrypt_Sold_SymmKey1
print 'Value of @f'
print @f
print 'Value of @C'
print @C
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-2
print 'original char value'
print cast( @b-2 as varchar(10))
print cast(cast(@b as varchar(10)) as float) /* error at this line */
May 4, 2009 at 11:34 am
Vicky (5/4/2009)
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-2 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-2=(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-2
print 'original float value'
print cast(cast(@b as decimal(10, 5)) as float) /* error at this line */
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-2 varbinary(100)
set @b-2=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-2 is a varbinary value returned by decryption function???:crying:
Any help will greatly alleviate.
The fundamental problem is that you can't convert a varbinary back to a float, as I see you've discovered. Your best bet would be to declare @f as a decimal data type or explicitly convert it to decimal before the conversion.
Now for the second part -- this query in particular:
declare @b-2 varbinary(100)
set @b-2=cast(12663.456 as varbinary)
print 'original float value' print cast(cast(@b as decimal(10, 5)) as float)
You're assuming that the value 12663.456 is a float value, when in fact it's implicitly typed as a decimal(8, 3). You can test this by checking the binary values as shown below:
declare @b-2 varbinary(100)
set @b-2 = cast(12663.456 as varbinary(100))
select @b-2
-- Result = 0x08030001A03AC100
declare @C numeric(8, 3)
set @C = 12663.456
select cast(@c as varbinary(100))
-- Result = 0x08030001A03AC100
Your best bet in this case is to use decimal and not float, since you can't convert a varbinary back to a float.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply