• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Problem querying Database from Perl

Hi,

Can anyone please help me with the code for below issue:

After reading a log file how to compare the data from the file with the Oracle Database . If data is found ok, orlese send an email as below format:


Found error:

      log: 17
      DB: 16
      list of Id's not found  
            id1
               id2...

[b][u]Sample Log file I am reading in[/b][/u] :
IDNUM0040/101515780
IDNUM0128/101290758
IDNUM0132/2544021W
IDNUM0132/2544025W
IDNUM0205/101198986
IDNUM0205/101199893

Open in new window


[b][u]Current Script I am able to get:[/b][/u]
#!/local/bin/perl
use strict;
use warnings;
use DBI;



my $dir = '/usr/home/Scripts/Test';


# get the text files from the specified dir
opendir DIR, $dir or die "could not open directory $dir: $!";
my @files = grep /\.txt$/, readdir DIR;
closedir DIR;


my $dbh = DBI->connect("dbi:Oracle:****", "****", "*****" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;

foreach my $fil (@files) {

# read the ids from the files

    open IN, "$dir/$fil" or die "could not read $fil: $!";

   #my @ids = map { chomp; "'$_'" } <IN>;

    my @ids = map { "'$_'" } map { chomp; split /\s+/ } <IN>;

 
print "checking ", scalar(@ids), " ids from $fil\n";
print"@ids\n\n";
    

my $sel=$db->prepare('select (number||'/'||id) as List_ID from Info where List_ID in (' . join(', ', @ids) . ')');
   $sel->execute();
while(my $subref = $SEL->fetchrow_hashref()) {
    my $list=$subref->{'List_ID'};
print "$list\n";

}

Open in new window


The  above query has problem too. When I run the script it is erroring near query as "Illegal division by zero"
0
new_perl_user
Asked:
new_perl_user
  • 7
  • 6
1 Solution
 
Carl BohmanCommented:
The problem looks like your quoting on line 40.  Note that your string is quoted with single quotes.  You are also trying to use single quotes inside the string to send to the database.  This doesn't work.  You need to either escape the internal single quotes or change the outer quotes to double quotes.
0
 
new_perl_userAuthor Commented:
Hi,

As per suggestion tried something like this but did not work

my $sel=$db->prepare("select (number||'/'||id) as List_ID from Info where List_ID in (' . join(', ', @ids) . ')");
0
 
Carl BohmanCommented:
Here's the correct way to fix it:

my $sel=$db->prepare("select (number||'/'||id) as List_ID from Info where List_ID in (" . join(', ', @ids) . ')'); 

Open in new window


Note that you are concatenating (using periods) three different strings inside the prepare function call.  You need to treat each one separately.  You can't change the beginning quote of one and the ending quote of another and expect it to work correctly.

The three strings are:
"select (number||'/'||id) as List_ID from Info where List_ID in ("
join(', ', @ids)
')'

Quotes wiithin quotes is one of the easiest ways to introduce bugs if you aren't careful.  However, it is a very common thing to need to do, so it's important to understand how to do it.

It's also worth noting that your code is susceptible to SQL injection attacks.  All someone would need to do is add a line to one of the files in the directory that you are referencing and they could do things you don't want them to do.  Some examples (which may not be 100% correct syntax, but it wouldn't be hard to figure out the correct syntax):

0) or (1=1) -- Select all rows, not just a subset.
0); drop table info; -- There goes your data.

You need to validate your ids before you use them.  If you are expecting them to be numeric, make sure they are before blindly concatenating them into a SQL statement.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
new_perl_userAuthor Commented:
Hi,

Thank you for the suggestion. I tried the above fix and it worked I mean the query is not erroring out but
when I send the result into a variable and trying to print it out it is  not able to..

while(my $subref = $sel->fetchrow_hashref()) {
    my $list=$subref->{'List_ID'};
print "$list\n";

}
0
 
Carl BohmanCommented:
If you are getting data back, that syntax looks like it should work.  Try adding a debug statment immediately after line 42.  Something like this would work:
warn $subref;

Open in new window


If that line doesn't print anything out, then you aren't getting any results.  If it does print out, then maybe $list is empty.
0
 
new_perl_userAuthor Commented:
Yes it is printing as  below for 37 times because I have 37 Id's

HASH(0x1d1b0e10) at ./Check.pl line 43, <IN> line 42.
0
 
Carl BohmanCommented:
Change that debug line to this:
use Data::Dumper; warn Dumper($subref);

Open in new window

Do you see the data you are looking for?
0
 
new_perl_userAuthor Commented:
Hi,

After adding the above line result is as follows:

$VAR1 = {
          'LIST_ID' => 'IDNUM0128/101290758'
        };

$VAR1 = {
          'LIST_ID' => 'IDNUM0132/2544025W'
        };
...  for 37 times.


0
 
Carl BohmanCommented:
Notice that the key (LIST_ID) is all in upper case.  Keys are case-sensitive.  Change line 43 to:
my $list=$subref->{'LIST_ID'};

Open in new window

0
 
new_perl_userAuthor Commented:
wow. Thanks a ton it is printing now.

If possible can you please help me if the remaining:

If the ID's match then fine else send an email with the body as:

Found error:

      log: 17
      DB: 16
      list of Id's not found  
            id1
               id2..
0
 
Carl BohmanCommented:
There are a lot of different ways to send e-mail in Perl.  However, each approach depends heavily on your operating system, system configuration, mail server configuration, etc.

Sending e-mail is an entirely new topic, unrelated to the current question and should be opened as a new question.
0
 
new_perl_userAuthor Commented:
Sure. Thanks.
0
 
new_perl_userAuthor Commented:
opened up a new question. can you pls look into it.

I am accepting this one .

Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now