1

In theory varchar(max) and varbinary(max) columns should be capable of storing up to 2GB of data but I cannot store a unicode string 5000 characters long.

I've looked through other questions on this topic and they all suggest checking column sizes. I've done this and see that all related columns are declared with max size.

The key difference from similar questions is that, when storing I'm encrypting data using EncryptByKey and I think that it's the bottleneck I'm looking for. From MSDN I know that return type of EncryptByKey has max size of 8000 bytes, and it is not clear what is max size of @cleartext argument, but I suspect it's the same.

The following code gives me error :

OPEN SYMMETRIC KEY SK1 DECRYPTION BY CERTIFICATE Cert1;

DECLARE @tmp5k AS NVARCHAR(max);
SET @tmp5k = N'...5000 characters...';
SELECT EncryptByKey(Key_GUID('SK1'), @tmp5k);
GO

[22001][8152] String or binary data would be truncated.

How to encrypt and store big strings (around 5k unicode characters)?

naXa stands with Ukraine
  • 35,493
  • 19
  • 190
  • 259
  • The problem is the return value of `ENCRYPTBYKEY`; which as you said is 8,000 bytes. An `nvarchar` containing 5,000~ characters would be 10,000~ bytes, hence you have 2,000~ bytes of truncation. – Thom A Apr 23 '18 at 10:11
  • Ok, is there a way to encrypt more than 8000 bytes? – naXa stands with Ukraine Apr 23 '18 at 10:14
  • 1
    I've had a quick google, and seems the answer is no, not out of the box. One method would be to "slice and dice" your data; in your case that would be separating it into 4,000 (or less) character chunks and encrypting those, and then decrypting them and concatenating them back together. – Thom A Apr 23 '18 at 10:16

1 Answers1

0

So I ran into this issue when using C# and trying to encrypt and inserts a long JSON string into SQL. What ended up working was converting the plain-text string to binary and then using the same SQL EncryptByKey function to insert that instead.

If you're doing this is just SQL, I think you can use this function:

CONVERT(VARBINARY(MAX), @tmp5k) AS ToBinary

So using our example:

OPEN SYMMETRIC KEY SK1 DECRYPTION BY CERTIFICATE Cert1;

DECLARE @tmp5k AS NVARCHAR(max);
SET @tmp5k = N'...5000 characters...';
SELECT EncryptByKey(Key_GUID('SK1'), CONVERT(VARBINARY(MAX), @tmp5k));
GO

And here's an example of using SQL to convert the binary back to a string:

CONVERT(VARCHAR(100), CONVERT(VARBINARY(100), @TestString)) AS StringFromBinaryFromString ;
Clint Beilman
  • 36
  • 4