[Snort-devel] [ snort-Bugs-645619 ] ODBC problems in spo_database.c + fix

noreply at ...12... noreply at ...12...
Fri Nov 29 06:40:06 EST 2002

Bugs item #645619, was opened at 2002-11-29 10:38
You can respond by visiting: 

Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Martin J. Evans (bohicaa)
Assigned to: Nobody/Anonymous (nobody)
Summary: ODBC problems in spo_database.c + fix

Initial Comment:
I mailed this list of problems and the patch to fix them to
Roman Danyliw but I have not seen them in CVS yet.
The patch can be found here:

minor alterations:

o rename u_handle, u_environment (that is what it is)

o unusual mix of ODBC 2.0 and ODBC 3.0 calls (e.g.
SQLAllocEnv and SQLFreeHandle). Since very few ODBC
calls are made standardised on ODBC 2.0.


o The code assumes the only way you can access MS SQL
Server is through the DB-Library but you can access MS
SQL Server through ODBC.  The hard-wired '[]'
characters around the "schema" table should really be
using the ODBC driver's SQL_IDENTIFIER_QUOTE_CHAR. This
may apply to other tables too in other databases with
ODBC drivers but only the "schema" one changed in this fix.

o Code not using ODBC diagnostics which makes
diagnosing an error a pain. Added odbc_errors()
function which uses SQLError() to retrieve ODBC error

o Transaction support appears to have been added to
Insert() using "BEGIN", "COMMIT" and "ROLLBACK" but the
SQL92 is "BEGIN TRANSACTION" not just "BEGIN". Any
case, ODBC has transaction support using SQLTransact()
so use that.

o The timestamp created in Database() is not ODBC
standard. However, code already existed for MS SQL
Server that was correct for ODBC too.

o snort_escape_string() was attempting to escape alot
of characters

[1] it did not need to and 
[2] used the wrong escape (i.e. it is not '\'). Only '
needs to be escaped by doubling it up.

o CheckDBVersion() seemed to have #ifndef ENABLE_MSSQL
around code testing if the dbtype_id == DB_MSSQL and
hence the "schema" table would not be escaped properly.
The escaping also was required for ODBC.

o The ODBC code was leaking a statement handle every
time Select() or Insert() was called. Code now
allocates one statement handle and closes it after each

o The ODBC code was using SQLPrepare/SQLExecute
unnecessarily when SQLExecDirect() could be used -
saves a call.

o ODBC code in Select() to test if any data was
returned was incorrect and only working by accident. It
used SQLRowCount which rarely returns anything other
than -1 with most ODBC drivers for a select statement.
 Code changed to use SQLNumResultCols() and check
resultant columns is only 1.

o The ODBC code in Connect() to connect to the database
was checking the return status with SQL_SUCCESS causing
snort to not accept perfectly good connections. Many
ODBC drivers return SQL_SUCCESS_WITH_INFO for
SQLConnect calls (i.e. MS SQL Server reports language
or database changed on connection). Code changed to use
the SQL_SUCCEEDED macro.

o ODBC connection handle leaked on termination and
causing the freeing of the environment to fail - added
SQLFreeConnect call to Disconnect(). Also added code to
free up the statement in Disconnect().


You can respond by visiting: 

More information about the Snort-devel mailing list