function CreateAG($db, $ag_name, $ag_desc)
{
$sql = "INSERT INTO acid_ag (ag_name, ag_desc) VALUES ('" . $ag_name . "','" . $ag_desc . "');";
$db->baseExecute($sql, -1, -1, false);
if ($db->baseErrorMessage() != "") {
FatalError(gettext("Error Inserting new AG"));
}
$ag_id = $db->baseInsertID();
/* The following code is a kludge and can cause errors. Since it is not possible
* to determine the last insert ID of the AG, we requery the DB to ascertain the ID
* by matching on the ag_name and ag_desc. -- rdd (1/23/2001)
*
* Modified code to only run the kludge if the dbtype is postgres. Created a function
* to use the actual insertid function if available and return -1 if no -- srh (02/01/2001)
*
* Transaction support is neccessary to get this absolutely correct, because using
* an insert_id might break in a multi-user environment. -- rdd (02/07/2001)
*/
if ($ag_id == -1) {
$tmp_sql = "SELECT ag_id FROM acid_ag WHERE ag_name='" . $ag_name . "' AND " . "ag_desc='" . $ag_desc . "'";
if ($db->DB_type == "mssql") {
$tmp_sql = "SELECT ag_id FROM acid_ag WHERE ag_name='" . $ag_name . "' AND " . "ag_desc LIKE '" . MssqlKludgeValue($ag_desc) . "'";
}
$tmp_result = $db->baseExecute($tmp_sql);
$myrow = $tmp_result->baseFetchRow();
$ag_id = $myrow[0];
$tmp_result->baseFreeRows();
}
return $ag_id;
}
function Action_archive_alert_op($sid, $cid, &$db, $action_arg, &$ctx)
{
global $DBlib_path, $DBtype, $db_connect_method, $alert_dbname, $alert_host, $alert_port, $alert_user, $alert_password, $archive_dbname, $archive_host, $archive_port, $archive_user, $archive_password, $debug_mode;
$db2 =& $ctx;
$insert_sql = array();
$sql_cnt = 0;
$archive_cnt = 0;
$sql = "SELECT hostname, interface, filter, detail, encoding FROM sensor " . "WHERE sid={$sid}";
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
/* Run the same query on archive db, to check if sensor data already in */
$tmp_result_db2 = $db2->baseExecute($sql);
$tmp_row_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
/* Insert sensor data only if we got it from alerts db and it's not already in archive db */
if ($tmp_row && !$tmp_row_db2) {
$sql = "INSERT INTO sensor (sid,hostname,interface,filter,detail,encoding,last_cid) " . "VALUES ({$sid},'" . $tmp_row[0] . "','" . $tmp_row[1] . "','" . $tmp_row[2] . "','" . $tmp_row[3] . "','" . $tmp_row[4] . "','0')";
if ($db->DB_type == "mssql") {
$insert_sql[$sql_cnt++] = "SET IDENTITY_INSERT sensor ON";
}
$insert_sql[$sql_cnt++] = $sql;
if ($db->DB_type == "mssql") {
$insert_sql[$sql_cnt++] = "SET IDENTITY_INSERT sensor OFF";
}
}
/* If we have FLoP's event `reference` column - archive it too. */
if (in_array("reference", $db->DB->MetaColumnNames('event'))) {
$sql = "SELECT signature, timestamp, reference FROM event WHERE sid={$sid} AND cid={$cid}";
} else {
$sql = "SELECT signature, timestamp FROM event WHERE sid={$sid} AND cid={$cid}";
}
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$sig = $tmp_row[0];
$timestamp = $tmp_row[1];
/* baseFetchRow() may return an empty string rather than as array */
if ($tmp_row != NULL) {
/* Not everybody uses FLoP: */
if (array_key_exists(2, $tmp_row)) {
$reference = $tmp_row[2];
/* FLoP's event reference */
} else {
$reference = "";
}
} else {
$reference = "";
}
$tmp_result->baseFreeRows();
/* Run the same query on archive db, to check if event data already in */
$tmp_result_db2 = $db2->baseExecute($sql);
$tmp_row_event_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
$sig_name = "";
/* Insert event data only if we got it from alerts db and it's not already in archive db */
if ($db->baseGetDBversion() < 100 && !$tmp_row_event_db2) {
/* If we have FLoP's event `reference` column - archive it too. */
if ($reference != "") {
$sql = "INSERT INTO event (sid,cid,signature,timestamp,reference) VALUES ";
$sql .= "({$sid}, {$cid}, '" . $sig . "', '" . $timestamp . "', '" . $reference . "')";
} else {
$sql = "INSERT INTO event (sid,cid,signature,timestamp) VALUES ";
$sql .= "({$sid}, {$cid}, '" . $sig . "', '" . $timestamp . "')";
}
$insert_sql[$sql_cnt++] = $sql;
} else {
if ($sig != "") {
$sig_name = GetSignatureName($sig, $db);
if ($db->baseGetDBversion() >= 103) {
if ($db->baseGetDBversion() >= 107) {
$sql = "SELECT sig_class_id, sig_priority, sig_rev, sig_sid, sig_gid ";
} else {
$sql = "SELECT sig_class_id, sig_priority, sig_rev, sig_sid ";
}
$sql .= "FROM signature WHERE sig_id = '" . $sig . "'";
$result = $db->baseExecute($sql);
$row = $result->baseFetchRow();
$sig_class_id = $row[0];
$sig_class_name = GetSigClassName($sig_class_id, $db);
$sig_priority = $row[1];
$sig_rev = $row[2];
$sig_sid = $row[3];
if ($db->baseGetDBversion() >= 107) {
$sig_gid = $row[4];
}
}
$MAX_REF_CNT = 6;
$sig_reference = array($MAX_REF_CNT);
$sig_reference_cnt = 0;
$sql = "SELECT ref_id FROM sig_reference WHERE sig_id='" . $sig . "'";
$tmp_result = $db->baseExecute($sql);
while (($tmp_row = $tmp_result->baseFetchRow()) != "" && $sig_reference_cnt < $MAX_REF_CNT) {
$ref_id = $tmp_row[0];
$sql = "SELECT ref_system_id, ref_tag FROM reference " . "WHERE ref_id='" . $ref_id . "'";
$tmp_result2 = $db->baseExecute($sql);
$tmp_row2 = $tmp_result2->baseFetchRow();
$tmp_result2->baseFreeRows();
$sig_reference[$sig_reference_cnt++] = array($tmp_row2[0], $tmp_row2[1], GetRefSystemName($tmp_row2[0], $db));
}
$tmp_result->baseFreeRows();
if ($debug_mode > 1) {
echo "<PRE>";
print_r($sig_reference);
echo "</PRE>";
}
}
}
$sql = "SELECT ip_src,\n ip_dst,\n ip_ver, ip_hlen, ip_tos, ip_len, ip_id, ip_flags,\n ip_off, ip_ttl, ip_proto, ip_csum " . "FROM iphdr WHERE sid='{$sid}' AND cid='{$cid}'";
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
/* Run the same query on archive db, to check if iphdr data already in */
$tmp_result_db2 = $db2->baseExecute($sql);
$tmp_row_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
/* Insert iphdr data only if we got it from alerts db */
if ($tmp_row) {
$ip_proto = $tmp_row[10];
/* Insert iphdr data only if it's not already in archive db */
if (!$tmp_row_db2) {
$sql = "INSERT INTO iphdr (sid,cid,\n ip_src,\n ip_dst,\n ip_ver,ip_hlen,ip_tos,ip_len,ip_id,ip_flags,\n ip_off,ip_ttl,ip_proto,ip_csum) VALUES " . "({$sid}, {$cid}, '" . $tmp_row[0] . "', '" . $tmp_row[1] . "'," . "'" . $tmp_row[2] . "','" . $tmp_row[3] . "','" . $tmp_row[4] . "','" . $tmp_row[5] . "'," . "'" . $tmp_row[6] . "','" . $tmp_row[7] . "','" . $tmp_row[8] . "','" . $tmp_row[9] . "'," . "'" . $tmp_row[10] . "','" . $tmp_row[11] . "')";
$insert_sql[$sql_cnt++] = $sql;
}
} else {
$ip_proto = -1;
}
if ($ip_proto == 6) {
$sql = "SELECT tcp_sport, tcp_dport, tcp_seq, tcp_ack, tcp_off,\n tcp_res, tcp_flags, tcp_win, tcp_csum, tcp_urp " . "FROM tcphdr WHERE sid='{$sid}' AND cid='{$cid}'";
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
/* Run the same query on archive db, to check if tcphdr data already in */
$tmp_result_db2 = $db2->baseExecute($sql);
$tmp_row_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
/* Insert tcphdr data only if we got it from alerts db and it's not already in archive db */
if ($tmp_row && !$tmp_row_db2) {
$sql = "INSERT INTO tcphdr (sid,cid,\n tcp_sport, tcp_dport, tcp_seq,\n tcp_ack, tcp_off, tcp_res, tcp_flags,\n tcp_win, tcp_csum, tcp_urp) VALUES " . "({$sid}, {$cid}, '" . $tmp_row[0] . "', '" . $tmp_row[1] . "'," . "'" . $tmp_row[2] . "','" . $tmp_row[3] . "','" . $tmp_row[4] . "','" . $tmp_row[5] . "'," . "'" . $tmp_row[6] . "','" . $tmp_row[7] . "','" . $tmp_row[8] . "','" . $tmp_row[9] . "')";
$insert_sql[$sql_cnt++] = $sql;
}
} else {
if ($ip_proto == 17) {
$sql = "SELECT udp_sport, udp_dport, udp_len, udp_csum " . "FROM udphdr WHERE sid='{$sid}' AND cid='{$cid}'";
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
/* Run the same query on archive db, to check if udphdr data already in */
$tmp_result_db2 = $db2->baseExecute($sql);
$tmp_row_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
/* Insert udphdr data only if we got it from alerts db and it's not already in archive db */
if ($tmp_row && !$tmp_row_db2) {
$sql = "INSERT INTO udphdr (sid,cid, udp_sport, udp_dport, " . "udp_len, udp_csum) VALUES " . "({$sid}, {$cid}, '" . $tmp_row[0] . "', '" . $tmp_row[1] . "'," . "'" . $tmp_row[2] . "','" . $tmp_row[3] . "')";
$insert_sql[$sql_cnt++] = $sql;
}
} else {
if ($ip_proto == 1) {
$sql = "SELECT icmp_type, icmp_code, icmp_csum, icmp_id, icmp_seq " . "FROM icmphdr WHERE sid='{$sid}' AND cid='{$cid}'";
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
/* Run the same query on archive db, to check if icmphdr data already in */
$tmp_result_db2 = $db2->baseExecute($sql);
$tmp_row_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
/* Insert icmphdr data only if we got it from alerts db and it's not already in archive db */
if ($tmp_row && !$tmp_row_db2) {
$sql = "INSERT INTO icmphdr (sid,cid,icmp_type,icmp_code," . "icmp_csum,icmp_id,icmp_seq) VALUES " . "({$sid}, {$cid}, '" . $tmp_row[0] . "', '" . $tmp_row[1] . "'," . "'" . $tmp_row[2] . "','" . $tmp_row[3] . "','" . $tmp_row[4] . "')";
$insert_sql[$sql_cnt++] = $sql;
}
}
}
}
/* If we have FLoP extended db, archive `pcap_header` and `data_header` too. */
if (in_array("pcap_header", $db->DB->MetaColumnNames('data')) && in_array("data_header", $db->DB->MetaColumnNames('data'))) {
$sql = "SELECT data_payload, pcap_header, data_header FROM data WHERE sid='{$sid}' AND cid='{$cid}'";
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
$pcap_header = $tmp_row[1];
$data_header = $tmp_row[2];
} else {
$sql = "SELECT data_payload FROM data WHERE sid='{$sid}' AND cid='{$cid}'";
$tmp_result = $db->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
}
/* Run the same query on archive db, to check if data already in */
$tmp_result_db2 = $db2->baseExecute($sql);
$tmp_row_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
/* Insert data only if we got it from alerts db and it's not already in archive db */
if ($tmp_row && !$tmp_row_db2) {
/* If we have FLoP extended db `pcap_header` or `data_header` then archive it too. */
if ($pcap_header != "" || $data_header != "") {
$sql = "INSERT INTO data (sid,cid, data_payload, pcap_header, data_header) VALUES ";
$sql .= "({$sid}, {$cid}, '" . $tmp_row[0] . "', '" . $pcap_header . "', '" . $data_header . "')";
} else {
$sql = "INSERT INTO data (sid,cid, data_payload) VALUES ";
$sql .= "({$sid}, {$cid}, '" . $tmp_row[0] . "')";
}
$insert_sql[$sql_cnt++] = $sql;
}
$sql = "SELECT optid, opt_proto, opt_code, opt_len, opt_data " . "FROM opt WHERE sid='{$sid}' AND cid='{$cid}'";
$tmp_result = $db->baseExecute($sql);
while (($tmp_row = $tmp_result->baseFetchRow()) != "") {
$sql = "INSERT INTO opt (sid,cid,optid,opt_proto," . "opt_code,opt_len,opt_data) VALUES " . "({$sid}, {$cid}, '" . $tmp_row[0] . "', '" . $tmp_row[1] . "'," . "'" . $tmp_row[2] . "','" . $tmp_row[3] . "','" . $tmp_row[4] . "')";
$select_sql = "SELECT optid, opt_proto, opt_code, opt_len, opt_data " . "FROM opt WHERE sid='{$sid}' AND cid='{$cid}' AND optid='{$tmp_row['0']}'";
/* Run the select query on archive db, to check if data already in */
$tmp_result_db2 = $db2->baseExecute($select_sql);
$tmp_row_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
/* Insert data only if it's not already in archive db */
if (!$tmp_row_db2) {
$insert_sql[$sql_cnt++] = $sql;
}
$tmp_result->baseFreeRows();
}
$archive_cnt = 0;
/* If signatures are normalized (schema v100+), then it is
* impossible to merely copy the event table completely. Rather
* the signatures must be written to the archive DB, and their
* new ID must be written into the archived event table
*/
if ($db->baseGetDBversion() >= 100) {
/* Check whether this signature already exists in
* the archive DB. If so, get the ID, otherwise first
* write the signature into the archive DB, and then
* get the newly inserted ID
*/
$sig_id = GetSignatureID($sig_name, $db2);
if ($sig_id == "" && $sig_name != "") {
if ($db->baseGetDBversion() >= 103) {
if ($sig_class_id == "") {
$sig_class_id = 'NULL';
} else {
/* get the ID of the classification */
$tmp_sql = "SELECT sig_class_id FROM sig_class WHERE " . "sig_class_name = '" . $sig_class_name . "'";
$tmp_result = $db2->baseExecute($tmp_sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
if ($tmp_row == "") {
$sql = "INSERT INTO sig_class (sig_class_name) " . " VALUES ('" . $sig_class_name . "')";
$db2->baseExecute($sql);
$sig_class_id = $db2->baseInsertID();
/* Kludge query. Getting insert ID fails on postgres. */
if ($db->DB_type == "postgres") {
$sql = "SELECT last_value FROM sig_class_sig_class_id_seq";
$tmp_result = $db2->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
$sig_class_id = $tmp_row[0];
}
} else {
$sig_class_id = $tmp_row[0];
}
}
if ($sig_priority == "") {
$sig_priority = 'NULL';
}
if ($sig_rev == "") {
$sig_rev = 'NULL';
}
if ($sig_gid == "") {
$sig_gid = 'NULL';
}
if ($db->baseGetDBversion() >= 107) {
$sql = "INSERT INTO signature (sig_name, sig_class_id, sig_priority, sig_rev, sig_sid, sig_gid) " . "VALUES ('" . addslashes($sig_name) . "'," . $sig_class_id . ", " . $sig_priority . ", " . $sig_rev . ", " . $sig_sid . ", " . $sig_gid . ")";
} else {
$sql = "INSERT INTO signature (sig_name, sig_class_id, sig_priority, sig_rev, sig_sid) " . "VALUES ('" . addslashes($sig_name) . "'," . $sig_class_id . ", " . $sig_priority . ", " . $sig_rev . ", " . $sig_sid . ")";
}
} else {
$sql = "INSERT INTO signature (sig_name) VALUES ('" . $sig_name . "')";
}
$db2->baseExecute($sql);
$sig_id = $db2->baseInsertID();
/* Kludge query. Getting insert ID fails on postgres. */
if ($db->DB_type == "postgres") {
$sql = "SELECT last_value FROM signature_sig_id_seq";
$tmp_result = $db2->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
$sig_id = $tmp_row[0];
}
}
/* add reference information */
for ($j = 0; $j < $sig_reference_cnt; $j++) {
/* get the ID of the reference system */
$tmp_sql = "SELECT ref_system_id FROM reference_system WHERE " . "ref_system_name = '" . $sig_reference[$j][2] . "'";
$tmp_result = $db2->baseExecute($tmp_sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
if ($tmp_row == "") {
$sql = "INSERT INTO reference_system (ref_system_name) " . " VALUES ('" . $sig_reference[$j][2] . "')";
$db2->baseExecute($sql);
$ref_system_id = $db2->baseInsertID();
/* Kludge query. Getting insert ID fails on postgres. */
if ($db->DB_type == "postgres") {
$sql = "SELECT last_value FROM reference_system_ref_system_id_seq";
$tmp_result = $db2->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
$ref_system_id = $tmp_row[0];
}
} else {
$ref_system_id = $tmp_row[0];
}
$sql = "SELECT ref_id FROM reference WHERE " . "ref_system_id='" . $ref_system_id . "' AND " . "ref_tag='" . $sig_reference[$j][1] . "'";
if ($db->DB_type == "mssql") {
/* MSSQL doesn't allow "=" with TEXT data types, but it does
* allow LIKE. By escaping all the characters in the search
* string, we make LIKE work like =.
*/
$mssql_kludge_sig_tag = MssqlKludgeValue($sig_reference[$j][1]);
$sql = "SELECT ref_id FROM reference WHERE " . "ref_system_id='" . $ref_system_id . "' AND " . "ref_tag LIKE '" . $mssql_kludge_sig_tag . "'";
}
$tmp_result = $db2->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
if ($tmp_row != "") {
$ref_id = $tmp_row[0];
$tmp_result->baseFreeRows();
} else {
$sql = "INSERT INTO reference (ref_system_id, ref_tag) " . " VALUES (" . $sig_reference[$j][0] . ",'" . $sig_reference[$j][1] . "')";
$db2->baseExecute($sql);
$ref_id = $db2->baseInsertID();
/* Kludge query. Getting insert ID fails on postgres. */
if ($db->DB_type == "postgres") {
$sql = "SELECT last_value FROM reference_ref_id_seq";
$tmp_result = $db2->baseExecute($sql);
$tmp_row = $tmp_result->baseFetchRow();
$tmp_result->baseFreeRows();
$ref_id = $tmp_row[0];
}
}
if ($ref_id != "" && $ref_id > 0) {
$sql = "INSERT INTO sig_reference (sig_id, ref_seq, ref_id) " . "VALUES (" . $sig_id . "," . ($j + 1) . "," . $ref_id . ")";
$select_sql = "SELECT sig_id FROM sig_reference WHERE sig_id=" . $sig_id . " AND ref_seq=" . ($j + 1);
/* Run the select query on archive db, to check if data already in */
$tmp_result_db2 = $db2->baseExecute($select_sql);
$tmp_row_db2 = $tmp_result_db2->baseFetchRow();
$tmp_result_db2->baseFreeRows();
/* Insert data only if it's not already in archive db */
if (!$tmp_row_db2) {
$insert_sql[$sql_cnt++] = $sql;
}
}
}
/* Insert event data only if it's not already in archive db */
if (!$tmp_row_event_db2) {
/* If we have FLoP's event `reference` column - archive it too. */
if ($reference != "") {
$sql = "INSERT INTO event (sid,cid,signature,timestamp,reference) VALUES ";
$sql .= "({$sid}, {$cid}, '" . $sig_id . "', '" . $timestamp . "', '" . $reference . "')";
} else {
$sql = "INSERT INTO event (sid,cid,signature,timestamp) VALUES ";
$sql .= "({$sid}, {$cid}, '" . $sig_id . "', '" . $timestamp . "')";
}
$insert_sql[$sql_cnt++] = $sql;
}
}
if ($debug_mode > 1) {
echo "<PRE>";
print_r($insert_sql);
echo "</PRE>";
}
/* Write Alerts into archive database */
for ($j = 0; $j < count($insert_sql); $j++) {
$db2->baseExecute($insert_sql[$j], -1, -1, false);
if ($db2->baseErrorMessage() == "") {
++$archive_cnt;
} else {
if ($db2->DB_type == "mssql") {
// MSSQL must be reset in this case, or else the same error message
// will be returned for all subsequent INSERTS, even though they
// succeed.
$db2->baseConnect($archive_dbname, $archive_host, $archive_port, $archive_user, $archive_password);
}
/* When we get such an error, assume that this is ok */
if (strstr($insert_sql[$j], "SET IDENTITY_INSERT")) {
++$archive_cnt;
} else {
if ($debug_mode > 1) {
ErrorMessage(gettext("Archive error:") . $db2->baseErrorMessage() . "<BR>" . $insert_sql[$j]);
}
/* When detect a duplicate then stop */
break;
}
}
}
/* Check if all or any data was written to archive database,
* before purging the alert from the current database
*/
if ($archive_cnt == $sql_cnt && $sql_cnt > 0) {
$archive_cnt = 1;
/*
* Update alert cache for archived alert right after we copy it to archive db.
* This fixes issue when alert in archive db not cached if archived alert cid
* is lesser than other alerts cid already cached in archive db.
*/
CacheAlert($sid, $cid, $db2);
} else {
$archive_cnt = 0;
}
return $archive_cnt;
}
