ginda has asked for the wisdom of the Perl Monks concerning the following question:

Hello all
Iam making a simple scripted program that can read and write to a mysql database using html forms. I can search the database fine so far. Bu now i am trying to insert data into the database table but it just doesnt wanna work. I think its somthing todo with the param part of the program as i was using one param fields before but now going to use two.
Heres the code for the inserting of data. 
#!/usr/bin/perl -w

use strict;

use CGI;
use CGI::Carp qw(fatalsToBrowser);
use vars qw($Query $Query2 $dbh $sth $Value $Data1 $Data2);

my @results;
my @row;
use DBI;

$Query = new CGI(  );


$dbh = DBI->connect("DBI:mysql:exelstock")
      or die "Cannot connect: " . $DBI::errstr;

$Data1 = $Query->param('Value4');
$Data2 = $Query->param('Value5');

$sth = $dbh->prepare("INSERT INTO walstock (sid, name, qty) VALUES (DE
+FAULT, $Data1, $Data2)");
$sth->execute();

print "Content-type: text/html\n\n";
print "<HTML>\n";
print "<BODY>\n";
print "<HEAD><TITLE>Insert Records</TITLE></HEAD>\n";

$sth = $dbh->prepare("SELECT * FROM walstock WHERE name='$Data1'");
$sth->execute();

while (@row = $sth->fetchrow_array) {
    print "@row\n";

}


$sth = $sth->finish();

$dbh->disconnect();

print "</BODY>\n";
print "</HTML>\n";

[download]
Trying to do this for a university project, any help would be great thanks all

Edited by Chady -- added code tags.

2005-03-21 Edited by Arunbear: Changed title from 'Was working but now this part doesnt', as per Monastery guidelines

Replies are listed 'Best First'.
Re: Problems inserting values in mysql database
by brian_d_foy (Abbot) on Mar 19, 2005 at 05:24 UTC

    After each DBI method call, check $DBI::errstr to see what it might say. You might also want to include RaiseError as an option until you figure out where it's breaking.

    Good luck :)

    --
    brian d foy <bdfoy@cpan.org>
[reply]
Re: Problems inserting values in mysql database
by cowboy (Friar) on Mar 19, 2005 at 05:32 UTC
    You should use placeholders. Read the perldoc for DBI, and DBD::mysql 
    $sth = $dbh->prepare("INSERT INTO walstock (sid, name, qty) VALUES (?,
+?,?)");
# ok, we have 3 placeholders, pass 3 params to the execute function.

if ($sth->execute('DEFAULT', $Data1, $Data2)) {
  print "Worked, cool";
}
else {
  print "Didn't work, uncool, error is: $DBI::errstr";
}

    [download]
    The placeholders, in some cases speed up your queries, but the main concern, is they escape data properly, which makes for safe queries, and little risk of sql-injection attacks. Imagine this ($user_id read from data a user submitted) 
    # we expect $user_id to be a user_id, but we dont' check it.
$dbh->prepare("DELETE FROM foo WHERE user_id=$user_id");
# looks good, except, the user submitted as the value for user_id: 10 
+or 1=1
# so we end up with, "DELETE FROM foo WHERE user_id=10 or 1=1
# which in effect, deletes everything, because 1 always equals 1

    [download]
    The moral of the story, always use placeholders by default. If you choose not to use them, you know why you aren't using them, and know to verify your data extra carefully. Make using them the default, and think about any time you aren't using them.

    (updated, thought of last paragraph after I hit submit, so I added it)
    (updated again, removed question about DEFAULT, as it's a valid mysql keyword)
[reply]
[d/l]
[select]
      The DEFAULT keyword usually just specifies that a column should take on its default value. DEFAULT is a reserved work in mysql...should be OK.
      chas
[reply]
        Thanks, I wasn't aware of the DEFAULT option.
[reply]
Re: Problems inserting values in mysql database
by chas (Priest) on Mar 19, 2005 at 05:32 UTC
    How are the data values provided to the script (from form fields?) Does the script run from the command line (providing values for the data)? Does everything work when you omit $Data2 (That's what you seemed to be saying...) or is the INSERT the problem?
    chas
[reply]

Back to Seekers of Perl Wisdom