Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pervasive SQL (PSQL): Wireshark to audit SQL statements

Posted on 2013-11-05
7
Medium Priority
?
1,630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 28

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 28

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
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 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 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

636 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