[Snort-devel] last_cid in new database scheme v106

Roman Danyliw roman at ...49...
Wed Sep 11 12:17:14 EDT 2002


Chad,

> Speaking practically... The primary key for all of these tables
> shouldn't be sid,cid anyway.  There should only be one value (int?) as
> the key.

In theory, you are correct; a single primary key instead of a composite
one is preferred.  The sid/cid key pair has stayed with us more for
legacy reasons that anything else.  However, I am now reluctant to break
all the existing applications that read the snort database.

> This would easily prevent all collisions of CID, since a
> sequence could be used (in most databases).

"Most databases" is the key phrase here.  MySQL, by far the most popular  
database logging configuration, does not support explicit sequences.  For 
this reason, there is the addition sensor.cid field.

Furthermore, it would be the introduction of a sequence (which internally
keep state identical to what is achieved by the sensor.last_cid), not the
elimination of the composite primary that would prevent the CID collision 
problem.  

> Then, use sid only as an
> identifier, and only in the event table.   Structurally this is more
> sound, and far more efficient for the DBs.

Eliminating the sid from the primary key would only make the DB design  
more space efficient.  Properly written databases will create a
single index built from the composite primary key.  This single
index, not the individual primary key fields, is used for the vast 
majority of queries.

> The next value could easily
> be selected, and placed in the placeholders for its bound placeholder.
> I'm in the process of trying to write a new database plugin
> (database2?), that will use this new strucutre, as well as provide more
> efficient means of insert/update/select statements.  The use of prepared
> statements with bound placeholders will prevent the rebuilding of query 
> strings, and in the vast majority of databases, increase response
> times... While only staying the same in others.

As the maintainer of the database code, I welcome your contribution and  
that of the original author of the Oracle code (Mike Andersen and Thomas  
Stenhaug).

That said, I would caution you about the universality of the
bound-variable paradigm.  Yes, it is present in real enterprise
databases currently supported in Snort: Oracle and MS-SQL Server.
However, the two most popular databases, MySQL and PostgreSQL, do not
support it.  I would prefer to see a solution which gracefully addresses
the advantages and short-comings of all the databases Snort supports.

Snort is unique among its IDS peers in that it natively supports so
many database back-ends.

> The other major reason for this is to prevent the error: "database:
> oracle_error: ORA-01704: string literal too long."  which I would
> consider a very major bug... Since this error does not truncate the data
> when you get it, no data is entered into the field (the data_payload in
> data field).  Which means, that even though the alert will appear in the
> database, there will be no data for anyone to investigate whether or not
> it was a positive.  This makes a large number of alerts pointless since
> they are often overflows that are greater than the max length allowed in
> a query string.  Placeholders/bound vars fix this.

Agreed.  This is a huge problem for those who log to Oracle.  I will
gladly accept any temporary hack that will do the proper data binding
calls for Oracle.  I believe this would only affect INSERTs to the
data table.  My lack of access to an Oracle DB precludes me from making
this change myself.

The long-term solution currently being written is a more generalized data
structure to represent the DB schema in the plug-in.  The required 
fields for an INSERT or SELECT would be populated into the data
structure, and database-specific code would be responsible for converting
it to the appropriate SQL and making the necessary API calls.

cheers,
Roman




More information about the Snort-devel mailing list