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!!!

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

 
$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>";

Perl CGI to insert data into MySQL table

re: Perl CGI to insert data into MySQL table

re: Perl CGI to insert data into MySQL table

Similar Perl bytes

/bytes/development

/bytes/it

/bytes/about