3

I'm starting an encryption project, using EncryptByPassPhrase(), mostly on VARCHAR values. Naturally, the encrypted values are longer than the originals. Is there a formula I can use to calculate how long I need to make the new VARBINARY fields, so as to hold any possible value of the original VARCHAR field?

E.g., the first field I examined has values of up to 37 characters in length, and the encrypted values are up to 100 bytes; another has values up to 50 chars and encrypted values are up to 124 bytes. However, values as short as two or three characters can encrypt to 76 bytes. If I size the new fields as 75 + X bytes, will I have room to store the encrypted version of any possible text value of length X or less?

  • EncryptByPassPhrase returns a varbinary(8000) – Max Vernon Nov 12 '15 at 22:21
  • Jon, can you provide an example of a 2 or 3 character cleartext value that encrypts to 76 bytes? I cannot seem to reproduce that. – Solomon Rutzky Nov 12 '15 at 22:27
  • @srutzky: You are quite correct: the value had trailing spaces. I'd forgotten that SQL Server can store trailing spaces but doesn't report them with LEN. – Jon of All Trades Nov 12 '15 at 22:54
5

As far as my testing goes (using SQL Server Express 2014, SP1 and SQL Server Developer 2012 SP2, both 64-it), the formula when not using an authenticator for the return value (VARBINARY) length of:

ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', {anything})

is:

28 + (8 * (DATALENGTH(@ClearText) / 8))

Try the following:

DECLARE @ClearText VARCHAR(8000);
SET @ClearText = 'testdfdf gkdj flkgjdlfkgjdlf gjlf gklf TE%$%^&^%HFGHFhg fkgh jfgkhæ';

SELECT LEN(ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', @ClearText)) AS [ActualLength],
       28 + (8 * (DATALENGTH(@ClearText) / 8)) AS [EstimatedLength];

Returns:

ActualLength    EstimatedLength
92              92

And, if you change the datatype of @ClearText to instead be NVARCHAR(4000), and run it again, it returns:

ActualLength    EstimatedLength
156             156

Note: the formula looks like it can be reduced by cancelling out the 8s. However, that will cause it to not work correctly since it is banded-ranges of datalengths that fit into a "bucket":

input bytes    result length
-----------    -------------
 1 -  7        28
 8 - 15        36
16 - 23        44
24 - 31        52

So the (DATALENGTH(@ClearText) / 8) portion of the formula is enforcing that decimal values are ignored rather than the value be rounded. And that is accomplished by default behavior of dividing two INT values ;-).


UPDATE:

The testing done above does not use an option available to ENCRYPTBYPASSPHRASE: specifying an "authenticator". Doing so adds 16 bytes to the minimum length, and then while the increments are still in 8-byte steps, and while the banded-ranges are still 8 bytes each, the initial range is only 4 bytes so the ranges are offset by 4 as compared to the range boundaries when not using an authenticator. To help illustrate, the following chart shows the ranges and their corresponding result lengths:

input bytes    result length
-----------    -------------
 0 -  3        44
 4 - 11        52
12 - 19        60
20 - 27        68

The formula when using an authenticator for the return value (VARBINARY) length of:

ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', {anything}, 1, {anything_1-128_bytes})

is:

44 + (8 * ((DATALENGTH(@ClearText) + 4) / 8))

NOTES:

  • The length of PassPhrase has no effect on the result length
  • An empty string for PassPhrase has no effect on the result length
  • In order for an Authenticator value to have any effect, the value of Add_Authenticator (the 3rd input parameter) must be set to 1
  • The length of Authenticator has no effect on the result length as long as it is at least 1.
  • An empty string for Authenticator will have an effect on the result length, and that effect is the same as setting Add_Authenticator to 0.
  • If either Add_Authenticator is set to 0, or Authenticator is an empty string or NULL, then the formula is the same as if there was no "authenticator".

The following is an expanded and improved test that shows both with and without an "authenticator", and makes it easier to change the @PassPhrase value:

DECLARE @ClearText NVARCHAR(4000),
        @Authenticator sysname,
        @PassPhrase VARCHAR(100);

SET @PassPhrase = '_My_PassPhr@zE_yo_';
SET @ClearText = 'testdfdf gkdj flkgjdlfkgjdlf gjlf gklf TE%$%^&^%HFGHFhg fkgh jfgkhæ';
SET @Authenticator = REPLICATE(N' ', 128);

SELECT LEN(ENCRYPTBYPASSPHRASE(@PassPhrase, @ClearText))
                                         AS [ActualLengthSansAuthenticator],
       28 + (8 * (DATALENGTH(@ClearText) / 8)) AS [EstimatedLengthSansAuthenticator];

SELECT LEN(ENCRYPTBYPASSPHRASE(@PassPhrase, @ClearText, 1, @Authenticator))
                                         AS [ActualLengthWithAuthenticator],
       44 + (8 * ((DATALENGTH(@ClearText) + 4) / 8)) AS [EstimatedLengthWithAuthenticator];
  • 2
    That's exactly what I needed! I know it's usually bad form to accept an answer so soon, but this is a perfect answer. – Jon of All Trades Nov 12 '15 at 22:55
  • @JonofAllTrades I updated my answer (new UPDATE section). It now includes a 2nd formula which is used when an "authenticator" is specified. – Solomon Rutzky Nov 13 '15 at 2:54
  • 1
    good complete answer. – Max Vernon Nov 13 '15 at 4:16
1

This is not scientific; the only way to get the scientific answer would be to look at the code the SQL Server developers used to implement the 3DES 128 algorithm.

Having said that, built a quick test rig that attempts to determine the practical length of the encrypted text.

;WITH Num1
AS 
(
SELECT TOP(4000) rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , pwd = REPLICATE('x', ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id))
FROM sys.objects o, sys.objects o1
)
, Num2
AS 
(
SELECT TOP(4000) rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , dat = REPLICATE('x', ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id))
FROM sys.objects o, sys.objects o1
)
SELECT Encrypted = ENCRYPTBYPASSPHRASE(pwd,dat,1,CONVERT(VARBINARY(20), ROW_NUMBER() OVER (ORDER BY num1.rn, num2.rn)))
    , EncryptedLength = LEN(ENCRYPTBYPASSPHRASE(pwd,dat,1,CONVERT(VARBINARY(20), ROW_NUMBER() OVER (ORDER BY num1.rn, num2.rn))))
    , RowNum = ROW_NUMBER() OVER (ORDER BY num1.rn, num2.rn)
    , DatLen = LEN(dat)
    , Diff = LEN(ENCRYPTBYPASSPHRASE(pwd,dat,1,CONVERT(VARBINARY(20), ROW_NUMBER() OVER (ORDER BY num1.rn, num2.rn)))) - LEN(dat)
FROM Num1, Num2;

This generates passphrases and data of varying length between 1 and 4000 bytes, then compares the output of the ENCRYPTBYPASSPHRASE function with length of the inputted data. I've found, on my computer, there is between 41 and 48 "extra" bytes in the output.

Having said that, I would suggest using VARBINARY(8000) as the field size since that is the defined maximum output for the function.

0

I came across this to determine the length of the varbinary data after enxrypting with enryptbypassphrase

CEILING((CAST(DATALENGTH(@cleartext) as float) + 1) / 8) * 8 + 20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.