mod_perl2 newbie DBI question

[Brian Gaber <Br...@PWGSC.GC.CA>]

I have a MySQL database application that is used and managed by cgi-bin
scripts (CGI.pm).  In development the performance was fine, but a
productin trial showed the performance to be unacceptable.  I am
attempting to fix the performance by using mod_perl2 which I have never
used.  I have successfully compiled and install mod_perl2 and have added
these entries into httpd.conf:

PerlModule Apache::DBI

PerlModule ModPerl::Registry
Alias /perl/ /usr/local/apache2/perl/
<Location /perl/>
    SetHandler perl-script
    PerlResponseHandler ModPerl::Registry
    PerlOptions +ParseHeaders
    Options +ExecCGI
    Order allow,deny
    Allow from all
</Location>

PerlModule ModPerl::PerlRun
Alias /perl-run/ /usr/local/apache2/perl-run/
<Location /perl-run/>
    SetHandler perl-script
    PerlResponseHandler ModPerl::PerlRun
    PerlOptions +ParseHeaders +GlobalRequest
    Options +ExecCGI
    Order allow,deny
    Allow from all
</Location>

	I have modified scripts to work in mod_perl2, but they don't
work reliably.  Sometimes they work and then they stop working and then
I have to stop and start Apache to get it working again.  I am hoping if
I supply one of my scripts someone can advise me what needs to be done
to make in work fast and reliably and then I can use this as an example
to fix my other scripts.  Here is the one script:

#!/usr/bin/perl -w

#use CGI qw/:standard :html3 :netscape/;
use CGI '-autoload';
use DBI();
use warnings;
use strict;

my $region = param('region');

my $JSCRIPT=<<JSEND;
   function changeTitle()
   {
      parent.document.title=document.title;
   }

   function validate(theForm)
   {
      theForm.submit();
   }
JSEND

my $LOCAL_STYLE=<<CSSEND;

   body {
      font-family:Verdana;
      font-size:12px;
   }

   .btn {
      font-family:Verdana;
      font-size:9px;
      color:black;
      border:1px solid #000000;
      margin-top:5px;
      background-color:white
   }

   table {
      font-family:Verdana;
      border:1px solid #000000;
      background-color:white
   }

   th {
      font-family:Verdana;
      font-size:12px;
      color:black;
   }

   td.right {
      font-family:Verdana;
      font-size:12px;
      color:black;
      text-align:right;
   }

   td {
      font-family:Verdana;
      font-size:12px;
      color:black;
      text-align:center;
   }

CSSEND

print header( -type => "text/html" );
print start_html( -title => "Title", -style=>{-code=>$LOCAL_STYLE},
-onLoad=>"changeTitle()", -script=>$JSCRIPT ),
      br({ -clear => 'all' }),
      "\n";

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=esnap;host=localhost",
                      "athena", "godess",
                      {'RaiseError' => 1});

# Determine MySQL locks table name
my $sth = $dbh->prepare("SELECT * FROM region_props WHERE region =
'$region'");
$sth->execute();
my $ref = $sth->fetchrow_hashref();
$sth->finish();
my $locks_table = $ref->{locks_table};

my @form_vars = param();

if ( @form_vars > 1 ) { # if required parameters were passed
   rm_lock();
}

print_form();           # Display the MySQL table

# Disconnect from the database.
$dbh->disconnect();

print end_html();

sub print_form {
   my $i = 0;
   my @clmnNames = ();
   my @rows = ();

   my $sth = $dbh->prepare("SELECT * FROM $locks_table");
   $sth->execute();

   while (my $ref = $sth->fetchrow_hashref()) {
      push(@rows, td({-class=>'centre'},checkbox(-name=>"ckbx_$i",
-value=>"$ref->{id}", -label=>'')).
                  td({-class=>'centre'},$ref->{id}).
                  td({-class=>'centre'},$ref->{rcd_opener}).
                  td({-class=>'centre'},$ref->{lock_date})
      );
      $i++;
   }
   $sth->finish();

   # Specified values for table column heading names
   $clmnNames[0] = "Select";
   $clmnNames[1] = "Id";
   $clmnNames[2] = "User Id";
   $clmnNames[3] = "Date";

   print start_form(),
      font({-face=>"Trebuchet MS, Arial", -size=>2},br(),
      center(strong("Delete Record Locks"),br(),br(),
      table({-class=>'bdr', -width=>'100%', -BgColor=>"white",
-border=>'0'},
      Tr([th(\@clmnNames)]),"\n",
      Tr([@rows])),"\n",
      button(-class=>"btn", -value=>"Delete selected record locks",
-onClick=>"validate(this.form)"),
      ));
      print hidden(-name=>'region', -value=>param('region')),"\n";
   print end_form();
}

sub rm_lock() {

   foreach ( param() ) {
      if ($_ =~ /^ckbx_\d+$/) {
         my $id2del = param($_);

         # Delete row from $locks_table
         $dbh->do("DELETE FROM $locks_table WHERE id='$id2del'");

         my $errno = $dbh->{mysql_errno};
         my $errTxt = $dbh->{mysql_error};
         if ( $errno > 0 ) {
            print center(font({-face=>"Trebuchet MS, Arial", -size=>2,
-color=>"red"}),b("Error deleting row from $locks_table,
",font({-color=>"black"},"MySQL Error Code: $errno -
$errTxt"))),"\n",p();
         }
      }
   }
}

Re: mod_perl2 newbie DBI question

[David Kaufman <da...@gigawatt.com>]

Hi Brian,

"Brian Gaber" <Br...@PWGSC.GC.CA> wrote...


my $region = param('region'); # ...

my $sth = $dbh->prepare(
 "SELECT * FROM region_props WHERE region = '$region'"
);


Works fine a few times and then:

DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '' at line 1 at
/usr/local/apache2/perl-run/regDelLocks.pl line 191.\n


As Michael pointed out, the problem is caused by inserting uncheched user 
input into your SQL query.  If the CGI paramter $region, which you've 
surrounded in single-quotes happens to *contain* a single-quote... boom! A 
SQL syntax error exactly as you report results, becuase MySQL sees this:

  SELECT * FROM region_props WHERE region = 'Joe's Region'

See the problem?  Newlines, carriage returns and other control characters 
can sometimes surprise you too (not to mention the intentional 
sql-injection attacks michael points out).  By using SQL placeholders, DBI 
will helpfully escape any and all problematic characters in $region, 
ensuring that it is at least safe (even if not entirely valid or correct 
correct) before passing it to the db, so the MySQL server gets:

  SELECT * FROM region_props WHERE region = 'Joe\'s Region'

hth,

-dave

Re: mod_perl2 newbie DBI question

[Dodger <el...@gmail.com>]

2008/6/12 Michael Peters <mp...@plusthree.com>:
> Brian Gaber wrote:

>> # Determine MySQL locks table name
>> my $sth = $dbh->prepare("SELECT * FROM region_props WHERE region =
>> '$region'");
>> $sth->execute();

> Btw, this is *really* bad security wise. $region is coming straight from the
> browser. You're setting yourself up for an SQL Injection attack. Imagine I
> request some URL like:

>  regDelLocks.pl?region= %27blah%27%3B+DROP+ALL+DATABASES

> Guess what will happen? Preventing this is really easy. Just use SQL bind params.

> my $sth = $dbh->prepare("SELECT * FROM region_props WHERE region = ?");
> $sth->execute($region);

Hear hear!

It can be true that sometimes you can't (or have good reason to not
want to) use placeholders.

For instance,it's feasible to build a totally dynamic query by pushing
whereclauses into an array and values into another array.

It's also feasible to build a variable length IN() list against some
array like, for instance,  "WHERE colname IN(@{[(join ', ', '?' x
scalar @yourarray]}" into the statement (or using a sprintf much the
same way), it often doesn't read well.

In those cases it's understandable that you'd want to build your query
dynamically, but there's no excuse for unsafeness!

Examples:

Placeholders:
my @things_to_check_for = qw(foo bar baz luhrman);
my $q = <<"EOF";
SELECT thingy
   FROM doohickeys
 WHERE type IN(@{[join ', ', ('?') x scalar @things_to_check_for]})
EOF
my $st = $dbh->prepare($q);
$st->execute(@things_to_check_for);

# if the @{[]} boggles you
my @things_to_check_for = qw(foo bar baz luhrman);
my $q = sprintf <<"EOF", join ', ', ('?') x scalar @things_to_check_for;
SELECT thingy
   FROM doohickeys
 WHERE type IN(%s)
EOF
my $st = $dbh->prepare($q);
$st->execute(@things_to_check_for);

# More explictly:
my @things_to_check_for = qw(foo bar baz luhrman);
my $things_to_check_for = join ', ', ('?') x scalar @things_to_check_for;
my $q = sprintf <<"EOF";
SELECT thingy
   FROM doohickeys
 WHERE type IN($things_to_check_for)
EOF
my $st = $dbh->prepare($q);
$st->execute(@things_to_check_for);

This is a rather contrived example using a very simple query however.
Getting more complex than that, or if you're pandering to PHP
programmers (who are used to their crap database interfaces that only
let you use bind variables on input if you intend to use them on
output too, for no apparent reason), you have potential reasons to
directly create your statement (also, it makes it easier to spit out a
runnable query for debugging, because you can just print the very
statement out*).

So use DBI's quote() method:

Safe without placeholders:

my @things_to_check_for = qw(foo bar baz luhrman);
my $q = <<"EOF";
SELECT thingy
   FROM doohickeys
 WHERE type IN(@{[join ', ', map $dbh->quote($_), @things_to_check_for]})
EOF

(if you are using placeholders everywhere, quote can still be useful
for debugging, assuming you are ONLY using placeholders or at the very
least not writing any statements with a literal question mark in
them). You can take your statement with placeholders and do this:

my $rows = $st->execute($value1, $value2, $value3);

unless ($rows) {
    my $show_query = $q;
    $show_query =~ s/\?/\%s/g;
    printf <<"EOF", $st->errstr, map $dbh->quote($_), $value1, $value2, $value3;
<div id="oops">
  <div id="oopsException">SQL Statement failed: %s</div>
  <div id="statementDump"><pre>$show_query</pre></div>
</div>
EOF
}

-- 
Dodger

RE: mod_perl2 newbie DBI question

[Brian Gaber <Br...@PWGSC.GC.CA>]

Michael,

	Thank you very much for this valuable advice.

	Cheers.

Brian 

-----Original Message-----
From: Michael Peters [mailto:mpeters@plusthree.com] 
Sent: Thursday, June 12, 2008 3:05 PM
To: Brian Gaber
Cc: Jim Brandt; modperl@perl.apache.org
Subject: Re: mod_perl2 newbie DBI question

Brian Gaber wrote:

> Is there anything useful in the Apache error log?  Works fine a few 
> times and then DBD::mysql::st execute failed: You have an error in 
> your SQL syntax; check the manual that corresponds to your MySQL 
> server version for the right syntax to use near '' at line 1 at 
> /usr/local/apache2/perl-run/regDelLocks.pl line 191.\n

What does the SQL statement you are executing look like when it
encounters this problem? It's possible that you aren't getting what you
think you should in param('region').

quoting your earlier email:
> my $dbh = DBI->connect("DBI:mysql:database=esnap;host=localhost",
>                       "athena", "godess",
>                       {'RaiseError' => 1});
>
> # Determine MySQL locks table name
> my $sth = $dbh->prepare("SELECT * FROM region_props WHERE region = 
> '$region'"); $sth->execute();

Btw, this is *really* bad security wise. $region is coming straight from
the browser. You're setting yourself up for an SQL Injection attack.
Imagine I request some URL like:

  regDelLocks.pl?region= %27blah%27%3B+DROP+ALL+DATABASES

Guess what will happen? Preventing this is really easy. Just use SQL
bind params.

my $sth = $dbh->prepare("SELECT * FROM region_props WHERE region = ?");
$sth->execute($region);

--
Michael Peters
Plus Three, LP

Re: mod_perl2 newbie DBI question

[Michael Peters <mp...@plusthree.com>]

Brian Gaber wrote:

> Is there anything useful in the Apache error log?  Works fine a few
> times and then
> DBD::mysql::st execute failed: You have an error in your SQL syntax;
> check the manual that corresponds to your MySQL server version for the
> right syntax to use near '' at line 1 at
> /usr/local/apache2/perl-run/regDelLocks.pl line 191.\n

What does the SQL statement you are executing look like when it encounters this
problem? It's possible that you aren't getting what you think you should in
param('region').

quoting your earlier email:
> my $dbh = DBI->connect("DBI:mysql:database=esnap;host=localhost",
>                       "athena", "godess",
>                       {'RaiseError' => 1});
>
> # Determine MySQL locks table name
> my $sth = $dbh->prepare("SELECT * FROM region_props WHERE region =
> '$region'");
> $sth->execute();

Btw, this is *really* bad security wise. $region is coming straight from the
browser. You're setting yourself up for an SQL Injection attack. Imagine I
request some URL like:

  regDelLocks.pl?region= %27blah%27%3B+DROP+ALL+DATABASES

Guess what will happen? Preventing this is really easy. Just use SQL bind params.

my $sth = $dbh->prepare("SELECT * FROM region_props WHERE region = ?");
$sth->execute($region);

-- 
Michael Peters
Plus Three, LP

RE: mod_perl2 newbie DBI question

[Brian Gaber <Br...@PWGSC.GC.CA>]

Answers:

Is there anything useful in the Apache error log?  Works fine a few
times and then
DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '' at line 1 at
/usr/local/apache2/perl-run/regDelLocks.pl line 191.\n

What happens in the browser when it stops working?
The server encountered an internal error or misconfiguration and was
unable to complete your request.

What platform are you running on (windows, linux, etc.)?
Server is AIX.  Client is Windows.

Are you seeing this in your development environment? Yes

Can you reproduce the problem reliably? Yes

Thanks.

-----Original Message-----
From: Jim Brandt [mailto:cbrandt@buffalo.edu] 
Sent: Thursday, June 12, 2008 2:36 PM
To: Michael Peters
Cc: Brian Gaber; modperl@perl.apache.org
Subject: Re: mod_perl2 newbie DBI question



Michael Peters wrote:
> Brian Gaber wrote:
> 
>>         I have modified scripts to work in mod_perl2, but they don't 
>> work reliably.  Sometimes they work and then they stop working and 
>> then I have to stop and start Apache to get it working again.
> 
> "stop working" is really too generic a description for anyone to 
> really diagnose. It's kind of like telling your doctor "I'm sick, 
> here's my picture, tell me what's wrong" :)

Some information that can help:

Is there anything useful in the Apache error log?

What happens in the browser when it stops working?

What platform are you running on (windows, linux, etc.)?

Are you seeing this in your development environment?

Can you reproduce the problem reliably?


> 
>> I am hoping if
>> I supply one of my scripts someone can advise me what needs to be 
>> done to make in work fast and reliably and then I can use this as an 
>> example to fix my other scripts.  Here is the one script:
> 
> The best way to get help is to make the problem script as small as 
> possible. As it stands right now, it's a bit like homework trying to
read through your problem.
> 

--
Jim Brandt
Administrative Computing Services
University at Buffalo

Re: mod_perl2 newbie DBI question

[Jim Brandt <cb...@buffalo.edu>]

Michael Peters wrote:
> Brian Gaber wrote:
> 
>>         I have modified scripts to work in mod_perl2, but they don't
>> work reliably.  Sometimes they work and then they stop working and then
>> I have to stop and start Apache to get it working again. 
> 
> "stop working" is really too generic a description for anyone to really
> diagnose. It's kind of like telling your doctor "I'm sick, here's my picture,
> tell me what's wrong" :)

Some information that can help:

Is there anything useful in the Apache error log?

What happens in the browser when it stops working?

What platform are you running on (windows, linux, etc.)?

Are you seeing this in your development environment?

Can you reproduce the problem reliably?


> 
>> I am hoping if
>> I supply one of my scripts someone can advise me what needs to be done
>> to make in work fast and reliably and then I can use this as an example
>> to fix my other scripts.  Here is the one script:
> 
> The best way to get help is to make the problem script as small as possible. As
> it stands right now, it's a bit like homework trying to read through your problem.
> 

-- 
Jim Brandt
Administrative Computing Services
University at Buffalo

Re: mod_perl2 newbie DBI question

[Michael Peters <mp...@plusthree.com>]

Brian Gaber wrote:

>         I have modified scripts to work in mod_perl2, but they don't
> work reliably.  Sometimes they work and then they stop working and then
> I have to stop and start Apache to get it working again. 

"stop working" is really too generic a description for anyone to really
diagnose. It's kind of like telling your doctor "I'm sick, here's my picture,
tell me what's wrong" :)

> I am hoping if
> I supply one of my scripts someone can advise me what needs to be done
> to make in work fast and reliably and then I can use this as an example
> to fix my other scripts.  Here is the one script:

The best way to get help is to make the problem script as small as possible. As
it stands right now, it's a bit like homework trying to read through your problem.

-- 
Michael Peters
Plus Three, LP