perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Kaufman" <da...@gigawatt.com>
Subject Re: mod_perl2 newbie DBI question
Date Fri, 13 Jun 2008 02:07:20 GMT
Hi Brian,

"Brian Gaber" <Brian.Gaber@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






Mime
View raw message