Solved

CGI and DBI "cache' problem?

Posted on 2002-05-12
8
229 Views
Last Modified: 2010-03-05
I got the following CGI program which takes in one field 's_off_no' from a query from.

Problem is it only works one time and after that, even though I am submitting different queries values, it is still using the very first value to do the database queries.

any suggestions?


=============================================

do 'dbi-lib.pl';



use CGI ':standard';
use DBI;
use strict;
use vars qw($baseURL);

# Declare base URL for return from this page
$baseURL = "../index.html";

 my $q = new CGI;
 my $user = $q->param('user');
 my $passwd = $q->param('passwd');
 my $action = $q->param('action');
 my $s_off_no = $q->param('s_off_no');


printHead('TDPS - Delete Offence');

# check that user is logon
if (($user eq '') || ($passwd eq '')) {

  goLogon();
  return;
}


print EOM;
table border=0 cellpadding=0 cellspacing=0 width=100%>
tr>
td>
EOM

searchform();
queryresult($s_off_no);


print EOM;
hr>
EOM

print EOM;
/td>
/tr>
/table>
EOM

printclose();



sub searchform {
   
print EOM;

form action="query.pl" method="post">
input type="hidden" name="user" value="$user">
input type="hidden" name="passwd" value="$passwd">
table border="0" cellpadding="2" cellspacing="2">
tr>
td colspan="2" class="pagetitle">Delete Offence Details/td>
/tr>
tr>
td>Offence Number/td>
td>input type="text" name="s_off_no" size="5" maxlength="5"> input type="submit" value="Search">/td>
/tr>
/table>
/form>

EOM


}



sub queryresult {

#my $s_off_no = $s_off_no;
     my ($s_off_no) = @_;

     
     
    my $debug = 1; # 0 to off, 1 to turn it on        
    my $results;

    my $dbuser = '111';
    my $dbpasswd = '222';
   

       
    # Query to check if driver exist
    my $query1 = "select veh_reg_no from offence " .
                 "where off_no = $s_off_no";    

    # Database DBI driver name
    my $dbname = "DBI:Oracle:";

    # Create database handle
    my $dbh = DBI->connect($dbname, $dbuser, $dbpasswd);
    if (!$dbh) {
       showSQLerror("Connecting to the database");
       return;
    } else {
      # set AutoCommit  1 = ON, 0 = OFF (1 is default)
      # transaction control is *required* here, hence set OFF
      $dbh->{AutoCommit} = 0;
    }

    if ($debug == 1) {
      print("
query1: $query1");    
    }
    $results = run_statement($dbh,$query1,"Y");
    if ($results == 0) {
        printErrors('Invalid Offence No.!');
        return;
    } elsif ($results == -1) {
        $dbh->rollback;
        $dbh->disconnect;
        return;
    } else {
   
      $dbh->commit;
      $dbh->disconnect;
      print("
$results");
    }
}


sub run_statement {

    my ($dbh,$sql,$getNo)=@_;
    my $new_offNo;

    # Create a statement handle - prepare the statement
    my $sth = $dbh->prepare($sql);
    if (!$sth) {
        showSQLerror("Preparing SQL statement");
        return (-1);
    }

    # Execute the statement against the database
    $sth->execute;
    if ($DBI::errstr) {
        showSQLerror("Executing SQL Statment");
        $sth->finish;
        return (-1);
    }

    if ($getNo eq "Y") {
        ($new_offNo) = $sth->fetchrow;
    } else {
        $new_offNo = 0;
    }
    $sth->finish;

    return ($new_offNo);
}
=============
0
Comment
Question by:slok
  • 4
  • 4
8 Comments
 
LVL 10

Expert Comment

by:oleber
ID: 7008575


sub searchform {
 
my $time=time();  
print EOM;

form action="query.pl" method="post">
input type="hidden" name="user" value="$user">
input type="hidden" name="passwd" value="$passwd">

input type="hidden" name="stupid_value" value="$time">

table border="0" cellpadding="2" cellspacing="2">
tr>
td colspan="2" class="pagetitle">Delete Offence Details/td>
/tr>
tr>
td>Offence Number/td>
td>input type="text" name="s_off_no" size="5" maxlength="5"> input type="submit" value="Search">/td>
/tr>
/table>
/form>

EOM


}
0
 
LVL 3

Author Comment

by:slok
ID: 7010422
well, I print out the "stupid_value" everytime I submit the query from the form. And the time is different.

But my database query is still using the first "query" parameter.

0
 
LVL 10

Expert Comment

by:oleber
ID: 7010546
Probably I cann't help you. I see the code and i didn't understand the why of your problem.

Did you try the GET method in the form?


0
 
LVL 3

Author Comment

by:slok
ID: 7010745
I manage to solve the problem. though I don't know why.

By moving codes like
==
my $q = new CGI;
my $user = $q->param('user');
my $passwd = $q->param('passwd');
my $action = $q->param('action');
my $s_off_no = $q->param('s_off_no');
==

and any other validation code into subroutine, it solves the problem with 'caching'. Not sure why if these codes exist in the "main", it will have a 'caching' effect.

I will delete this question in a week's time.

Thanks for the help.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 10

Expert Comment

by:oleber
ID: 7010837
what is your server? are you using mod-perl, mason, ... or something like that?
0
 
LVL 3

Author Comment

by:slok
ID: 7010890
I'm running Apache 1.3.19 with mod_perl on win2k.
Perl used is ActivePerl.
0
 
LVL 10

Accepted Solution

by:
oleber earned 100 total points
ID: 7010926
try the original code but set a '{' at the beginning of your code and a '}' at the end of your code.

I had that some problem when I was working with mod-perl.

Sorry I forget that problem.


Take care with the values that come from the form. You can have serios problems.

Think in having the s_off_no = "1; delete offence"
cann't this do bad things to your database?


use CGI ':standard';
use DBI;
use strict;
use vars qw($baseURL);

{ # <<----
# Declare base URL for return from this page
$baseURL = "../index.html";

my $q = new CGI;
my $user = $q->param('user');
my $passwd = $q->param('passwd');
my $action = $q->param('action');
my $s_off_no = $q->param('s_off_no');


printHead('TDPS - Delete Offence');

# check that user is logon
if (($user eq '') || ($passwd eq '')) {

 goLogon();
 return;
}


print EOM;
table border=0 cellpadding=0 cellspacing=0 width=100%>
tr>
td>
EOM

searchform();
queryresult($s_off_no);


print EOM;
hr>
EOM

print EOM;
/td>
/tr>
/table>
EOM

printclose();

}  #<<------

sub searchform {
   
print EOM;

form action="query.pl" method="post">
input type="hidden" name="user" value="$user">
input type="hidden" name="passwd" value="$passwd">
table border="0" cellpadding="2" cellspacing="2">
tr>
td colspan="2" class="pagetitle">Delete Offence Details/td>
/tr>
tr>
td>Offence Number/td>
td>input type="text" name="s_off_no" size="5" maxlength="5"> input type="submit" value="Search">/td>
/tr>
/table>
/form>

EOM


}



sub queryresult {

#my $s_off_no = $s_off_no;
    my ($s_off_no) = @_;

   
   
   my $debug = 1; # 0 to off, 1 to turn it on        
   my $results;

   my $dbuser = '111';
   my $dbpasswd = '222';
   

       
   # Query to check if driver exist
   my $query1 = "select veh_reg_no from offence " .
                "where off_no = $s_off_no";    

   # Database DBI driver name
   my $dbname = "DBI:Oracle:";

   # Create database handle
   my $dbh = DBI->connect($dbname, $dbuser, $dbpasswd);
   if (!$dbh) {
      showSQLerror("Connecting to the database");
      return;
   } else {
     # set AutoCommit  1 = ON, 0 = OFF (1 is default)
     # transaction control is *required* here, hence set OFF
     $dbh->{AutoCommit} = 0;
   }

   if ($debug == 1) {
     print("
query1: $query1");    
   }
   $results = run_statement($dbh,$query1,"Y");
   if ($results == 0) {
       printErrors('Invalid Offence No.!');
       return;
   } elsif ($results == -1) {
       $dbh->rollback;
       $dbh->disconnect;
       return;
   } else {
   
     $dbh->commit;
     $dbh->disconnect;
     print("
$results");
   }
}


sub run_statement {

   my ($dbh,$sql,$getNo)=@_;
   my $new_offNo;

   # Create a statement handle - prepare the statement
   my $sth = $dbh->prepare($sql);
   if (!$sth) {
       showSQLerror("Preparing SQL statement");
       return (-1);
   }

   # Execute the statement against the database
   $sth->execute;
   if ($DBI::errstr) {
       showSQLerror("Executing SQL Statment");
       $sth->finish;
       return (-1);
   }

   if ($getNo eq "Y") {
       ($new_offNo) = $sth->fetchrow;
   } else {
       $new_offNo = 0;
   }
   $sth->finish;

   return ($new_offNo);
}
0
 
LVL 3

Author Comment

by:slok
ID: 7011054
I probably would not go and test your sample codes.
But the proposed solution makes sense.

Will accept it as others might find it useful.

Thanks a lot for all the help. Definitely a learning experience.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now