my @field = qw(
   Title Email City State Country URL
   Date Description rid dt_create publish
);

my $insert_sth = $dbh->prepare(
   'INSERT INTO ads_2004 ('
   . join(', ', map "'$_'", @fields)
   . ') VALUES (?,?,?,?,?,?,?,?,NULL, NOW(), 0)'
);
[download]

use CGI qw(:standard);

my %field;
for (@field) {
   my $param = param($_);
   die unless $param;     # do validation on $param here ...
   $field{$_} = $param;
}

# or validate specific pieces outside the loop:
die unless $field{rid} =~ /^\d+$/;
die unless is_valid_email($field{Email});

$insert_sth->execute(values %field);
[download]

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Thanks!

Your code looks great but unfortunately, I'm not quite sure how to integrate it into the existing script I use, sorry.
However, I've been creating a new version of the script with all the field names matching the database as you suggested but am not quite there yet.

Thanks again, :-)

-Bob

"so many drummers, so little time"

#!/usr/bin/perl -wT
use strict;
use DBI;
use CGI;

my $cgi = CGI->new();          
print $cgi->header(),       # Print header etc.
      $cgi->start_html();


if($cgi->param()) {    # something was submitted
     my $name =    $cgi->param("name") || "Anonymous";
     my $message = $cgi->param("message") || "";

     if($message)  {   # If they submitted some message

          # Make a connection to the database.
          my $dbh = DBI->connect("DBI:mysql:host=localhost;database=my
+_db",
                    "my_id", "my_pass",
                    {AutoCommit => 1,   # commit immediately
                     PrintError => 0,   
                     RaiseError => 1
                     ShowErrorStatement => 1
                    }
          );

          # Prepare the SQL so we can then use it to insert
          # into the database.  Notice that we use ?s instead
          # of actual values.  This means that we can get
          # DBI to do our actual quoting and saves us a lot
          # of bother.
          my $sth = $dbh->prepare("INSERT INTO guestbook  
                    (name, message, date) VALUES (?,?,?)");

          # Now we execute the SQL.  We pass in one value
          # for each question mark that we put into the 
          # prepare statement up there.  DBI will make sure
          # that our values are properly escaped.
          $sth->execute($name, $message, scalar(localtime(time)));

          # This entry has now been added to the database.
          # Since we (probably) don't need the database 
          # handle anymore, we tidy up by disconnecting.
          $dbh->disconnect();

          # Print something for the user to see.
          print "Thankyou for your addition to the guestbook.";

     }
     print "I think you forgot to add a message";
}

print_guestbook();     # You'll have to fill this one out.
print_addtoguestbook();# You'll have to fill this one out too.

print $cgi->end_html;
[download]

I whole-heartedly agree with davorg's advice that you replace your original script with the drop in replacement from nms. This code is free, well written and more secure than Matt's scripts. It is probably easier to improve in the manner you're trying than Matt's scripts will be, too.

Anyway, I hope this now makes sense to you.

jarich

Thanks Jarich!

The script already produces INSERT strings that work fine. Count Zero's code does excatly what I'm after, that is, it posts an INSERT string into MySQL. The problem is, that string is from an example that I made and placed on an HTML page, not what the form generates. In other words, every time the form is submitted, the same INSERT string posts. What I'm hoping somebody can see here is how to get the INSERT strings from the form to post - either by modifying CountZero's code or with something else - rather than the sample INSERT.

Thanks again,

What you're really asking of us is for us to read your mind. There's a whole heap of information that you should have put into this question. This includes the following:

• how much experience you've had with Perl
• what you've tried
• what you think is happening
• what relation the snippet of code above has to your problem
• where/when the file in $guestbookreal gets written.

You can read more about what kind of information we like to see in questions over at our tutorials page.

You've written this question assuming that we've already read the previous discussion from last week. If this question is entirely dependant on that discussion then this node should have gone into there, not created a new question. If this question is a new question then you need to write out all the relevant information (and only the relevant information) so that people like me can see everything I need here.

Now that I've read last week's discussion I'll discuss what's happening in your code over here.

$dbh = DBI->connect ("DBI:mysql:host=localhost;database=my_db",
 "my_id", "my_pass",
 {PrintError => 0, RaiseError => 1});
[download]

# Begin the Editing of the Guestbook File
open (FILE,"$guestbookreal") || die "Can't Open $guestbookreal: $!\n";
@LINES=<FILE>;
close(FILE);
$SIZE=@LINES;
[download]

# Open Link File to Output
open (GUEST,">$guestbookreal") || die "Can't Open $guestbookreal: $!\n
+";
[download]

for ($i=0;$i<=$SIZE;$i++) {
   $_=$LINES[$i];
   if (/<!--begin-->/) { 
      if ($entry_order eq '1') {         
      }

      {
         print GUEST "INSERT INTO `mysql_db` (`Title`, 
                     `Email`, `City`, `State`, `Country`, 
                     `URL`, `Date`, `Description`, `rid`,
                     `dt_create`, `publish` ) VALUES (";
      }
      ....
   }
   else {
      print GUEST $_;
   }
}

close (GUEST);
[download]

my $content;
open FILE, '<', $guestbookreal; # Open a filehandle FILE to $guestbook
+real 
while (<FILE>) {
   chomp; # delete any EOL character(s)
   $content .= $_; # Add the next line to the previous parts
}

$sth=$dbh->prepare($content);
$sth->execute();

$dbh->disconnect;
[download]

Because you've cut so much out of the main for loop I have no idea what specifically is wrong here. It appears obvious that the script is supposed to add your new entry (submitted from the guestbook form) into the $guestbookreal file as the first line. You seem to have lost the lines that would do that.

You're trying to shoehorn a database onto code which is designed to store guestbook entries into a flat HTML file. I recommend that instead of generating your SQL and printing it to a file you consider rewriting this part of the script entirely. Perhaps you could replace everything from:

# Begin the Editing of the Guestbook File
[download]

$dbh->disconnect;
[download]

# Check that the things which must have a value have values:
if(defined $FORM{Description} and defined $FORM{Title})
{
    my $sql = "INSERT INTO `mysql_db` (`Title`, `Email`, 
              `City`, `State`, `Country`, `URL`, `Date`, 
              `Description`, `rid`, `dt_create`, `publish`
              ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    my $sth = $dbh->prepare($sql);
    $sth->execute($FORM{Title}, $FORM{Email}, $FORM{City},
                  $FORM{State}, $FORM{Country},
                  $FORM{URL}, $FORM{Date}, 
                  $FORM{Description}, $FORM{rid},
                  $FORM{dt_create}, $FORM{publish});
    $dbh->disconnect;
}
else
{
    print "You must enter a Description and a Title";
}
[download]

Once more I'll recommend that you change to using nms scripts rather than Matt Wrights'. As you've experienced in the past, Matt's scripts have a few security issues, and the hand-rolled CGI parameter parsing in the script I discuss here, makes me cringe.

Good luck.

jarich

Hi jarich,

Thanks for your reply - I had no idea that Matt's script was doing all that deleting/rewriting biz - yikes!

anyhow...

I tried inserting your code as suggested but am getting "Server Error" replies. While what you're suggesting does indeed make lots of sense, it seems that it also involves really overhauling most of the script - as well as the form - to reflect the new fields and their values, right?

Since the script as it is ("code ugliness" etc., notwithstanding...), already spits out working INSERT strings (with the field values for 'rid', 'dt_create', and 'publish' all included in the "Description" field - I know this does not reflect how many fields are in the form, but since the string output matches the database fields, it "works"...), I guess I don't understand why that same data can't somehow be redirected to MySQL along the lines of CountZero's code which does successfully post INSERT strings to MySQL - just from the wrong location?

Also, since Matt's <!begin> biz is not needed for inserting into MySQL and screws things up if it's there (which is why I removed it), I'm wondering if perhaps there's a way to simply delete/replace/avoid/modify those parts of the script, yet still process the form? If so, shouldn't it then be possible to execute the strings as they output?

Thanks again jarich,

-Bob

bobafifi.com

(cont.)

delete and/or modify these??

$mysql = "/home/flute/usedflutes-www/new_listings_mysql.html";
[download]

(from the top of the script and)

# Begin the Editing of the MySQL File
open (FILE,"$mysql") || die "Can't Open $mysql: $!\n";
@LINES=<FILE>;
close(FILE);
$SIZE=@LINES;

# Open Link File to Output
open (MYSQL,">$mysql") || die "Can't Open $mysql: $!\n";

for ($i=0;$i<=$SIZE;$i++) {
   $_=$LINES[$i];
   if (/<!--begin-->/) {
[download]

(from the MySQL section)

# MySQL

# Begin the Editing of the MySQL File
open (FILE,"$mysql") || die "Can't Open $mysql: $!\n";
@LINES=<FILE>;
close(FILE);
$SIZE=@LINES;

# Open Link File to Output
open (MYSQL,">$mysql") || die "Can't Open $mysql: $!\n";

for ($i=0;$i<=$SIZE;$i++) {
   $_=$LINES[$i];
   if (/<!--begin-->/) {


      if ($entry_order eq '1') {
        
      }

{
         print MYSQL "INSERT INTO `mysql_db` (`Title`, `Email`, `City`
+, `State`, `Country`, `URL`, `Date`, `Description`, `rid`, `dt_create
+`, `publish`, ) VALUES (";
      }


      if ($line_breaks == 1) {
         $FORM{'Title'} =~ s/\cM\n/\n/g;
      }

      print MYSQL "$FORM{'Title'}";

print MYSQL ", ";

      if ( $FORM{'Email'} ){
    my $email_name = $dbh->quote($FORM{'Email'});

#         if ($linkmail eq '1') {
            print MYSQL $email_name;

#         }
#         else {
#            print MYSQL "$FORM{'Email'}";
#         }

         print MYSQL ", ";
      }

      if ( $FORM{'City'} ){
         print MYSQL "$FORM{'City'}";
      }
     {
         print MYSQL ", ";
      }
      if ( $FORM{'State'} ){
         print MYSQL "$FORM{'State'}";
      }
{
         print MYSQL ", ";
      }
      if ( $FORM{'Country'} ){
         print MYSQL "$FORM{'Country'}";
      }
{
         print MYSQL ", ";
      }

if ($FORM{'URL'}) {
         print MYSQL "$FORM{'URL'}";
      }
      else {
         print MYSQL "$FORM{'URL'}";
      }
{
         print MYSQL ", ";
      }

if ($separator eq '1') {
         print MYSQL "'$date'";
      }
      else {
         print MYSQL "'$date'";
      }
{
         print MYSQL ", ";
      }
      
if ( $FORM{'Description'} ){
         print MYSQL "$FORM{'Description'}, '', NOW(), 0) \n\n";


      }

           if ($entry_order eq '0') {
         print MYSQL "<!--begin-->\n";
      }

   }
   else {
      print MYSQL $_;
   }
}

close (MYSQL);

my $content;
open FILE, '<', $mysql; # Open a filehandle FILE to $mysql 

while (<FILE>) {
   chomp; # delete any EOL character(s)
   $content .= $_; # Add the next line to the previous parts
}

$sth=$dbh->prepare($content);
$sth->execute();

close (MYSQL);

$dbh->disconnect;
[download]

=====

Thanks,


-Bob

bobafifi.com

use DBI;

...

my $db_handle = DBI->connect( "DBI:mysql:database=$dbname;host=$hostna
+me",
                              $username, $password ) or
   die "MySQL connection failed: $DBI::errstr";

...

# make up an sql statement string and a set of parameter values
# and execute it, as suggested in jeffa's reply...
[download]

Yes, I already have a connection to DBI at the top of the script (that stuff is all in my earlier post, sorry - http://perlmonks.org/index.pl?node_id=329045

Thanks!

-Bob

Ok, I'm not quite sure what you are after but it sounds like instead of reading text from a file, you want to read it from a form. Is that right?

If so, you need to look at something like cgi.pm This will allow you to create a form, and then you can get the value of that form when you hit submit.

So basically, do you have the form created already? Is the action on that form your perl script? Is your perl script getting the values? If you answered yes, then you just need to submit that to your database. If you answered no to any of those questions, you need to give us more information and code. Suggested snippets of code to include: Part of the form that shows a field you want to insert, your sql insert statement and anything else you think might help.

Hi MCS,

OK - here's the situation:

I posted this problem last week http://perlmonks.org/index.pl?node_id=329045
(there you can see my original question, code examples and replies).

During the back-and-forth of replies, CountZero provided something that worked - well, almost...

The script generates INSERT strings whenever the form is submitted. Normally, these strings post to an HTML page ( as well as to an e-mail send that I get).
By selecting, copying and pasting the INSERT strings into a GUI and hitting "GO" they are added to the MySQL and work fine.

For some reason (which I do not understand), inserting CountZero's code somehow turns the POST function of the script

 $guestbookreal = "/home/flute/usedflutes-www/new_listings_publish.html";

to a GET function of the sample INSERT string sitting on the HTML page - not from the form.

Since CountZero's code does post the INSERT string to MYSQL - but from the wrong location - I'm trying to find out what the *right* location is of the INSERT string as it outputs from the form - so that is what goes into MySQL.

Thanks again,


-Bob

bobafifi.com

Wow... first, a major rewrite is in order. I looked over your code from the other post and I realize most of it is from Matt's script archive but I think even you, as a beginner, can write better code than that. That said I offer the following suggestions for your existing code:

It appears you are getting the input from STDIN which doesn't make sense if it is from a form. but if it is working and all you want to do is automate putting the print statement into a database, instead of (or in addition to) all your lines that say print GUEST you would need a line that says something like $insert .= and then the rest of the line. Example:


print GUEST "INSERT INTO `mysql_db` (`Title`, `Email`, `City`, `State`
+, `Country`, `URL`, `Date`, `Description`, `rid`, `dt_create`, `publi
+sh` ) VALUES (";
      }
      if ($line_breaks == 1) {
          =~ s/\cM\n/\n/g;
      }
      print GUEST "";
print GUEST ", ";
[download]


Would become:

my $insert = "INSERT INTO `mysql_db` (`Title`, `Email`, `City`, `State
+`, `Country`, `URL`, `Date`, `Description`, `rid`, `dt_create`, `publ
+ish` ) VALUES (";
      }
      if ($line_breaks == 1) {
          =~ s/\cM\n/\n/g;
      }
      $insert .= "";    # This can be removed as it doesn't do anythin
+g
$insert .= ", ";
[download]


As you can see, first I initialized $insert which would be done the first time you need to write it and then after that I use the concatinate operator (.=) to add text to $insert. Then when you are done, you just need to change $sth=$dbh->prepare($content); to $sth=$dbh->prepare($insert); and then it should insert your code into the database.

Some suggestions:
I suggest you pick up learning perl (and maybe a few other good books, check the book review section of this site) and rewrite your code. Use CPAN and check out some of it's modules, the documentation is usually quite good. Specifically look at CGI.pm which you can use to create all of your forms and then use the data from said forms to populate the database. (put the information in it) Writing to a file and then reading it again to enter the data into a database is VERY slow and while that may not be a big issue to you now, it might later on. Also problems can arrise if 2 people try and write to your guestbook at the same time. These issues go away when you stop writing to disc all the time.

So maybe get your current version working and then take it upon yourself to rewrite it once it's working. Trust me, it's worth it to write it yourself. With good resources like perl monks as well as the countless books and web tutorials on perl, even someone with no programming experience can learn, it just takes a little time.