Update db row with perl cgi

Question

I am trying to update my sql table with some new values entered into the user. For some reason the sql command is not updating my db. I am getting the correct values I verified. Here is my code

#!/usr/bin/perl 
#This is going to be the user login check and will set a cookie

use DBI;
use CGI qw(:standard);

use strict;

#Connection error 
sub showErrorMsgAndExit {
    print header(), start_html(-title=>shift);
    print (shift);
    print end_html();
    exit;
}

#Connecting to the database
my $dbUsername = "root";
my $dbPassword = "password";

my $dsn = "DBI:mysql:f18final:localhost";
my $dbh = DBI->connect($dsn, $dbUsername, $dbPassword, {PrintError => 0});

#error checking
if(!$dbh) {
    print header(), start_html(-title=>"Error connecting to DB");
    print ("Unable to connec to the database");
    print end_html();
    exit;
}

print header;
print start_html(-title=>'Add Classes');

#Get the information the user entered
my $id = param('classid');
my $className = param('classname');
my $department = param('department');
my $classnum = param('classnum');
my $grade = param('grade');
my $credits = param('credit');
print "$id $className, $department, $classnum, $grade, $credits";
#first sql check to see if username is already taken
my $check = "UPDATE tblclasses(classname, department, classnum, grade, credits) VALUES (?, ?, ?, ?, ?) WHERE classID = $id";
my $sth = $dbh->prepare($check);
$sth->execute($className, $department, $classnum, $grade,$credits);
print "<h1>Success</h1>";
print "<form action=http://localhost/cgi-bin/edit.pl method = 'post'>";
print "<input type = 'submit' name = 'submit' value = 'Update Another'>";
print "</form>";
print "<form action=http://localhost/cgi-bin/actions.pl method = 'post'>";
print "<input type = 'submit' name = 'submit' value = 'Back to actions'>";
print "</form>";


print end_html();
exit;

When I try to run the sql command in mysql workbench it successfuly updates the row. What is my issue?

You either need to set RaiseError => 1 in the DBI constructor so that errors will be thrown as exceptions you can see in the log (easy option), or check for errors yourself for every connect and prepare and execute and other DBI calls as described in the DBI documentation. — Grinnz
– Grinnz, Commented Dec 12, 2018 at 23:06
Just in case this is a new project and you weren't aware, there are much better alternatives to CGI.pm even just for CGI scripts. Otherwise feel free to ignore this comment. — Grinnz
– Grinnz, Commented Dec 12, 2018 at 23:14

GMB · Accepted Answer · 2018-12-13 00:16:03Z

There is an error in the syntax of your SQL statement :

UPDATE tblclasses(classname, department, classnum, grade, credits) 
VALUES (?, ?, ?, ?, ?)
WHERE classID = $id

Should be written :

UPDATE tblclasses
SET classname = ?, 
       department = ?,
       classnum = ?,
       grade = ?,
       credits = ?
WHERE classID = ?

See the mysql docs.

Side notes (as commented also by @Grinnz) :

you should always « use strict »
you should set DBI attribute « RaiseError » to 1 on your database or statement handle(s) ; hence all DBI errors become fatal ; disabling both « RaiseError » and « PrintErrror » results in DBI neither dying on errors nor reporting them, hence you must manually check the return code of each and every DBI call to make sure that it worked - see the DBI docs
you should bind all variables in your SQL statement to void SQL injection (you did not bind $id, I changed that in the above query)

Hambone · Accepted Answer · 2018-12-12 23:58:15Z

Without knowing the DBMS I can't be 100% certain, but it appears as though you blended the syntax for an insert and an update command. The correct syntax for an update should be:

UPDATE tblclasses
set
  classname = ?,
  department = ?,
  classum = ?,
  grade = ?,
  credits = ?
WHERE classID = $id

Also, for what it's worth, you should also be able to pass the $id variable as a parameter also rather than interpolating it. This, in theory, will be kinder to the database as it will be compiling once and executing the same SQL statement over and over, only with different bind variable values:

my $check = qq{
  UPDATE tblclasses
  set
    classname = ?,
    department = ?,
    classum = ?,
    grade = ?,
    credits = ?
  WHERE classID = ?
};

my $sth = $dbh->prepare($check);
$sth->execute($className, $department, $classnum, $grade,$credits, $id);

Collectives™ on Stack Overflow

Update db row with perl cgi

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related