File Recovery from a legacy SQL Server database

Someone wanted files retrieved from a legacy database export. In this case, they were Word documents and PDFs stored as hex strings in an IMAGE column. The hex strings, of course, are representations of the bytes that make up the files. Now, there is a way of recovering files from this, using a native stored procedure, but I didn’t have admin-level access to this particular database. I also couldn’t use the online conversion sites, as the data was too sensitive.

Converting hex string to bytes

The best solution, I thought, was to hash out a basic .NET Windows Forms application, to convert a hex string in the IMAGE column to a byte array, and then to write that array to a file - very basic C# stuff. Since only a handful of files needed to be recovered, and I didn’t know how MFA would work with a Windows Forms application, I didn’t bother adding SQL Reader, Entity Framework and a handful of stored procedures to get the hex strings. It was much quicker to just run the SQL queries in SSMS, and copy the strings into a text box.

There isn’t a standard .NET assembly for converting hext strings to bytes, it seems, so we’ll need a method, which I called HexStringToBytes().

For this to work, the hex strings actually need to be hex strings, with an even number of characters, and with the ‘0x’ prefix and whitespace removed.

if (hex.StartsWith("0x", StringComparison.OrdinalIgnoreCase))
{
    hex = hex.Substring(2);
}
hex = hex.Replace(" ", "").Replace("\n", "").Replace("\r", "");

If the hex string length has an odd number of characters, it can be padded with a ‘0’.

if (hex.Length % 2 != 0)
{
    hex += "0";
}

Since every two characters in the hex string represents a byte, the byte array will be half the length of the input string. We then need to iterate over each pair of characters and convert it to a byte.

int length = hex.Length;
byte[] result = new byte[length / 2];

for (int i = 0; i < length; i += 2)
{
    result[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);
}

When the method exits, the result byte array will be written directly to a file, using the WriteAllBytes() method. Currently the file extension will depend on the option selected in a drop-down menu, but I might geet round to modifying the application to determine the file type from the first 16 bytes.

There is another thing that needs changing: The Windows Forms text box element has a default character limit that’s likely to be shorter than the file string. That can be changed by adding the following line in Form.Designer.cs: 

this.txtHexCode.MaxLength = 0;

SQL Server result string lengths

At first, I though the files were corrupted. I knew the application was converting the hex strings and writing the bytes correctly. Why was the file apparently corrupted, when I could see the PDF data structures in Notepad++? 

It turned out SQL Server Management Studio truncates the column lengths. Casting it as VARBINARY(MAX) didn’t solve the problem. I changed the SSMS results max value length to ‘2097152’ in ‘Tools’ -> ‘Options…’ -> ‘Query Results’ -> ‘Results to Text’.

Eventually I decided to put together a script that gets the hex string in blocks, and it looked something like:

DECLARE @pointer VARBINARY(MAX);
DECLARE @size BIGINT;

SELECT @hexstring = [file_column]
FROM [dbo].[file_table]
WHERE [file_code] = '61414';

SELECT @size = DATALENGTH(@hexstring)

SELECT SUBSTRING(@hexstring, 1, 2000000) AS Block1;
SELECT SUBSTRING(@hexstring, 2000001, 2000000) AS Block2;
SELECT SUBSTRING(@hexstring, 4000001, 2000000) AS Block3;
SELECT SUBSTRING(@hexstring, 6000001, 2000000) AS Block4;
-- and so on...

Each segment can be pasted into the .NET application’s text box, and the application will process them as a single long hex string, after removing all the whitespace and ‘0x’ characters.