?
Solved

Pervasive SQL (PSQL): Wireshark to audit SQL statements

Posted on 2013-11-05
7
Medium Priority
?
1,671 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 29

Assisted Solution

by:Bill Bach
Bill Bach earned 2000 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 29

Assisted Solution

by:Bill Bach
Bill Bach earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 29

Accepted Solution

by:
Bill Bach earned 2000 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 29

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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

862 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