Click here to Skip to main content
11,709,366 members (54,708 online)
Click here to Skip to main content

Tagged as

Intercepting .NET SQL queries at runtime

, 16 Aug 2010 CPOL 17K 12
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

You may also be interested in...

Comments and Discussions

 
Questionfinding sql stmt from a thread Pin
Nikhil Shikarkhane22-Sep-11 4:45
memberNikhil Shikarkhane22-Sep-11 4:45 
I am a noob in WinDbg, so my question might sound lame.

I want to learn to find out sql statement executed by a given thread.
For example i have a thread with following stack. How should i go about finding the sql stmt it executed? I have a java application which uses hibernate. Also how do i load sos module ?
I have a lot of questions in my head but i will it for later. Thank you for this article and your response.

Child-SP          RetAddr           Call Site
00000000`3042a788 00000000`76c72f60 ntdll!NtSignalAndWaitForSingleObject+0xa
00000000`3042a790 00000000`00d14f97 kernel32!SignalObjectAndWait+0x110
00000000`3042a840 00000000`00d12eba sqlservr!SOS_Scheduler::Switch+0x181
00000000`3042af90 00000000`00d12628 sqlservr!SOS_Scheduler::SuspendNonPreemptive+0xca
00000000`3042afd0 00000000`00d12994 sqlservr!SOS_Scheduler::Suspend+0x2d
00000000`3042b000 00000000`0124ae87 sqlservr!EventInternal<Spinlock<153,1,0> >::Wait+0x1a8
00000000`3042b050 00000000`00dfd533 sqlservr!LockOwner::Sleep+0x1f7
00000000`3042b0e0 00000000`00d3193f sqlservr!lck_lockInternal+0xd7a
00000000`3042be70 00000000`00d3657e sqlservr!GetLock+0x1eb
00000000`3042bf40 00000000`0124b45e sqlservr!BTreeRow::AcquireLock+0x1f9
00000000`3042c010 00000000`00d46ae5 sqlservr!IndexDataSetSession::GetRowByKeyValue+0xb73
00000000`3042c130 00000000`00d472a9 sqlservr!IndexDataSetSession::FetchRowByKeyValueInternal+0x299
00000000`3042c530 00000000`00d538c4 sqlservr!RowsetNewSS::FetchRowByKeyValue+0x403
00000000`3042c670 00000000`00d48f9c sqlservr!CValFetchByKey::ManipData+0x81
00000000`3042c6f0 00000000`00d53836 sqlservr!CEs::GeneralEval4+0xdb
00000000`3042c7a0 00000000`00df4c94 sqlservr!CQScanRangeNew::GetRow+0xfc
00000000`3042c7f0 00000000`00df4c2a sqlservr!CQScanNLJoinNew::GetRowHelper+0x112
00000000`3042c830 00000000`00df4c2a sqlservr!CQScanNLJoinNew::GetRowHelper+0x73
00000000`3042c870 00000000`00df34c7 sqlservr!CQScanNLJoinNew::GetRowHelper+0x73
00000000`3042c8b0 00000000`00da99bd sqlservr!CQScanAssertNew::GetRow+0x2b
00000000`3042c920 00000000`00da984b sqlservr!CQueryScan::GetRow+0x69
00000000`3042c950 00000000`00de2e41 sqlservr!CXStmtQuery::ErsqExecuteQuery+0x602
00000000`3042cae0 00000000`00de2c9a sqlservr!CXStmtDML::XretDMLExecute+0x222
00000000`3042cb80 00000000`021a1724 sqlservr!CXStmtDML::XretExecute+0xa5
00000000`3042cbb0 00000000`013754f5 sqlservr!CMsqlExecContext::ExecuteStmts<1,0>+0xdbc
00000000`3042db60 00000000`00d6b30c sqlservr!CMsqlExecContext::FExecute+0x9a0
00000000`3042dce0 00000000`021d20bf sqlservr!CSQLSource::Execute+0x7b2
00000000`3042de10 00000000`021d1b33 sqlservr!InternalPrepExec+0x147
00000000`3042e280 00000000`021d5587 sqlservr!SpPrepExec+0xa32
00000000`3042e940 00000000`021d5bbb sqlservr!CSpecProc::ExecuteSpecial+0x177
00000000`3042ea80 00000000`00d6c1a6 sqlservr!CSpecProc::Execute+0x297
00000000`3042ebf0 00000000`00d5cd34 sqlservr!process_request+0x64b
00000000`3042f250 00000000`00d1bbd8 sqlservr!process_commands+0x4ab
00000000`3042f460 00000000`00d1b8ba sqlservr!SOS_Task::Param::Execute+0x12a
00000000`3042f570 00000000`00d1b6ff sqlservr!SOS_Scheduler::RunTask+0x96
00000000`3042f5d0 00000000`01238fb6 sqlservr!SOS_Scheduler::ProcessTasks+0x128
00000000`3042f640 00000000`01239175 sqlservr!SchedulerManager::WorkerEntryPoint+0x2b6
00000000`3042f720 00000000`01239839 sqlservr!SystemThread::RunWorker+0xcc
00000000`3042f760 00000000`01239502 sqlservr!SystemThreadDispatcher::ProcessWorker+0x2db
00000000`3042f810 00000000`74a837d7 sqlservr!SchedulerManager::ThreadEntryPoint+0x173
00000000`3042f8b0 00000000`74a83894 msvcr80!endthreadex+0x47
00000000`3042f8e0 00000000`76c1f56d msvcr80!endthreadex+0x104
00000000`3042f910 00000000`76e53021 kernel32!BaseThreadInitThunk+0xd
00000000`3042f940 00000000`00000000 ntdll!RtlUserThreadStart+0x1d

AnswerRe: finding sql stmt from a thread Pin
Rene Pally4-Oct-11 9:20
memberRene Pally4-Oct-11 9:20 
GeneralSimply superb Pin
Anantharaman_N24-Aug-10 23:19
memberAnantharaman_N24-Aug-10 23:19 
GeneralRe: Simply superb Pin
Rene Pally25-Aug-10 2:19
memberRene Pally25-Aug-10 2:19 
GeneralMy vote of 5 Pin
Paola Pierola18-Aug-10 4:02
memberPaola Pierola18-Aug-10 4:02 
GeneralMy vote of 3 Pin
mancab17-Aug-10 22:49
membermancab17-Aug-10 22:49 
GeneralRe: My vote of 3 Pin
Rene Pally18-Aug-10 2:33
memberRene Pally18-Aug-10 2:33 
GeneralMy vote of 5 Pin
Pranay Rana17-Aug-10 0:53
memberPranay Rana17-Aug-10 0:53 
GeneralRe: My vote of 5 Pin
Rene Pally17-Aug-10 1:47
memberRene Pally17-Aug-10 1: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
Web04 | 2.8.150819.1 | Last Updated 16 Aug 2010
Article Copyright 2010 by Rene Pally
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid