URL Parameters & DBD::mysql::st execute failed

[aitai]

Hi, All

I know that this should be simple, and for some reason I am losing my hair! I would appreciate any insight into this problem. Thanks in advance.

This is the full error message:Can't execute the select statement:Unknown column 'INSS209' in 'where clause'

The unknown column in the message is a url parameter passed:
$cgi = new CGI;
$Course_No = $cgi->param('Course_No');
Course_No is the column name, and INSS209 is the value.

I am using ActiveStatePerl and MySQL on WIN2K.

The full script;

#!D:/perl/bin/perl
# Prepared by Mizan, Ivan, Zhuo, & Baharak
# Group: 6
# Date: Dec 11, 2001


use CGI qwstandard);
use CGI::Carp qw(fatalsToBrowser);
use DBI;

$cgi = new CGI;
$Course_No = $cgi->param('Course_No');


#define variables
$database="test";
$host="localhost";
$user="avandermerwe";
$password="aitai";

if ($Course_No = $cgi->param('Course_No')){
#&SetVariables;
&ConnectDB;
&PrintPageStart;
&CourseQuery;
&PrintCourses;
&ConnectEnd;
&PrintPageEnd;
}
else{
&PrintPageStart;
&EmptyRecordset;
&PrintPageEnd;
}

#connect to MySQL database
sub ConnectDB {
$dbh=DBI->connect ("DBI:mysql:$database:$host",$user,$password);
}

#end connection to MySQL database
sub ConnectEnd {
$rc=$sth->finish;
$rc=$dbh->disconnect;
}

#select courses from the database
sub CourseQuery{
#my $Course_No = $cgi->param('Course_No');

$Statement = qq[
SELECT *
FROM courses
WHERE Course_No = $Course_No
];

# prepare statement for execution
$sth = $dbh->prepare($Statement)
or die "Can't prepare the query:", $sth->errstr, "\n";

#execute the query
$rv=$sth->execute
or die "Can't execute the select statement:", $sth->errstr, "\n";
}

sub PrintPageStart{
# Prepare HTML headers
print header, start_html (-title=>"Course Description",-bgcolor=>'white');
print&quot;<br />\n&quot;;
print&quot;<center>\n&quot;;
print&quot;<h2>Course Description</h2>\n&quot;;
print&quot;</center>\n&quot;;
print&quot;<br />\n&quot;;
}

sub PrintPageEnd{
print &quot;</body>\n&quot;;
print &quot;</html>\n&quot;;
}

sub PrintCourses{

while (@row=$sth->fetchrow_array){
($Course_No,$Title,$Description,$Credits,$Prerequisite)=@row;
print &quot;<table>\n&quot;;
print &quot;<tr>\n<td><b>Course Number: </b></td>\n<td>$Course_No</td>\n</tr>\n&quot;;
print &quot;<tr>\n<td><b>Title: </b></td>\n<td>$Title</td>\n</tr>\n&quot;;
print &quot;<tr>\n<td><b>Description: </b></td>\n<td>$Description</td>\n</tr>\n&quot;;
print &quot;<tr>\n<td><b>Credits: </b></td>\n<td>$Credits</td>\n</tr>\n&quot;;
print &quot;<tr>\n<td><b>Prerequisites: </b></td>\n<td>$Prerequisite</td>\n</tr>\n&quot;;
print &quot;</table>\n&quot;;}
}

sub EmptyRecordset{
print &quot;<b>You did not make a valid select. Try again!</b>\n&quot;;
}

 

[hmerrill]

Read up on &quot;placeholders&quot; - bring up a DOS window and at the command prompt type:

perldoc DBI

and search(using the &quot;/&quot; operator) for &quot;placeholder&quot;. I believe your select will look something like this:


sub CourseQuery{
#my $Course_No = $cgi->param('Course_No');

$Statement = qq[
SELECT *
FROM courses
WHERE Course_No = ?
];

# prepare statement for execution
$sth = $dbh->prepare($Statement)
or die &quot;Can't prepare the query:&quot;,
$sth->errstr, &quot;\n&quot;;

#execute the query
$rv=$sth->execute($Course_No)
or die &quot;Can't execute the select statement:&quot;, $sth->errstr, &quot;\n&quot;;
}
-----------------------------------------------------

Notice the &quot;?&quot; in the select $Statement, and the $Course_No in the execute.

HTH.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com

 

[Coderifous]

It will work the way you had it, just make it so that quotes are around the variable:

$Statement = qq[SELECT * FROM courses
WHERE Course_No = &quot;${Course_No}&quot;];
[/color red]
Since I hate using place holders, this is the way I would do it. When in doubt, type the SQL statement into the MySQL DOS client. More often than not, you'll realize the problem when you see it there:
SELECT * FROM courses WHERE Course_No = ISSN90;
This would return the same error you were getting. Since the value isn't in quotes, MySQL thinks that ISSN90 is a column name.
SELECT * FROM courses WHERE Course_No = &quot;ISSN90&quot;;
Much better... huh?

--Jim

 

[aitai]

Thanks, Guys

Your suggestions put me over the top-much appreciated!

Ivan