problem with Perl and databases

Forum Moderators: coopster & phranque

Message Too Old, No Replies

problem with Perl and databases

aline

4:13 pm on Apr 26, 2003 (gmt 0)

I'm trying to send data to a database from user's input. the database is working fine when I sent new values to the table by inserting them(for example $update='insert into fish values(4,\'luke\')'; but I can't do it when I try to send the values from what the users might put. The code is the following:

#!c:/perl/bin/perl.exe
use DBI;
$,="\t";
use CGI ":standard";
$name=param("name");
$capacity=param("capacity");

print"content-type:text/html\n\n";
print"<html>\n";

$db=DBI->connect("dbi:mysql:Jo") or die"\n Error($DBI::err):$DBI::errstr\n";
$update='insert into fish values($name,$capacity)';
$s=$db->do($update);
$db->disconnect();
print"<b><font color=\"990000\">Database updated</font color></b>";

Can anyone tell me what I'm doing wrong as this code doesn't update the database at all?
Thanks

andreasfriedrich

4:23 pm on Apr 26, 2003 (gmt 0)

If you want to interpolate variables into a string you need to use double quotes, the q [perldoc.com]q operator or heredoc syntax with no quotes at all or double quotes.


$update = "INSERT [mysql.com] into fish values($name,$capacity)";


$update = qq{INSERT [mysql.com] into fish values($name,$capacity)};


$update = END_OF_SQL; 
INSERT [mysql.com] into fish values($name,$capacity) 
END_OF_SQL


$update = "END_OF_SQL"; 
INSERT [mysql.com] into fish values($name,$capacity) 
END_OF_SQL

Make sure that you escape all special characters in

$name

and

$capacity

Andreas

aline

4:28 pm on Apr 26, 2003 (gmt 0)

I've done what you said but my database still refuse to be updated!:-(

Glacai

4:39 pm on Apr 26, 2003 (gmt 0)

I think you may have to put the values into single quotes
$update = "insert into fish values ('$name', '$capacity')";

aline

4:41 pm on Apr 26, 2003 (gmt 0)

Thank you thank you, it's now working!

andreasfriedrich

4:44 pm on Apr 26, 2003 (gmt 0)

Try adding

or die $db->errstr;

to your do method call. This will report any db errors. Using placeholders (?) will safe you from having to escape any special characters in $name and $capacity. And you will not need to enclose non-numeric values in single quotes.


$db->do('insert into fish values(?,?)', undef, $name, $capacity) 
 or die $db->errstr;

When AutoCommit is off you will need to commit changes after you are finished with your transactions by calling

$db->commit

Andreas