Beefy Boxes and Bandwidth Generously Provided by pair Networks
Come for the quick hacks, stay for the epiphanies.
 
PerlMonks  

Adding the values of a Hash to a MySQL Database

by dru145 (Friar)
on Apr 15, 2002 at 21:35 UTC ( [id://159338]=perlquestion: print w/replies, xml ) Need Help??

dru145 has asked for the wisdom of the Perl Monks concerning the following question:

Monks,

I'm trying to add records to a MySQL database using DBI. The format that I'm using to add it to the db works when I print it to command line. Here is what it looks like:
IP = 192.168.30.136 Cname = WKBX0010B-A Port = 135 Service = DCE endpoint resolution Banner = IP = 192.168.30.136 Cname = WKBX0010B-A Port = 80 Service = World Wide Web HTTP Banner = HTTP/1.1 200 OK..Server: Microsoft-IIS/5.0..Date: Thu, 14 Mar + 2002 14:37:54 GMT..Connection: Keep-Alive..Content-Length: 1270.. IP = 192.168.30.136 Cname = WKBX0010B-A Port = 139 Service = NETBIOS Session Service Banner =
I'm trying to do the same thing with DBI, but I'm getting weird results. I would like the db to look like this
+--------------+----------------+----------+---------+--------+ | ip | svr_name | port | service | banner | +--------------+----------------+----------+---------+--------+ | 192.168.30.136 | WKBX0010B-A | 135 | DCE endpoint resolution + | NULL | | 192.168.30.136 | WKBX0010B-A | 80 | World Wide Web HTTP + | HTTP/1.1 200 OK..Server: Microsoft-IIS/5.0..Date: Thu, 14 Mar 20 +02 14:37:54 GMT..Connection: Keep-Alive..Content-Length: 1270.. | | 192.168.30.136 | WKBX001B-A | 139 | NETBIOS Session Service | NUL +L | +--------------+----------------+----------+---------+--------+
Here is my code that I thought would work (note most of the file matching code was written by danger):
#!/usr/bin/perl -w use DBI; use strict; my $infile = './portscan2.txt'; my %data; my ($ip,$port); open INFILE, "$infile" or die "Can't open $infile: $!\n"; while (<INFILE>){ if (/(\d+\.\d+\.\d+\.\d+)\s+(\S+)/) { $ip = $1; $data{$ip}{cname} = $2; next; } if(m/^\s{1}\|___\s+(\d+)\s\s(.*)/){ $port = $1; $data{$ip}{ports}{$port}{service} = $2; $data{$ip}{ports}{$port}{banner} = ''; next; } if(m/^\t\s{1}\|___\s+(.*)/){ $data{$ip}{ports}{$port}{banner} = $1; next; } } my ($sth,$i,$j); ### Enable error checking my %attr = ( PrintError => 0, RaiseError => 1, ); ### Connect to database my $dbh = DBI->connect("DBI:mysql:audit", 'username', 'pass', \%attr); for $i (keys %data) { for $j (keys %{$data{$i}{ports}}) { $i = $dbh->do("insert into Supersearch set ip='$i',svr_name='$data +{$i}{cname}',port='$j',service='$data{$i}{ports}{$j}{service}',banner +='$ data{$i}{ports}{$j}{banner}'"); } } ### Disconnect from db $dbh->disconnect; exit;
I appreciate any help

Thanks,
Dru
Another satisfied monk.

Replies are listed 'Best First'.
Re: Adding the values of a Hash to a MySQL Database
by VSarkiss (Monsignor) on Apr 15, 2002 at 22:09 UTC

    Well, you don't say what the problem is, but your SQL is incorrect, at least. Furthermore, you're setting your outer loop control variable, $i, to the result of your DBI call, which seems very odd. Try something like this (note, untested):

    $dbh->do( "insert into Supersearch (ip, svr_name, port, service, banner) values (?, ?, ?, ?, ?)", undef, $i, $data{$i}{cname}, $j, $data{$i}{ports}{$j}{service}, $data{$i}{ports}{$j}{banner}) or die $dbh->errstr;
    To explain: an insert statement takes a values clause, not a set clause. Also, the do statement in DBI allows you to use placeholders, which are much better for avoiding quotation problems.

Re: Adding the values of a Hash to a MySQL Database
by ehdonhon (Curate) on Apr 16, 2002 at 14:53 UTC

    What is the problem you are having? Try adding this after your $dbh->do() ...

    die( $dbh->errstr() ) unless ( $i );

    Let us know what you get. If it does not crash, try adjusting the trace level in DBI to 2.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://159338]
Approved by seattlejohn
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others avoiding work at the Monastery: (6)
As of 2024-03-29 05:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found