Tracing SQL Queries in Real time for MySQL Databases using WINDBG and Basic assembler knowledge

Https://www.codeproject.com/Articles/43305/Tracing-SQL-Queries-in-Real-Time-for-MySQL-Databas Assembly and MySQLIntroduction

One of the more interesting things for any person are to see how the internal engines from the server software work. The purpose of this article are to show how we can apply basic assembler knowledge to find interesting runtime information.

Few days ago, my friend is involved on Php+mysql site development. He was experiencing some issues.

Ok, we can start.

2. You'll need MySQL installation download and install any version of MySQL. Please make sure this your MySQLD service is running successfully (in other words, ensure. Your MySQL is working prope Rly).
4. Download the latest version of Windbg for Windows from the Microsoft site.
6. Launch Windbg.
8. Press F6 and attach the mysqld.exe process.
10. Set the Windbg symbols properly by using File->symbols File Path:srv*c:\windows*http://msdl.microsoft.com/ Download/symbols.
12. On Windbg command line, execute .reload .
14. Press F5 to run the process (if you attach the process, this gets frozen). Using F5 or with G command, the process runs.
16. Here are the tricky part. MYSQLD.exe process (or service in this case) was in charge of executing the SQL Queries from PHP pages, O R MYSQL different clients. Navicat is a cool MySQL client which allows us to see the MYSQL Server in a graphical mode, like Microsoft Management Stud IO does with SQL Server.
18. Let's start navicat tool for educative purposes (if you want), or use your own PHP or any other application which is a MYS QL Client.
20. EXECUTE is the magic word. The tricky part is:why if MYSQLD. EXE Process performs a SQL Query executing any kind of function in any part of EXECUTE  their internal code? Let ' s put a breakpoint there.
22. Breakpoint:stop The current MYSQLD  execution by Ctrl+break on Windbg and put the following command: bm *mysqld*!*execute* (bm= Break in mask, library all *mysqld* and function *execute*).
24. Press F5 and perform any client operation with PHP Page or Navicat or any other MYSQL client.
26. You'll see a freeze in your page or navicat:why? Because MYSQLD was stopped. Lets See the WinDbg.

28. Nice, the MYSQLD process stopped on, let's see MYSQLD!MYSQL_EXECUTE_COMMAND the Stack trace:use KB command:
30. As you can see, you can observe directly the input parameters for on the Args to child section MYSQL_EXECUTE_COMMAND  . Every hexadecimal value there represents normally a pointer to any specified data structure. Is any there on any of the string  Args to pointer? Let's examine this.
32. Click on View->memory. On Address, write the Pointer (captured from-args-to-child) try with 01eb2630 and the other Args to child:

34. We did not see any interesting thing on all args to child parameters MYSQL_EXECUTE_COMMAND for, and what is about the previous Functi On: mysql_parse ?

36. eureka! There is something interesting there. What if we print the value there? Let ' s execute: .printf “%ma”.03b62a68 :

38. Yes definitely a SQL Query captured from MYSQLD process. Now if we have the function, we want, delete all breakpoints by using the command and use BC * bp mysqld!mysql_parse and continue the execution by using F5 or G-windbg command line.
40. Now we WinDbg stopped on mysqld!mysql_parse .
42. The one million question Is:everytime that any MYSQL Query executes something, it'll freeze my app until press F5 app? The answer is no, if we use a more intelligent breakpoint. We know the function mysql_parse , but are which memory address it is stored? A Call stack theory:

44. Let's explain this if the process is starting a function and it pushes the function parameters to be used. Then what happens with ESP processor register? Example:  VOID SUM (INT x,int *y) .   esp  represents the top  of the stack, and   ebp  the Base address for The  Stack . Let ' s assume That  esp=1000 .
2. The process pushes Y  ESP  The pointer to the value and decreases their value, decreases the top of the stack? S Ounds confusing, Yes it's, in the true Windows operative System, the the the stack is in the lower part of TOP  me Mory than EBP (Base pointer of the stack) ESP=ESP-4 : 996 .
4. The process pushes the value of X ESP=ESP-4 : 992 .
6. The process push the return address for the previous function: ESP=ESP-4 : 998 .
46. When the Windbg stops, the stack was in the state c  . For example, you can find the second parameter by just executing a simple math operation:2o parameter are located in The POI(ESP+8) , as we can see the Windbg previous picture, YES, we are the string  second parameter. Let's try this:
48. Printing the 2o parameter: .printf “%ma”,poi(esp+8) .

50. Why POI? Poi in WinDbg represents or gets the pointer address ESP+8 of, %ma  means or represent just a ASCII chars. represents Unicode.
52. Good, now we can put together in a simple breakpoint.
54. The complete breakpoint: bp mysqld!mysql_parse ".printf \"\\n%ma\",poi(esp+8);gc"
56. When we set Bp=breakpoint  mysqld!mysql_parse in the function, it prints an ASCII string  given a pointer to the esp+8  (second parame ter, and to gc  continue the execution without stop.

License

This article, along with any associated source code and files, is licensed under the code Project Open License (Cpol)

Tracing SQL Queries in Real time for MySQL Databases using WINDBG and Basic assembler knowledge