[Snort-devel] last_cid in new database scheme v106
Yuan John Jiang
yjj at ...1563...
Wed Sep 11 12:40:01 EDT 2002
I hope I'm not wasting too much bandwidth when asking such beginners'
1) I assume event.sid references the primary key sensor.sid. Does
*.cid reference anything? Is it a pure sequence number (in
conjunction with sid) for the event table?
2) The name sid confused me at first because the the unique ID for each
Snort rule is sid in Snort documents. Am I right that the latter is
Y. John Jiang, Clean Communications (formerly NetPlexus Corp.)
10201 Lee Highway, Ste 520, Fairfax City, VA 22030
703-788-2404 or 703-359-4403 x110, 703-622-1066(mobile),
Fax: 703-359-4263 or 801-749-9747
On Wed, 11 Sep 2002, 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