Solved

Pervasive SQL (PSQL): Wireshark to audit SQL statements

Posted on 2013-11-05
7
1,432 Views
Last Modified: 2014-03-20
I'm using Wireshark to audit the SQL statements running on a Pervasive SQL database. I'm able to view some of the statements, but much of the captured traffic looks like gibberish. And I'm not seeing some of the SQL statements I'm expecting to see.

The application we use runs as an exe file on the same server as the database. I'm wondering if some of the database activity never crosses the wire since the database and the application are on the same server. Or maybe I'm not following the right TCP stream. I've set up a filter to only show traffic on port 1583. I then select the option to follow the TCP Stream for the client computer I'm interested in analyzing.

Here's a sample of one of the captures:

Insert Into PR_EARN(REF_NO,LOC_NO,DIV_NO,EMP_NO,PAY_CODE,HOURS,RATE,PAY_AMT,YN_1,YN_2,YN_3,YN_4,YN_5,YN_6,YN_7,YN_8,YN_9,YN_12,YN_13,YN_14,YN_15,JOB,MULT,TP_NO_1,TP_NO_2,TP_NO_3,STATES_1,STATES_2,STATES_3,ADMIN_FEE,PAY_DATE,SUMM_NO,BILL_AMT,ITEM_NO) Select REF_NO,LOC_NO,DIV_NO,EMP_NO,CODE,HOURS,RATE_1,AMOUNT_1,YN_5,YN_10,YN_11,YN_12,YN_3,YN_4,YN_2,YN_20,YN_30,YN_24,YN_25,YN_14,' ',JOB,MULT*100,TP_NO_1,TP_NO_2,TP_NO_3,STATES_1,STATES_2,STATES_3,AMOUNT_6,'2013-11-07',SUMM_NO,BILL_AMT,ITEM From PR_ITEM2 Where SUMM_NO=28740052 And TYPE=1.............'.]....:.......*...*...
.k.............................t..'.]....$.................l.l....$...'.]......................l....'.]....".................m.m....$.'.]......................m....'.]....$.................n.n....$...'.]......................n....'.]....$.................o.o....$...'.]......................o....'.]....".................p.p.....p'.]....".................p....2.*.'.]....4.......$...$...".q.q..2.*.....SQL_CUR4b0ec48'.]....................".q....'.]....4.......$...$...Q.r.r..2.*...................'.]....................Q.r....'.]....R.......B...B.....s.s..2.*....+Delete From PR_ITEM2 Where SUMM_NO=28740052.'.]....,.................s..................'.]....$.................t.t..2.*...'.]....,.................t..................'.]....$.................u.u..2.*...'.]....,.................u..................'.]....4.......$...$...Q.v.v..2.*..............h....'.]....................Q.v....'.]....4.......$...$...Q.w.w..2.*...................'.]....................Q.w....'.]....^.......N...N...
.x.x..2.*....+Delete From PR_ITEM2 Where SUMM_NO=28740052.............'.]....:.......*...*...
.x................................'.]....$.................y.y..2.*...'.]......................y....'.]....".................z.z..2.*.'.]......................z....'.]....$.................{.{..2.*...'.]......................{....'.]....$.................|.|..2.*...'.]......................|....'.]....".................}.}.....p'.]....".................}......$.'.]....4.......$...$...".~.~....$.....SQL_CUR4b0ec48'.]....................".~....'.]....4.......$...$...Q........$...................'.]....................Q......'.]....R.......B...B............$....+Delete From PR_INP2  Where SUMM_NO=28740052.'.]....,....................................'.]....$........................$...'.]....,....................................'.]....$........................$...'.]....,....................................'.]....4.......$...$...Q........$..............h....'.]....................Q......'.]....4.......$...$...Q........$...................'.]....................Q......'.]....^.......N...N...

As you can see, some of the SQL code is clearly visible. But much of the capture is unintelligible. Is there a way to more effectively use Wireshark to capture SQL statements being sent to the Pervasive database? Or will some of the statements always be hidden since the database and the application are on the same server?
0
Comment
Question by:Perfishent
  • 4
  • 3
7 Comments
 
LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 500 total points
ID: 39626028
Actually, you ARE capturing the statements, and you can see everything from Wireshark already if you are using a CLIENT DSN.  The text you see is the text of the SQL statement being sent to the engine -- the rest of the "gibberish" is the other overhead in sending the query, setting parameters, returning data, and so on.  It may be gibberish to a human, but it is critical in the course of making the ODBC connection work properly.

Now, if you are using an ENGINE DSN, then you will NOT see traffic, because it is being sent directly to the SRDE via memory calls, bypassing the network layer altogether.  As such, you may see nothing in Wireshark in this case.  Changing from an Engine DSN to a Client DSN may yield better results.

There are a few other options to making this easier:
1) You could use a third party tool like SQLInterceptor, which is designed to read the data from the WinPcap interface (or from a Wireshark PCAP trace file) and spit queries (and other commands) out in "human-readable" format.  
2) You could enable the Query Plan Viewer on the ODBC Engine DSN.  In registry under Hkey_Local_machine/Software/Odbc/Odbc.ini, for the datasource, add two registry keys:
         String value QryPlan=1    
      String value QryPlanOutput=c:\test.qpf
Now, every time a new query comes in via ODBC, the query plan will be written out.  This can have an impact on the engine, though, slowing it down a bit.  After you create the query plan file, you can use the Query Plan Viewer (W3SQLSPV.EXE) to open it up and view the contents.   You can further extract the SQL statements out of the QPF file using a third party tool called ParseQPF.
0
 

Author Comment

by:Perfishent
ID: 39627780
I don't see any Pervasive Client DSN's set up on the server. But I do see the ODBC registry entries for each database name under HKEY_LOCAL_MACHINE-->Software-->Wow6432Node-->ODBC-->ODBC.INI. Does that mean the application uses Engine DSN's? If so, I will try the Query Plan Viewer method.

I tried doing a Wireshark capture at night when no one  else was using the application. I added a new record through the user interface of the application. In the capture file, I could see some supporting SQL queries in plain text, but I could not see the main INSERT query anywhere. I could see the relevant table name and the relevant insert values scattered throughout the capture but nothing intelligible. So, it looks like Wireshark is capturing the operation, but it's just not in a human readable format.

I may give the trial version of SQL Interceptor a try.
0
 
LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 500 total points
ID: 39627796
You can confirm the type of DSN by going into the 32-bit ODBC Administrator:
   C:\Windows\SysWoW64\ODBCAD32.EXE
In there, you should see the 32-bit DSN's, and you can tell if they are Engine or Client DSN's.  

If you saved the PCAP file from your test (it doesn't work with PCAPNG files), then this command should work:
    SQLInterceptor /Fcapturefile.pcap
The other parms can be left as the default values, and it should show you the queries you are seeking -- as long as they are coming in over the network connection.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Perfishent
ID: 39715420
If SQL Interceptor captures the same streams as WireShark, then I don't think it will be helpful in viewing more SQL than I'm already viewing with WireShark. Some of the SQL seems to be internal to the application and never crosses the wire.

When I run C:\Windows\SysWoW64\ODBCAD32.EXE I see that the DSN's are System DSN's. I can't change the type of DSN's the system uses because it's a third party application.

Are the SQL statements still capturable using PCC or some other tool?
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 39715433
If the client and the server are two different machines, then every request MUST cross the network wire.  Many applications use BOTH the SQL interface AND the Btrieve interface.  Do you see any traffic to the server on TCP port 3351?  If so, then perhaps the app is using Btrieve calls for some of the database accesses.  You can extract the individual database calls with BtrvInterceptor, or by enabling the MKDE Trace capability on the server itself (from within the PCC).  

As for capturing the *SQL* statements with this PCC -- this is not possible, as this tool doesn't have any related feature.  Your two options are already listed above.
0
 

Author Comment

by:Perfishent
ID: 39715475
The request has to cross the wire, but couldn't the request kick off a series of SQL statements that don't all cross the wire?

I will try adding port 3351 (Btrieve) to the WireShark captures. I had only been capturing port 1583.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39715500
The only way that could happen is if the app uses stored procedures. You would see this as a CALL statement in the SQL data.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Unrecognized Database Format 8 122
Update from TABLE-A to TABLE-B 5 56
SSRS troubles 4 68
How to resolve SQL Server DB deadlock which makes my application hangs ? 6 45
This article describes some very basic things about SQL Server filegroups.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question