How to decrypt zipped data in MSSQL table



  • 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‹

    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 data is not encrypted; it's simply compressed using https://en.wikipedia.org/wiki/Deflate , which isn't directly available in TSQL. TSQL https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver15 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 http://www.zlib.org/rfc-gzip.html#file-format but not mandatory decompressor behavior.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2