By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,680 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,680 IT Pros & Developers. It's quick & easy.

Perl CGI to insert data into MySQL table

P: 76
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
  1. my $cgi = new CGI;
  2.  
  3. print $cgi->header() .
  4. $cgi->start_html( -title => 'Results',
  5. -bgcolor => '#6699CC').
  6. $cgi->h1('My Results') . "\n";
  7. my @params = $cgi->param();
  8.  
  9. # i run some process with the params values that i get
  10. ......
  11.  
  12. # i print the output of the process on the browser as an html table
  13. print "<tr><th>Accession</th><td>" . $accession . "</td></tr>\n";
  14. print "<tr><th>Sequence</th><td>" . $sequence . "</td></tr>\n";
  15.  
  16. # now i want to send those two values into my MySQL table
  17. # and i couldn't do that :(
  18. $dbh = DBI->connect("DBI:mysql: testdb:localhost","root","passwd") or die "Error: $DBI::errstr\n";
  19. my $sql = "INSERT INTO $tablename (anum, sequence) VALUES ('$accession','$sequence')";
  20. $sth = $dbh->prepare($sql);
  21. $sth->execute;
  22. $dbh->do($sql);
  23. $dbh->disconnect;
  24.  
  25. print "<form name="subscribers" method="post" action="insert.pl">";
  26. print "</form>";
  27.  
Aug 6 '07 #1
Share this Question
Share on Google+
2 Replies


miller
Expert 100+
P: 1,089
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
  1. my $sth = $dbh->prepare(qq{INSERT INTO $tablename SET anum=?, sequence=?});
  2. $sth->execute($accession, $sequence) or die $dbh->errstr;
  3.  
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
Aug 6 '07 #2

P: 76
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
  1. $dbh = DBI->connect("DBI:mysql: test:localhost","root","passwd") or die "Error: $DBI::errstr\n";
  2. 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=?,$
  3. $sth->execute($accession, $sequence, $header, $length, $part_desc, $MolWt, $source, , , , , ) or die $dbh->errstr;
  4. print "<form method=\"post\" action=\"sptxtupdate.pl\">";
  5. print "<INPUT type=\"submit\" name=\"update\" value=\"Update\">\n";
  6. print "</form>";
  7. print "<br>";
  8.  
Aug 6 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.