Your code has a lot of regularity, but you're not exploiting that. You can reduce a lot of potential maintenance headaches by doing so. I'd rewrite it as follows.

use strict;
use DBI;

# Check arguments first
if (@ARGV != 2)
{
    die "Need exactly two args, got ", scalar(@ARGV), "\n";
}

# Set up all our SQL
my $time_sql =
    'select sid, cid from acid_event where timestamp between ? and ?';

my @tab_sql;
foreach (qw(event iphdr tcphdr udphdr icmphdr
        opt data acid_ag_alert acid_event))
{
    push @tab_sql =>
    "delete from $_ where "
    . ($_ eq 'acid_ag_alert'             # this one's different
        ? 'ag_sid = ? and ag_cid = ?'
        : 'sid = ?    and cid = ?');
}

# Connect to the database
my $db = DBI->connect(qw(dbi:mysql:snort acid_user secret))
    or die "Can't connect: $DBI::errstr\n";

# Get outer level data
my ($outer_sth, $inner_sth);
my ($sid, $cid);

eval {
    $outer_sth = $db->prepare($time_sql);
    $outer_sth->execute(@ARGV);
    $outer_sth->bind_columns(undef, \$sid, \$cid);
};

die $@ if $@;

# Loop for each row of data
while ($outer_sth->fetch())
{
    foreach (@tab_sql)
    {
    eval {
        $inner_sth = $db->prepare($_);
        $inner_sth->execute($sid, $cid);
        $inner_sth->finish();            # not really necessary
    };
    die $@ if $@;
    }
}

$outer_sth->finish();         # not really necessary
[download]

The whole trick, if you can call it that, is to notice that all your SQL statements vary in the table name, and only once in the column name. It's gravy after that.

Note, I've only tested this to a limited extent since I don't have snort.