[Snort-devel] last_cid in new database scheme v106
mca at ...498...
Wed Sep 11 12:28:07 EDT 2002
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.
Roman Danyliw wrote:
> > 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
> > 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
> 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.
> In remembrance
> Snort-devel mailing list
> Snort-devel at lists.sourceforge.net
More information about the Snort-devel