[Snort-devel] last_cid in new database scheme v106

Michael Anderson mca at ...498...
Wed Sep 11 12:28:07 EDT 2002


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