[Snort-devel] last_cid in new database scheme v106
Kreimendahl, Chad J
Chad.Kreimendahl at ...1167...
Fri Sep 13 09:33:02 EDT 2002
Gonna reply to both messages I can reply to:
I already have the new design... Will crank out a PDF of what it looks
like.... (only DDL I have is for Oracle right now). The structure has
stayed the same. I now use CID as the primary key (though, want to use
EID or Event_id)... Sid is a just used as a reference back to sensor
My plan isn't to kill spo_database and replace it, but more to provide
an alternative going forward. For those who don't want to change their
apps (Demarc... Etc.) they will still have the wonderful spo_database to
use (especially since they're MySQL). The changes to the new design
shouldn't require many changes to any code. Admitedly in our app, we'd
have to change some navigation to no longer include the to-be redudant
sid. We'd have to reflect that in the DB, and a few other places... But
in the long run, it's absolutely worth it for us... Primarily for
performance and backup reasons.
I'd like to enlist as many people as are willing (and able) to help with
this. (spo_database2? spo_rdbms?) Also been considering splitting out
all the DBs, and only keeping the core functionality in the main code...
Having spo_rdbms_oracle? Or some such thing.
A clearer definition of the oracle query size problem:
In a statement (INSERT in this example)... INSERT INTO data
(sid,cid,data) VALUES ('<value1>','<value2>',...) if any <valueX>
contains more than 4000 characters, the whole statement fails (and thus,
nothing is inserted) Of course, this also holds true if any value is
greater than the allowable size of the field... But we don't run into
that anywhere, yet (2G payload?). Since we're inserting HEX... We're
actually writing 2 chars for each byte (right?)... So in practice we end
up with the largest field containing 2000 bytes. Of course, Oracle is a
little strange in this regard. What I said only holds true in VARCHAR
and LONG. Somehow if you're using a BLOB, Oracle considers the hex data
to be binary, and thus allows 4000 bytes... (8000 characters)... This
seems to be the only way to violate it.
With placeholders, this is all resolved... The maximum amount of data
allowed to be inserted into a placeholder is 2GB for oracle (128MB for
postgres?). Can anyone disagree that simply truncating it is not the
answer. There's the the problem of not seeing the data. You'd still
likely miss where the inserted attack was in a buffer overflow attempt,
The fix for databases without sequences (MySQL), would be the use of
auto_increment... With the downfall being... Erasure of the data would
likely cause a restart of the number at 0 (or 1). So, what about the
creation of an additional table, which simply holds last_cid... On which
is incremented and returns the next number. This should resolve any
collision problems, and I think you can return the value to which it was
updated. Not quite as efficient as sequences, but probably acceptible.
I'm sure with all the minds we have here, we can find a work-around.
We can kinda skip the rest of what I was gonna say...
What Roman was saying in his last paragraph is exactly what I was trying
to get across... Having a centralize control (spo_rdmbs) that has any
global database functions within... Such as putting the data into
appropriate vars... And then calling the appropriate output database
modules to do their respective jobs. This would definatley allow for
slightly more headache, but I believe far more flexibility in the
database code. If the functionality were there.... And someone like
myself wanted to change the way say things were output to oracle... I
could roll my own spo_rdbms_some-database that took those values and did
my own thing with (DBM files?)... Would allow people to create their own
schemas and possibly find much better ways of doing things than any of
us could do.
From: Dirk Geschke [mailto:Dirk_Geschke at ...802...]
Sent: Thursday, September 12, 2002 5:11 AM
To: Kreimendahl, Chad J
Cc: Dirk Geschke; snort-devel at lists.sourceforge.net;
Dirk_Geschke at ...802...
Subject: Re: [Snort-devel] last_cid in new database scheme v106
yes that would be a good thing to do. But first of all we need
a new design of the database. Without this a new plugin does not
make too much sense (ok, it could be coded nicer and some of my
previous ideas of pre-inserting of the rules could be performed).
On the other hand, with a new design we have to ensure that all
the available tools like ACID (and I guess there are a lot of
similar tools out there) are running with this new desgin.
So if this concept should not end in a dead project we have
to involve a lot of people. Otherwise you will be the only
user and this is disappointing and discouraging.
What is the maximum query length on oracle? Maybe you should set
the constant in src/output-plugins/spo_database.c
#define MAX_QUERY_LENGTH 8192
to the right value?
From: Roman Danyliw [mailto:roman at ...49...]
Sent: Wednesday, September 11, 2002 2:17 PM
To: Kreimendahl, Chad J
Cc: Dirk Geschke; snort-devel at lists.sourceforge.net
Subject: RE: [Snort-devel] last_cid in new database scheme v106
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.
"Most databases" is the key phrase here. MySQL, by far the most popular
database logging configuration, does not support explicit sequences.
this reason, there is the addition sensor.cid field.
Furthermore, it would be the introduction of a sequence (which
keep state identical to what is achieved by the sensor.last_cid), not
elimination of the composite primary that would prevent the CID
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.
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.
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
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
it to the appropriate SQL and making the necessary API calls.
More information about the Snort-devel