How to get CLR file contents from database (and convert from varbinary to string)

Imagine following situation – you have a procedure that uses CLR function to perform some data manipulation. You suspect the CLR function to be sub-optimal, but don’t have the source and cannot confirm your claim. If you are lucky, you can get its contents by scripting whole assembly – if it’s in a DLL, you might try to reverse-engineer it using .NET Reflector (if you have a DLL file). If it’s a separate file (in C# for example), it is scripted in binary format and you have to unscramble it. This is my method of doing it:

;WITH s AS (SELECT 'here comes binary representation of file' s),
num AS (
SELECT v1.number + v2.number * 100 + v3.number * 10000 + v4.number * 1000000 number from master.dbo.spt_values v1 CROSS JOIN master.dbo.spt_values v2 CROSS JOIN master.dbo.spt_values v3 CROSS JOIN master.dbo.spt_values v4
WHERE v1.number < 100 AND v1.type = 'P' AND
v2.number < 100 AND v2.type = 'P' AND
v3.number < 100 AND v3.type = 'P' AND
v4.number < 100 AND v4.type = 'P' ),
ch AS (
SELECT CHAR(CONVERT(int, CONVERT(VARBINARY, '0x' + SUBSTRING(s, 3, 2), 1))) ch, 0 AS i FROM s
UNION ALL
SELECT CHAR(CONVERT(int, CONVERT(VARBINARY, '0x' + SUBSTRING(s, 3 + 2*(i+1), 2), 1))), i + 1 from s INNER JOIN ch ON 1 = 1 WHERE 2 + 2*(i+1) < LEN(s))
SELECT REPLACE(REPLACE(REPLACE(ch,'</ch><ch>', ''), '</ch>', ''),'<ch>', '') from ch FOR XML PATH('')
OPTION (MAXRECURSION 0)

You may also try this when facing a task of conversion from varbinary to string. Example – binary string is 0xEFBBBF7573696E672053797374656D3B0D0A7573696E672053797374656D2E446174613B0D0A7573696E672053797374656D2E446174612E53716C436C69656E743B0D0A7573696E672053797374656D2E446174612E53716C54797065733B0D0A7573696E67204D6963726F736F66742E53716C5365727665722E5365727665723B0D (a snippet of actual C# code), so running this query

;WITH s AS (SELECT '0xEFBBBF7573696E672053797374656D3B0D0A7573696E672053797374656D2E446174613B0D0A7573696E672053797374656D2E446174612E53716C436C69656E743B0D0A7573696E672053797374656D2E446174612E53716C54797065733B0D0A7573696E67204D6963726F736F66742E53716C5365727665722E5365727665723B0D' s),
num AS (
SELECT v1.number + v2.number * 100 + v3.number * 10000 + v4.number * 1000000 number from master.dbo.spt_values v1 CROSS JOIN master.dbo.spt_values v2 CROSS JOIN master.dbo.spt_values v3 CROSS JOIN master.dbo.spt_values v4
WHERE v1.number < 100 AND v1.type = 'P' AND
v2.number < 100 AND v2.type = 'P' AND
v3.number < 100 AND v3.type = 'P' AND
v4.number < 100 AND v4.type = 'P' ),
ch AS (
SELECT CHAR(CONVERT(int, CONVERT(VARBINARY, '0x' + SUBSTRING(s, 3, 2), 1))) ch, 0 AS i FROM s
UNION ALL
SELECT CHAR(CONVERT(int, CONVERT(VARBINARY, '0x' + SUBSTRING(s, 3 + 2*(i+1), 2), 1))), i + 1 from s INNER JOIN ch ON 1 = 1 WHERE 2 + 2*(i+1) < LEN(s))
SELECT REPLACE(REPLACE(REPLACE(ch,'</ch><ch>', ''), '</ch>', ''),'<ch>', '') from ch FOR XML PATH('')
OPTION (MAXRECURSION 0)

will give

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

If you have any questions/suggestions, please let me know.

EDIT:
It’s worth to read Books Online. On the CAST and CONVERT reference page, example H, there is exactly the same example of conversion from binary to string and they do it ine one line:

SELECT convert(varchar(max), binary_string, 0)

Thank you and good night. 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s