Solved

Pervasive SQL (PSQL): Wireshark to audit SQL statements

Posted on 2013-11-05
7
1,325 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now