Need to log all the SQL commands from an ODBC connection. How?

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,883
Reaction score
3,057
Location
On the toilet
Hi guys,

Would like to do the items as mentioned in the topic title but haven't found anything other than the ODBC admin logger while, it works isn't the most user friendly.
Figured someone must have had to do this in the past and my google-fu is failing me.

Any help would be appreciated.
 
Pho3nix said:
Hi guys,

Would like to do the items as mentioned in the topic title but haven't found anything other than the ODBC admin logger while, it works isn't the most user friendly.
Figured someone must have had to do this in the past and my google-fu is failing me.

Any help would be appreciated.
Click to expand...
What database?
 
Assuming MSSQL ,If a SQL server allows connections from an ODBC driver does it not by default allow SQL profiler on the same port ?
I imagine you can connect directly to the SQL server using SQL profiler ? Unless the intention is to programmatically capture the queries and store it somewhere.
 
SBSP said:
Assuming MSSQL ,If a SQL server allows connections from an ODBC driver does it not by default allow SQL profiler on the same port ?
I imagine you can connect directly to the SQL server using SQL profiler ? Unless the intention is to programmatically capture the queries and store it somewhere.
Click to expand...

Exactly this. Need to pseudo reverse investigate.
 
The ODBC connector (ODBCAD32.EXE) Has a tracing tab with options log the data to file. If you enable it you could read the data from that file. Never tried it before
 
You mean this?
odbc.png

It works but not the most user friendly as I'm working with a bunch of various queries. Anything else you can suggest?
 
Please see below.
3 sections for a select :(
Code: 
EXCEL           257c-4460	EXIT  SQLDriverConnectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		HDBC                0x000002A37756A5F0
		HWND                0x0000000000070816
		WCHAR *             0x00007FFF5242CF80 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x00007FFF5242CF80 <Invalid buffer length!> [-3]
		SWORD                       -3 
		SWORD *             0x0000000000000000
		UWORD                        1 <SQL_DRIVER_COMPLETE>

		DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'testDB'. (5701) 

		DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (5703) 

EXCEL           257c-4460	ENTER SQLAllocHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x000002A37756A5F0
		SQLHANDLE *         0x000002A37594B638

EXCEL           257c-4460	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x000002A37756A5F0
		SQLHANDLE *         0x000002A37594B638 ( 0x000002A36A78AAA0)

EXCEL           257c-4460	ENTER SQLSetStmtAttr 
		SQLHSTMT            0x000002A36A78AAA0
		SQLINTEGER                   4 <SQL_ATTR_ASYNC_ENABLE>
		SQLPOINTER                 1 <SQL_ASYNC_ENABLE_ON>
		SQLINTEGER                  -5 

EXCEL           257c-4460	EXIT  SQLSetStmtAttr  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x000002A36A78AAA0
		SQLINTEGER                   4 <SQL_ATTR_ASYNC_ENABLE>
		SQLPOINTER                 1 <SQL_ASYNC_ENABLE_ON>
		SQLINTEGER                  -5 

EXCEL           257c-4460	ENTER SQLExecDirectW 
		HSTMT               0x000002A36A78AAA0
		WCHAR *             0x000002A3797645F0 [      36] "SELECT * FROM "testDB"."dbo"."Names""
		SDWORD                    36

EXCEL           257c-4460	EXIT  SQLExecDirectW  with return code 2 (SQL_STILL_EXECUTING)
		HSTMT               0x000002A36A78AAA0
		WCHAR *             0x000002A3797645F0 [      36] "SELECT * FROM "testDB"."dbo"."Names""
		SDWORD                    36

EXCEL           257c-17d4	ENTER SQLExecDirectW 
		HSTMT               0x000002A36A78AAA0
		WCHAR *             0x000002A3797645F0 [      36] "SELECT * FROM "testDB"."dbo"."Names""
		SDWORD                    36

EXCEL           257c-17d4	EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x000002A36A78AAA0
		WCHAR *             0x000002A3797645F0 [      36] "SELECT * FROM "testDB"."dbo"."Names""
		SDWORD                    36
 
Last edited:
Not as crazy as I thought it would be. Seems you just need to look for the WCHAR * lines. Which makes sense, as WCHAR * seems to be a pointer, and the 36 seems to be the size of the string. I cannot say for certain, as only one sample has been provided.

Any way, a simple Python 3 script like so should help give you an indication.

Code: 
import pymssql as sql

path_to_file = 'D:\\'
file_name = 'odbc_log.log'
with open(path_to_file + file_name, 'r') as log_file:
    ls = [line.rstrip() for line in log_file.readlines()]

valid = []
for line_num, value in enumerate(ls):
    if 'WCHAR' in value and len(value) > 53:
        valid.append(value)
    
server = 'localhost'
database = 'testDB'
user_id = 'log_writer'
password = 'password_for_log_writer'

with sql.connect(server=server, database=database,
                 user=user_id, password=password) as sqlConn:
    write_cursor = sqlConn.cursor()

    for query in valid:
            write_cursor.execute("INSERT INTO log_table (Executed_Query) VALUES ('%s')",
                                 query)

    sqlConn.commit()

A note, I haven't tested this out. So there may be syntax errors.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X