How to decrypt zipped data in MSSQL table

by

The question:

We’re trying to import (reverse engineer) some data from someone else’s MS SQL database, without any vendor support.

In the past the data has been stored in either plain text or RTF so easy to extract. But this database has some ‘encrypted’ looking content. Where data is in this format there is another column ‘zipped’ which = 1. I have been told they were compressing data to keep under the 10GB limit for SQL Express.

It looks a bit like this:

-Ëj„@D÷
þC}€7!2‹<Pɾí¾êÍôCn·ÿ~¦6UÅ©–F

Any thoughts on how to decode it? There are other rows in the same table that are plain text or RTF but many are this format.

Here is a sample export of the database:

https://1drv.ms/u/s!Au6oldAhXo2M5Xt_bE9Q5iA0WdfF?e=IsuYdQ

which includes a screenshot of what it should look like.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

The data is not encrypted; it’s simply compressed using Deflate, which isn’t directly available in TSQL. TSQL COMPRESS uses Gzip, which is similar, but not strictly compatible. So in C# something like:

using System.Data.SqlClient;
using System.IO;
using System.IO.Compression;

using var con = new SqlConnection("server=.;database=spmcopy;integrated security=true;multipleactiveresultsets=true");
con.Open();

var cmd = new SqlCommand("select counter, cast(cast([text] as varchar(max)) as varbinary(max)) ZippedText from notescopy where Zipped=1 ", con);
var cmdWrite = new SqlCommand("update notescopy set [text] = cast(cast(@t as varchar(max)) as text), Zipped = 0 where counter = @c",con);
var ptext = cmdWrite.Parameters.Add(new SqlParameter("@t", System.Data.SqlDbType.VarBinary, -1));
var pCounter = cmdWrite.Parameters.Add(new SqlParameter("@c", System.Data.SqlDbType.Int));

using var reader = cmd.ExecuteReader();

while (reader.Read())
{
    pCounter.Value = reader.GetInt32(0);
    var compressedStream = reader.GetStream(1);

    var dest = new MemoryStream();
    var decompressedStream = new DeflateStream(compressedStream, CompressionMode.Decompress);
    decompressedStream.CopyTo(dest);
    dest.Position = 0;
    ptext.Value = dest.ToArray();

    cmdWrite.ExecuteNonQuery();
}

Also, it looks like you can prepend a standard 10-byte header to the Deflate stream to make it GZip compatible, so

select cast(decompress(cast(0x1F8B0800000000000400 as varbinary(max)) + cast(cast([text] as varchar(max)) as varbinary(max))) as varchar(max)) d
from notescopy

As it looks like DECOMPRESS ignores the CRC32 and ISIZE trailer fields if they are absent, which is compliant but not mandatory decompressor behavior.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment