Hi Brian,
"Brian Gaber" <[EMAIL PROTECTED]> 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
