[Snort-devel] last_cid in new database scheme v106

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


Mike,

This seems quite useful.  Thanks for the pointer!

Roman

On Wed, 11 Sep 2002 14:31:54 -0500, Michael Anderson <mca at ...498...> wrote :

> Roman,
> You said that you do not have access to an Oracle database.  Oracle will let
you install a single user database for free for development purposes.  Go to
technet.oracle.com.  You
> might have to sign up but it doesn't cost you anything.
> 
> -Mike Anderson
> 
> Roman Danyliw wrote:
> 
> > 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
> >
> > -------------------------------------------------------
> > In remembrance
> > www.osdn.com/911/
> > _______________________________________________
> > Snort-devel mailing list
> > Snort-devel at lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/snort-devel
> 
> 
> 
> 




More information about the Snort-devel mailing list