Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Unanswered: INSERT statement doesn't work

    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.
    Last edited by utdpauls; 04-19-06 at 17:00.

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    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";
    board.perl-community.de - The German Perl-Community

  3. #3
    Join Date
    Jul 2005
    Location
    UK, England
    Posts
    14
    Replace:
    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:
    my $insert_query = qq{INSERT INTO temp (id) $cid_query};
    With:
    Code:
    my $insert_query = qq{INSERT INTO temp (id) AS $cid_query};
    I'm thinking......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •