Click here to Skip to main content
Click here to Skip to main content

Tagged as

Intercepting .NET SQL queries at runtime

, 16 Aug 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Intercepting .NET SQL queries at runtime - SQL Management Studio case.

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

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

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

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

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

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

  11. 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:
  12. Ok, we got it; we found the entry point for this function. Let's set a breakpoint there. Use:
  13. Bp 651f9c24

    and press F5 to continue Management Studio execution.

  14. 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.
  15. Now go WinDbg again:
  16. Yes, we can confirm that Windbg has stopped as expected.
  17. We can start exploring the Managed Stack objects. To do this, use:
  18. !dso

    Dump the stack objects:

  19. 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:
  20. !do 0c51f004

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

  21. 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:
  22. !do poi(0c51f004+10)

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

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

    to display the processor record.

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

  27. 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:
  28. .printf "%mu",poi(ecx+10)+c

  29. 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
  30. 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:
  31. 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)

Share

About the Author

Rene Pally
Tester / Quality Assurance
Bolivia Bolivia
Quality Assurance

Comments and Discussions

 
Questionfinding sql stmt from a thread PinmemberNikhil Shikarkhane22-Sep-11 5:45 
AnswerRe: finding sql stmt from a thread PinmemberRene Pally4-Oct-11 10:20 
GeneralSimply superb PinmemberAnantharaman_N25-Aug-10 0:19 
Absolutely awesome article! Thanks a ton for taking the time (and effort) to write it all down. I have windbg with me, but was never able to figure out what to do with it. This really got me started
GeneralRe: Simply superb PinmemberRene Pally25-Aug-10 3:19 
GeneralMy vote of 5 PinmemberPaola Pierola18-Aug-10 5:02 
GeneralMy vote of 3 Pinmembermancab17-Aug-10 23:49 
GeneralRe: My vote of 3 PinmemberRene Pally18-Aug-10 3:33 
GeneralMy vote of 5 PinmemberPranay Rana17-Aug-10 1:53 
GeneralRe: My vote of 5 PinmemberRene Pally17-Aug-10 2:47 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 16 Aug 2010
Article Copyright 2010 by Rene Pally
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid