Perl CGI to insert data into MySQL table

[idorjee]

hello,

i'm trying to write a perl cgi script to insert some values that i get from the my html form. i could manage to get the params from the html and process them (that part is not included in the following script), and put them into variables such as $accession and $sequence, but couldn't manage to submit those values into my MySQL table.

thank you so much!!!

my $cgi = new CGI;
 
print $cgi->header() .
$cgi->start_html( -title => 'Results',
-bgcolor => '#6699CC').
$cgi->h1('My Results') . "\n";
my @params = $cgi->param();
 
# i run some process with the params values that i get
......
 
# i print the output of the process on the browser as an html table
print "<tr><th>Accession</th><td>" . $accession . "</td></tr>\n";
print "<tr><th>Sequence</th><td>" . $sequence . "</td></tr>\n";
 
# now i want to send those two values into my MySQL table
# and i couldn't do that :(
$dbh = DBI->connect("DBI:mysql: testdb:localhost","root","passwd") or die "Error: $DBI::errstr\n";
my $sql = "INSERT INTO $tablename (anum, sequence) VALUES ('$accession','$sequence')";
$sth = $dbh->prepare($sql);
$sth->execute;
$dbh->do($sql);
$dbh->disconnect;
 
print "<form name="subscribers" method="post" action="insert.pl">";
print "</form>";
 

[miller]

You should never enter variables directly into a sql statement. This opens you up to security holes, and is actually harder to get to work right for the escaping of data. Instead always use placeholders in the following way:

my $sth = $dbh->prepare(qq{INSERT INTO $tablename SET anum=?, sequence=?});
$sth->execute($accession, $sequence) or die $dbh->errstr;
 

Also, always include or die calls with your execute statements. You will inherrantly mistype of a sql statement, and this way dbh will give you a meaningful error message when this happens.

- Miller

[idorjee]

Thank you for your quick reply. I'm sorry to bother you once again as I'm new with the perl cgi. Could you please tell me how to make the following part of the same script work? I don't understand what's missing. I'm trying to get a couple of buttons, so I could click to either insert into the MySQL table, or cancel the insertion. Please help.
thanks alot.

$dbh = DBI->connect("DBI:mysql: test:localhost","root","passwd") or die "Error: $DBI::errstr\n";
my $sth = dbh->prepare(qq{INSERT INTO $tablename SET anum=?, sequence=?, fasta_header=?, length=?, name=?, molwt=?, species=?, alpha_tms=?, alpha_orientation=?, beta_tms=?, beta_orientation=?,$
$sth->execute($accession, $sequence, $header, $length, $part_desc, $MolWt, $source, , , , , ) or die $dbh->errstr;
print "<form method=\"post\" action=\"sptxtupdate.pl\">";
print "<INPUT type=\"submit\" name=\"update\" value=\"Update\">\n";
print "</form>";
print "<br>";