Help debuging a script that uses commands I am just learning

Eagle_f91 has asked for the wisdom of the Perl Monks concerning the following question:

I have just gotten into using stored procs in MS SQL 2005 to move a lot of the logic from perl to T-SQL and now I am just learning how to call those procs and pass information to them. I though I understood what needed to be coded but aparently not as I am getting two errors I can't find the cause of. Can anyone look over the code with a fresh set of eyes to see what I can't find in the past few hours of debuging. Thanks
Code:

#!/user/bin/perl -w
use strict;
use CGI::Carp qq~fatalsToBrowser~;
use CGI q~:standard~;
use DBI;
print "Contect-type: text/html\n\n";
my ($DBH, $STH, $Creator, $ContactMethod, $Contact, $FileName, $Game, 
+$Section, $Width, $Height, $File, $FileHandel, $Result, $Error);
$Creator = param('Creator');
$ContactMethod = param('ContactMethod');
$Contact = param('Contact');
$FileName = param('FileName');
$Game = param('Game');
$Section = param('Section');
$Width = param('Width');
$Height = param('Height');
$File = param('File');
$File =~ s/.*[\/\\](.*)/$1/;
$FileHandel = upload('File');
$Error = 0;
mkdir ("e:/web/public_html/finalfantasyinfo/ffinfo/protected/images/av
+atars/$Game");
mkdir ("e:/web/public_html/finalfantasyinfo/ffinfo/protected/images/av
+atars/$Game/$Section");
$DBH = DBI -> connect ('dbi:ODBC:FFInfocom', '', '') or die "$DBI::err
+str;";
$STH = $DBH -> prepare (qq~exec AddAvatars ?, \@Creator = '$Creator', 
+\@ContactMethod = '$ContactMethod', \@ContactAddress = '$Contact', \@
+AvatarName = '$FileName, \@Game = '$Game', \@Section = '$Section', \@
+Width = '$Width', \@Height = '$Height'~) or die "$DBI::errstr;";
$STH -> bind_param_inout(1, \$Error, 1);
$STH -> execute or die "$DBI::errstr;";
if ($Error == 0) {
    open UPLOADFILE, ">e:/web/public_html/finalfantasyinfo/ffinfo/prot
+ected/images/avatars/$Game/$Section/$File";
    binmode UPLOADFILE;
    while (<$FileHandel>) {
        print UPLOADFILE;
    }
    close UPLOADFILE;
    $Result = 'New Avatar added';
} else {
    $Result = 'That Avatar already exists. Please add another one or c
+hange the file name and try again.';
}

&PrintHTML;
sub PrintHTML {
print qq~<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN
+"    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Final Fantasy Info.com Site Administration Section</title>
<meta name="description" content="Final Fantasy Info.com Site Administ
+ration Section" />~;
open (FILE, 'e:\web\public_html\finalfantasyinfo\ffinfo\ssi\header.txt
+') or die 'Can not open e:\web\public_html\finalfantasyinfo\ffinfo\ss
+i/header.txt';
print <FILE>;
close FILE;
print qq~</head>
<body onload="columnheight(); highlight()">
<div style="text-align: center; margin-bottom: 2px"><img src="http://w
+ww.ffinfo.com/images/misc/sitebanner.jpg" width="950" height="100" al
+t="Welcome to Final Fantasy Info.com - The Best Place to Find Everyth
+ing Final Fantasy!" /></div>~;
open (FILE, 'e:\web\public_html\finalfantasyinfo\ffinfo\ssi\nav.txt') 
+or die 'Can not open e:\web\public_html\finalfantasyinfo\ffinfo\ssi\n
+av.txt';
print <FILE>;
close FILE;
print qq~<div id="leftcolumn">~;
open (FILE, 'e:\web\public_html\finalfantasyinfo\ffinfo\admin\menu.txt
+') or die q~Can not open e:\web\public_html\finalfantasyinfo\ffinfo\a
+dmin\menu.txt~;
print <FILE>;
close FILE;
print qq~</div>
<div id="maincolumn"><div class="sectioncontent">$Result</div>
</div>
<div id="copywrite">~;
open (FILE, 'e:\web\public_html\finalfantasyinfo\ffinfo\ssi\copywrite.
+txt') or die 'Can not open e:\web\public_html\finalfantasyinfo\ffinfo
+\ssi\copywrite.txt';
print <FILE>;
close FILE;
print qq~</div>
</body>
</html>~;
}
[download]

Errors I get:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '
+Test4'. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark
+ after the character string ''. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not 
+be prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=-1); at e:\we
+b\public_html\finalfantasyinfo\ffinfo\admin\avatars\addavatar.pl line
+ 25.
[download]

Any thoughts of the cause of the problem would be welcomed, along with any thoughts of improve the security and function of the script. Thanks

Content restored by GrandFather

Comment on Help debuging a script that uses commands I am just learning Select or Download Code

Replies are listed 'Best First'.
Re: Help debuging a script that uses commands I am just learning by pc88mxer (Vicar) on Apr 07, 2008 at 02:15 UTC
I would guess that your problem stems from the improper quoting of parameters being passed to your `AddAvatars` stored proc. It looks like there a single quote missing after `$FileName` in the prepare statement: `... \@AvatarName = '$FileName, \@Game = '$Game', ... ^ single quote missing here` [download] Also, can you place holders for the parameters? Try something like: `$STH = $DBH -> prepare ('exec AddAvatars ?, @Creator = ?, @ContactMeth +od = ?, @ContactAddress = ?, @AvatarName = ?, @Game = ?, @Section = ? +, @Width = ?, @Height = ?') or die "$DBI::errstr;"; $STH -> bind_param_inout(1, \$Error, 1); $STH -> bind_param(2, $Creator); $STH -> bind_param(3, $ContactMethod); ...etc... $STH -> execute or die "$DBI::errstr;";` [download] Note that using place holders allows you to simplify the syntax of your SQL. You can use single quotes for the statement and there's no need to escape the at-signs.	[reply] [d/l] [select]
Help debuging a script that uses commands I am just learning by Gavin (Archbishop) on Apr 06, 2008 at 21:21 UTC
You should know better by now than to remove your question! UPATE Changed title to reflect now restored question thanks to jdporter and GrandFather	[reply]
Re: Help debuging a script that uses commands I am just learning by starbolin (Hermit) on Apr 07, 2008 at 18:30 UTC
Eagle f91 having committed the web forum blunder of asking for an opinion, and I having a quiet morning without the missus, I hereby offer my less than oh-so-humble two-cents. `<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">` I know that's the way some documents say you should link to your name-space but I've seen it break websites. Think what happens if w3.org is not available. Do you really need to specify a name-space? I don't see anything in your html that could be rendered in a non-standard way. `open (FILE, 'e:\web\public_html\finalfantasyinfo\ffinfo\ssi\header.txt +') or die +'Can not open e:\web\public_html\finalfantasyinfo\ffinfo\ssi/header.t +xt'; print <FILE>; close FILE;` [download] More a style preference but I would open all the files first then print your html. That way if one of the files won't open you can abort the screen and give them a less confusing error. It would also make the perl code cleaner. If you are worried about too many open filehandles then use symbolic filehandles. You're not saving any memory as `print <FILE>` slurps the whole thing in before printing it so you might as well slurp the files into scalars then call print on the scalars. There is also not reason not to be paranoid and open those for reading only: `open (my $file, '<e:\blahblahblah\header.txt')`That little change could prevent a mis-type from clobbering your dataset. This seems awkward: `mkdir ("e:/web/public_html/finalfantasyinfo/ffinfo/protected/images/avatars/$Game");` I wouldn't expect a cgi script to have permissions to create directories and you would only need to create the directory once but here each invocation of the script tries to create the directory and without checking first if the directory already exists. Multiple param() statements could become this: `( $Creator, $ContactMethod, $Contact, $FileName, $Game , $Section, $Width, $Height, $File, ) = map { param } qw( Creator ContactMethod Contact FileName Game Section Width Height File )` [download] Although I'm not sure this could be considered a visual improvement. It's just that programmers are lazy and always look for an automated way to excise any repetition. Happy coding! Looks like you have a fun little project there Eagle f91. s//----->\t/;$~="JAPH";s//\r<$~~/;{s\|~$~-\|-~$~\|\|\|s \|-$~~\|$~~-\|\|\|s,<$~~,<~$~,,s,~$~>,$~~>,, $\|=1,select$,,$,,$,,1e-1;print;redo}	[reply] [d/l] [select]