Translate Sql Query to Clr

Question

Translate Sql Query to Clr

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 1

meryemkurs072 - Monday, January 7, 2019 12:25 PM
Thank you so much.
I'm still not understanding how you read the ASSEMBLY code

You are welcome.

And I did the following to get the .NET code from your Assembly definition:

I executed the CREATE ASSEMBLY statement that you posted (before it was redacted)
I then executed the following, which uses a function from the SQL# library (that I wrote), File_WriteFileBinary (which is only in the Full version, not the Free version), to save the assembly definition to disk. It would be easy enough for anyone already doing SQLCLR to create a simple, one line function to do a similar thing. Just look into File.WriteAllBytes.
SELECT SQL#.File_WriteFileBinary(N'C:\TEMP\Database8.DLL', [content], N'Create', NULL)
FROM sys.assembly_files
WHERE [name] = N'Database8'
AND [file_id] = 1;
Then, I just opened that DLL in ILSpy.

It was that easy. In less than 2 minutes I was viewing the source code.

Take care, Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Solomon Rutzky - Monday, January 7, 2019 8:08 PM
meryemkurs072 - Monday, January 7, 2019 12:25 PM
Thank you so much.
I'm still not understanding how you read the ASSEMBLY code
You are welcome.
And I did the following to get the .NET code from your Assembly definition:
I executed the CREATE ASSEMBLY statement that you posted (before it was redacted)
I then executed the following, which uses a function from the SQL# library (that I wrote), File_WriteFileBinary (which is only in the Full version, not the Free version), to save the assembly definition to disk. It would be easy enough for anyone already doing SQLCLR to create a simple, one line function to do a similar thing. Just look into File.WriteAllBytes.
SELECT SQL#.File_WriteFileBinary(N'C:\TEMP\Database8.DLL', [content], N'Create', NULL)
FROM sys.assembly_files
WHERE [name] = N'Database8'
AND [file_id] = 1;
Then, I just opened that DLL in ILSpy.
It was that easy. In less than 2 minutes I was viewing the source code.
Take care, Solomon...

Nicely done and great explanation, Solomon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 3

Jeff Moden - Monday, January 7, 2019 9:57 PM
Solomon Rutzky - Monday, January 7, 2019 8:08 PM
It was that easy. In less than 2 minutes I was viewing the source code.
Nicely done and great explanation, Solomon.

Thanks :).

Also, it gets even better! It occurred to me last night: if the goal here was to hide the T-SQL queries from end-users, then we don't even need the 3 easy steps I noted in my previous reply. There is no need to export the assembly to be disassembled. In fact, there is no need to even import the assembly (I assume the assembly is not signed, so that might require setting TRUSTWORTHY ON (yuck) just to do the CREATE statement if using SQL Server 2017 or newer).

The T-SQL query is mostly a string literal in the .NET code (which makes sense). There are a few variables (from input parameters) that get concatenated in, but those are for the DB name, etc. Most of the query is there. Well, one of the reasons why I am grateful for having taken a mainframe assembler class in college, even though I never did any assembler programming after that class, is that you get to see how computers in general do basic processing of code. One thing we learned was that string literals are (usually) stored as-is at the end of programs. With that in mind, I simply converted the assembly hex bytes (the 0x4D5A..... string) into VARCHAR(MAX), while replacing all 0x00 bytes (null == string terminator) with a space. While this results in an imperfect representation since certain UTF-16 characters will not come out correctly, it certainly gets us close enough.

Just execute the following:

DECLARE @Assembly VARBINARY(MAX);

SET @Assembly = 0x4D5A.....{assembly hex bytes used in CREATE ASSEMBLY statement};

SELECT REPLACE(CONVERT(VARCHAR(MAX), @Assembly), CHAR(0) COLLATE Latin1_General_100_BIN2, ' ') FOR XML PATH('');

Or, if the assembly has already been loaded, just execute this:

SELECT REPLACE(CONVERT(VARCHAR(MAX), [content]), CHAR(0) COLLATE Latin1_General_100_BIN2, ' ') FROM sys.assembly_files WHERE [name] = N'Database8' AND [file_id] = 1 FOR XML PATH('');

In either case, click on the XML in the result set and then scroll to the bottom. There you will see the query and some other stuff. You will even see the full path on your hard-drive to the .pdb file for debugging, which include the Visual Studio version in the path name (and the fact that this was compiled in the Debug configuration instead of the Release configuration -- most likely-- it is possible to include the .pdb file in the Release configuration, but I don't think anyone actually does that).

Now, it might be possible to obscure the query by making it NOT a string literal, but instead constructing it from a byte array or something. But that is wasted effort since:

Nobody will ever be able to read or modify / fix that code.
The queries are still visible to SQL Server Profiler and Extended Events (plus several DMVs, DBCC INPUTBUFFER, and maybe others)
someone can still go through the 3 easy steps to view the code in ILSpy.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

meryemkurs072 SSC Eights! Points: 945 More actions · Answer 4

Sorry.
I made a late return :crying:
Thank you really for your attention. 😉

Is there another way to secure the query ?
I understand Clr is opening very comfortably.
Is there another way to visual studio?

Thank you