none
Byte array has a different length before and after saving to SQL Server

    Question

  • Hi,

    This is a strange problem which touches several domains, so I thought I would start here, since it feels most likely to be a database problem.  If necessary I can post code, but there is a lot of code, including both C# and Transact SQL stored procedures, so I will first pose the problem.

    I have an app which is saving some encrypted data . The encryption and decryption routine I am using makes use of the System.Security.Cryptography.SymmetricAlgorithm class, which I adapted from the excellent book "Pro ASP.NET 2.0 in C#" by Macdonald & Szpuszta. It works well about 99% of the time.

    The clear text data is encrypted to a byte array of either 32 or 48 bytes, depending on the length of the clear text. The relevant fields in the table are varbinary(60). When I retrieve the data and try to decrypt it, sometimes the retrieved byte array is 31 bytes. This causes the decryption routine to fail.

    In this app, I am importing several thousand records, each of which has two pieces of clear text to be encrypted - a drivers license number and a social security number. The input data is a static XML file. Whe I run the import several times using the same input data, there are always 15 to 20 invalid byte arrays, i.e. 31 byte long arrays. The results are different every run - different records and scattered between the two fields.  Specific records never fail two runs in a row.

    I have added code to my app to verify that the data I am passing to the stored procedure which does the actual save is the correct length, either 32 or 48 bytes. I am moderately confident that the data getting passed to SQL Server is valid. However, when I retrieve it, some of the byte arrays come out as 31 bytes.

    This has me stumped.  Any suggestions as to what can be causing this?

    Thanks.

    Dan


    Dan Hurwitz
    Wednesday, March 31, 2010 2:30 AM

Answers

  • Thanks for the above blog post. It confirmed what I suspected - that a byte was being truncated when the data was saved. I solved this problem by modifying the encrypt routine to add a byte of padding to the end of the byte array. Then I made the opposite modification to the Decrypt routing to truncate that last byte of padding.

    Here is the C# helper method which does this:

    	public static byte[] ByteArrayPadUnpad(byte[] byteArray, string strOperation)
    	{
    		//  this is used to prevent SQL Server from truncating the last (presumably null)
    		//    byte from an array when saving
    		//  if strOperation = "pad", this appends one byte to the end of the input byte array
    		//  if strOperation = "unpad", it removes the last byte from the array
    
    		if (strOperation == "pad")
    		{
    			byte bytePad = Convert.ToByte(9);
    			Array.Resize(ref byteArray, byteArray.Length + 1);
    			byteArray[byteArray.Length - 1] = bytePad;
    		}
    		else if (strOperation == "unpad")
    		{
    			Array.Resize(ref byteArray, byteArray.Length - 1);
    		}
    
    		return byteArray;
    	}

    Then in my cryptography class, in the EncryptData method, this is the last line of the method which returns the byte array:

    return Helper.ByteArrayPadUnpad(Target.ToArray(), "pad");

    In the DecryptData method, I inserted the following line to unpad the byte array:

     data = Helper.ByteArrayPadUnpad(data, "unpad");
    

    Thanks for your help.

    Dan


    Dan Hurwitz
    • Marked as answer by Dan Hurwitz Wednesday, March 31, 2010 2:48 PM
    Wednesday, March 31, 2010 2:48 PM

All replies

  • I forgot to mention in the above post I am using SQL Server 2005.

    On further pondering, it feels as though some of the encrypted data by coincidence has a leading or trailing null byte, which is being truncated by SQL Sever when the data is saved. Does this make sense or is this possible?  I can query the data with something such as the following and see the offending values.

    select EncryptedDriversLicenseNumber from MyTable where datalength(EncryptedDriversLicenseNumber) = 31

    Thanks for any insight you can provide.

    Dan


    Dan Hurwitz
    Wednesday, March 31, 2010 2:42 AM
  • Dan,

    Make sure you are up-to-date with service packs.

    What is the column data type? 

    I would not be very surprised if leading / trailing null got dropped somewhere along the line.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, March 31, 2010 4:46 AM
  • Thanks for the above blog post. It confirmed what I suspected - that a byte was being truncated when the data was saved. I solved this problem by modifying the encrypt routine to add a byte of padding to the end of the byte array. Then I made the opposite modification to the Decrypt routing to truncate that last byte of padding.

    Here is the C# helper method which does this:

    	public static byte[] ByteArrayPadUnpad(byte[] byteArray, string strOperation)
    	{
    		//  this is used to prevent SQL Server from truncating the last (presumably null)
    		//    byte from an array when saving
    		//  if strOperation = "pad", this appends one byte to the end of the input byte array
    		//  if strOperation = "unpad", it removes the last byte from the array
    
    		if (strOperation == "pad")
    		{
    			byte bytePad = Convert.ToByte(9);
    			Array.Resize(ref byteArray, byteArray.Length + 1);
    			byteArray[byteArray.Length - 1] = bytePad;
    		}
    		else if (strOperation == "unpad")
    		{
    			Array.Resize(ref byteArray, byteArray.Length - 1);
    		}
    
    		return byteArray;
    	}

    Then in my cryptography class, in the EncryptData method, this is the last line of the method which returns the byte array:

    return Helper.ByteArrayPadUnpad(Target.ToArray(), "pad");

    In the DecryptData method, I inserted the following line to unpad the byte array:

     data = Helper.ByteArrayPadUnpad(data, "unpad");
    

    Thanks for your help.

    Dan


    Dan Hurwitz
    • Marked as answer by Dan Hurwitz Wednesday, March 31, 2010 2:48 PM
    Wednesday, March 31, 2010 2:48 PM