Click here to Skip to main content
16,019,043 members
Articles / Programming Languages / ASM

Intercepting .NET SQL queries at runtime

Rate me:
Please Sign up or sign in to vote.
4.83/5 (9 votes)
16 Aug 2010CPOL3 min read 36K   12   10
Intercepting .NET SQL queries at runtime - SQL Management Studio case.

Image 1

Introduction

SQL Server Management Studio has been designed for very fast interaction for the Administrator and/or users with SQL Server objects. We can see the objects by just clicking on the appropriate item, for example, to get the views or the users.

The purpose of this article is to show how, with a quick knowledge of assembler language and pointers, we have the possibility to extract useful information without having the source code for any app. You can apply this article to show queries in runtime for any .NET system. Reverse Engineering for fun. It can also be applied for Worker Processes on IIS for live debugging without Visual Studio.

Let's start. You will need a basic knowledge of .NET Debugging on Runtime Debugging tools like Windbg. For heuristic knowledge of .NET environments, a natural way to execute commands is the SQLCommand object, but we need to confirm this by checking if the Management Studio process has instantiated this class. Let's start.

Steps

  1. Open SQL Server Management Studio.
  2. Login to SQL Server and execute some operations like view the objects or databases.
  3. Open the Windows debugger tool and attach the SQL Server Management Studio process:
  4. Image 2

  5. Once attached, we can find a command line inside the Windows debugger.
  6. There is a useful helper for debugging, and we can load this by using:
  7. .loadby sos mscorwks

    SOS WinDbg extensions allow to explore .NET objects at low level, based on the correct mscorwks.

  8. Execute:
  9. !dumpheap -type SqlCommand -stat

    Dump .NET Heap for SqlCommand type (-type), and obtain its statistics (-stat).

    Image 3

  10. Now we have where the .NET Method Table is located, for SqlCommand; go forward exploring which methods it encapsulates. Use:
  11. !dumpmt -md 6523db08

    Explaining the command: dump method descriptions given a Method Table code.

    Image 4

  12. Good that we have the list of methods and their entry points. Many .NET SQL commands use ExecuteReader for query execution. We can search on this:
  13. Image 5

  14. Ok, we got it; we found the entry point for this function. Let's set a breakpoint there. Use:
  15. Bp 651f9c24

    and press F5 to continue Management Studio execution.

  16. Play with Management Studio while the debugger is still attached. By doing this, you will observe Management Studio freeze, what happened? The breakpoint has already been triggered.
  17. Now go WinDbg again:
  18. Image 6

  19. Yes, we can confirm that Windbg has stopped as expected.
  20. We can start exploring the Managed Stack objects. To do this, use:
  21. !dso

    Dump the stack objects:

    Image 7

  22. Great !!! Now we have a part of the SQL command and we can observe the SqlCommand on top of our stack. Why not see the content? To do this, we can use:
  23. !do 0c51f004

    Explaining: the !do=dump object given an address, 0c51f004 in this case.

    Image 8

  24. Interesting information dumped there, the command text is there. But where is it located? The answer is in the offset. We need to get the pointer of the current SqlCommand+10, and display the string (CommandText) contained there:
  25. !do poi(0c51f004+10)

    Explaining: !do=dump the object, given a pointer to the 0c51f004+10 position. Nice, we have what we expected.

    Image 9

  26. 0c51f004 is a fixed address, but what does this address contain on any of the records? Let's review; just execute:
  27. r

    to display the processor record.

    Image 10

  28. Good, we can try this then:
  29. !do poi(ecx+10)

    Image 11

  30. But as you can observe, we have a lot of not so useful information. We just need to print the string query. We can do the following trick:
  31. .printf "%mu",poi(ecx+10)+c

    Image 12

  32. You are probably asking: Why printf? Why %mu ? poi(ecx+10)+c? printf prints a string ending in 0, %mu prints a Unicode string, poi(ecx+10) is clear, but why +c? Because the first char of a string class starts at the +c offset. You can see another article to clarify this: http://www.codeproject.com/KB/miscctrl/extractstringprocess.aspx
  33. Do we need to do this for every single command executed? The answer is no. This is why WinDbg has conditional breakpoints. Remember that we broke on the SqlReader command execution, and we can use:
  34. bp eip ".printf \"\\n%mu\",poi(ecx+10)+c;gc"

    Break in the current eip address (Current Execution Address), and once this stops, execute the command between double quotes (we explained this). Once this get this done, just press F5 and enjoy playing with Management Studio and looking at the SQL commands!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Tester / Quality Assurance
Bolivia Bolivia
Quality Assurance

Comments and Discussions

 
Question.Net with Postgres Pin
raranibar5-Feb-16 4:14
professionalraranibar5-Feb-16 4:14 
Questionfinding sql stmt from a thread Pin
Nikhil Shikarkhane22-Sep-11 4:45
Nikhil Shikarkhane22-Sep-11 4:45 
AnswerRe: finding sql stmt from a thread Pin
Rene Pally4-Oct-11 9:20
Rene Pally4-Oct-11 9:20 
Hi Nikhi,

Sorry for the late response. Answering your questions.
1.- Normally the SQL Sentences are being stored on the stack and it requires more examination before to get the plain text SQL Sentence. I can help you by using a remote connection to your PC or server. Just write me at: rpally@reversingsoftware.com if you are really interested to do that.
2.- Unfortunately there is no Java SOS extension for Windbg, in java probably we will require a native debugging to find interesting information. Java probably uses ODBC libraries for connect to database or another native library (DLL) provided by SQL Server. What you can do is attach the process server (Java in this case) and see which libraries are being loaded in runtime using
C++
lm
(Loaded Modules) and please give me this information to help you.
3.- If you have any other question just write me.

Sorry, I was not very clear but I will need more info to help you.

Regards
Rene.
Excellent

GeneralSimply superb Pin
Anantharaman_N24-Aug-10 23:19
Anantharaman_N24-Aug-10 23:19 
GeneralRe: Simply superb Pin
Rene Pally25-Aug-10 2:19
Rene Pally25-Aug-10 2:19 
GeneralMy vote of 5 Pin
PoleTT18-Aug-10 4:02
professionalPoleTT18-Aug-10 4:02 
GeneralMy vote of 3 Pin
mancab17-Aug-10 22:49
mancab17-Aug-10 22:49 
GeneralRe: My vote of 3 Pin
Rene Pally18-Aug-10 2:33
Rene Pally18-Aug-10 2:33 
GeneralMy vote of 5 Pin
Pranay Rana17-Aug-10 0:53
professionalPranay Rana17-Aug-10 0:53 
GeneralRe: My vote of 5 Pin
Rene Pally17-Aug-10 1:47
Rene Pally17-Aug-10 1:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.