INSERT statement doesn't work

utdpauls · #1 (**permalink**) 04-19-06, 15:25

I'm working on a script to delete alerts from a snort db based on the IP address. I had a working script, but it was taking too long to delete the data, so I decided to play around with some different ideas to see if I could speed it up. (OS is FreeBSD, db is mysql 4.1.18, perl is version 5.8.8. Both are built from ports.)

I'm not having any problem connecting to the db or doing simple selects or deletes, but the insert query I'm trying to construct doesn't do a thing. The account I'm using has ALL privileges on *.*.

What's more, the script seems to exit right then, without throwing any errors that I can see.

Here's a code snippet:

Code:

my $cidh;                                                       # the handle for cid queries
my $cid_query = qq{SELECT cid FROM iphdr WHERE inet_ntoa(ip_src) = '$ip' or inet_ntoa(ip_dst) = '$ip'};
my $insert_query = qq{INSERT INTO temp (id) $cid_query};
print "My insert query is $insert_query\n";
$cidh = $dbh_snort->prepare($insert_query);
$dbh_snort->execute();
print "Testing - did we make it here?\n";

This is the cli command and output from the script:
perl delete_alerts.pl -c delete_alerts.config -i 67.187.8.45
My insert query is INSERT INTO temp (id) SELECT cid FROM iphdr WHERE inet_ntoa(ip_src) = '67.187.8.45' or inet_ntoa(ip_dst) = '67.187.8.45'

As you can see, the second print statement never appears, so the script is exiting as soon as the query is executed. When I check the db, the query obviously didn't complete successfully:
select * from temp;
Empty set (0.01 sec)

And here's a trace of the script:

Code:

    DBI 1.50-nothread default trace level set to 0x0/3 (pid 16995)
    Note: perl is running without the recommended perl -w option
My insert query is INSERT INTO temp (id) SELECT cid FROM iphdr WHERE inet_ntoa(ip_src) = '67.187.8.45' or inet_ntoa(ip_dst) = '67.187.8.45'
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x81f613c)~0x81dd2ec 'INSERT INTO temp (id) SELECT cid FROM iphdr WHERE inet_ntoa(ip_src) = '67.187.8.45' or inet_ntoa(ip_dst) = '67.187.8.45'')
    New DBI::st (for DBD::mysql::st, parent=DBI::db=HASH(0x81dd2ec), id=)
    dbih_setup_handle(DBI::st=HASH(0x81dd49c)=>DBI::st=HASH(0x81dd40c), DBD::mysql::st, 81dd4a8, Null!)
    dbih_make_com(DBI::db=HASH(0x81dd2ec), 806b004, DBD::mysql::st, 256, 0) thr#0
dbd_st_prepare calling count_params (counting params emulation)
    <- prepare= DBI::st=HASH(0x81dd49c) at delete_alerts.pl line 130 via delete_alerts.pl line 114
    <> DESTROY(DBI::st=HASH(0x81dd49c)) ignored for outer handle (inner DBI::st=HASH(0x81dd40c) has ref cnt 1)
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x81dd40c)~INNER)
    <- DESTROY= undef at delete_alerts.pl line 114
    -> disconnect for DBD::mysql::db (DBI::db=HASH(0x81f613c)~0x81dd2ec)

Have I missed something simple? I can run the query inside of mysql and it works fine, so I know the query works. But perhaps it doesn't work inside of perl's DBI?

Line 114 is an eval statement (eval { ) - the entire script (after connecting to the db until just before disconnecting from the db) is wrapped in an eval to catch any errors.

reneeb · #2 (**permalink**) 04-24-06, 03:21

You have to "execute" $cidh! And I think you have to use the "VALUES"-Keyword in the statement, you should also use the ? in the statements due to security reasons (see perldoc DBI):

Code:

my $cid_query = qq{SELECT cid FROM iphdr WHERE inet_ntoa(ip_src) = '$ip' or inet_ntoa(ip_dst) = '$ip'};

my $insert_query = qq{INSERT INTO temp (id) VALUES(?)};
print "My insert query is $insert_query\n";
my $cidh = $dbh_snort->prepare($insert_query);
$cidh->execute($cid_query); # here was a mistake!
print "Testing - did we make it here?\n";

sqlbuddy · #3 (**permalink**) 05-18-06, 00:26

Replace:

Quote:

my $cid_query = qq{SELECT cid FROM iphdr WHERE inet_ntoa(ip_src) = '$ip' or inet_ntoa(ip_dst) = '$ip'};

with:

Code:

my $cid_query = qq{SELECT cid FROM iphdr WHERE inet_ntoa(ip_src) = $ip or inet_ntoa(ip_dst) = $ip};

Replace:

Quote:

my $insert_query = qq{INSERT INTO temp (id) $cid_query};

With:

Code:

my $insert_query = qq{INSERT INTO temp (id) AS $cid_query};

Internet Services

Web Development

Digital Marketing

Consumer Tech