unable to update in table Databases forum at WebmasterWorld

Welcome to WebmasterWorld Guest from 23.20.196.179
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,

Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases

Forum Library : Charter : Moderators: physics & whoisgregg
Databases Forum

unable to update in table

Geeklady

msg:4293084

12:06 pm on Apr 6, 2011 (gmt 0)

hello guys, am failing to update to my table in a mysql database. I am able to insert and delete but some how update does not want to work. I have tried binding it but still does not want to work. Here is the update.cgi code.

#!/usr/bin/perl -w

use CGI;
#use CGI::Carp qw(fatalToBrowser);
use DBI;
$query = CGI->new();

print "content-type:text/html\n\n";

$database_name="DBI:mysql:messageboard";
$db_username="root";
$db_password="root";

my $id=$query->param('ID');
#my $name=$query->param('Name');
my $password=$query->param('Password');
my $details=$query->param('Message');

$dbh=DBI->connect($database_name,$db_username,$db_password) || die "Connection to database failed:$DBI::errstr\n";
$sql="UPDATE messages SET message = '$details' WHERE id = '?'";
$sth=$dbh->prepare($sql);
$sth->execute || print $sql;

print "<html>";
print "<div align=left>";
print "<form method=\"POST\" action=\"/cgi-bin/display.cgi\" onsubmit=\"return check(Myform)\">";
print "<head>";
print "<title>Confirmation</title>";
print "</head>";
print "<body>";
print "<center><b><i>Record Updated!</i></b><br><br>";
print "<center><input type=\"submit\" value=\"View\" name=\"View\"></center>";
print "</div>";
print "</body>";
print "</html>";

eelixduppy

msg:4293164

2:53 pm on Apr 6, 2011 (gmt 0)

WHERE id = '?'

Seems you are missing the actual ID value for the row to update. Also, make sure you have update permissions for that username on that table.

Geeklady

msg:4293206

3:55 pm on Apr 6, 2011 (gmt 0)

i did use WHERE id = '$id'; but it still does not want to update. How do i put a permission to the table to be able to update?

rocknbil

msg:4293210

3:59 pm on Apr 6, 2011 (gmt 0)

Change this

$sql="UPDATE messages SET message = '$details' WHERE id = '?'";

to this

if (($id =~ /^\d+$/) && ($id > 0)) {
$sql="UPDATE messages SET message = '$details' WHERE id = $id";
}
else { die("No id to update"); }

Note
- the removal of quotes - you do not need to quote numeric values. If your id field is varchar or otherwise non numeric, leave them in.
- The if /else does something else very important, makes sure we're querying with a number. There should be more filtering on this to insure it's **this** user, but it's a start (toward cleansing input).

Additionally I'd change this

$sth->execute || print $sql;

to this

$sth->execute || die("Could not update at line 16");

Reason being, displaying an sql statement in a public script could reveal details about your database to potential hackers. It's OK to leave it as is for testing, but these things have a way of never getting updated in final deployment. :-)

Edit:

i did use WHERE id = '$id'; but it still does not want to update. How do i put a permission to the table to be able to update?

I didn't see this (must have cross-posted.) If the user doesn't have permissions, you'd likely see the "print $sql" statement (execute OR DO THIS.) Anyway that is set in the mysql command line or wherever you created the database.

For debugging only (note comments above) you can change your execute like so:

$sth->execute || print $db->errstr();

This would display the error returned by mySQL. However, knowing you put ID in I think if you add my error trap, you are more likely to see "No ID to update." This being the case, you'd need to find out why

my $id=$query->param('ID');

is not populating $id - is there a hidden field named ID actually in your form?

Geeklady

msg:4295321

10:53 am on Apr 11, 2011 (gmt 0)

rocknbil, i tried what you suggested but it cant update at line 16 if i dont put any quotes for $id. If i do put quotes, it does not want to execute.

LifeinAsia

msg:4295423

3:40 pm on Apr 11, 2011 (gmt 0)

What are you passing as the details and ID values?

rocknbil

msg:4295432

3:50 pm on Apr 11, 2011 (gmt 0)

but it cant update at line 16 if i dont put any quotes for $id.

Exactly . . . $id is null. You need to figure out why, see previous comments.

You can add this right after setting ID:

my $id=$query->param('ID');
print "Incoming ID is $id";

But it will most likely be that it looks like this

Incoming ID is

because it's null (or empty). If it's not, it may even be that there's no field in the database named "id", you may have it named "ID", or that you are referencing a non-existent table name (the select statement is otherwise fine.)

In both cases - in your **form** or as a database field name - "ID" and "id" are two different references/names.

You can use the $db->errstr() line I gave you to find out more info from mysql as to why it's dieing there.

Geeklady

msg:4296787

10:41 am on Apr 13, 2011 (gmt 0)

thankyou, guys for your help and suggestions. even though the update still did not want to work ;) i submitted my assignment and my lecturer deducted a few marks for not being able to update. I will still try and figure it out why it was giving a null value for the update field for future work. :)

rocknbil

msg:4296938

3:39 pm on Apr 13, 2011 (gmt 0)

Application debugging must come in chapter five. How backwards. :-)

LifeinAsia

msg:4296946

3:59 pm on Apr 13, 2011 (gmt 0)

i submitted my assignment

Not cool to ask us to debug your homework for you (especially when you didn't identify it as such). :(

Geeklady

msg:4296954

4:19 pm on Apr 13, 2011 (gmt 0)

whoa! am sorry if you feel that way guys, but i thought these forums are there to help each other out on different codes and things? if you feel like i used you to debug my code then am sorry, but i am new to perl and i was stuck and trying to get help not to get people to do my work. Once again sorry if i offended you for asking for help. :(

LifeinAsia

msg:4296978

5:06 pm on Apr 13, 2011 (gmt 0)

(I am speaking for myself, not necessarily for others.) I am not offended by being asked for help- I regularly offer help here, which is one of the reasons I became a moderator. I am offended by being asked for help on homework when it wasn't labeled as such.

I have no problem giving some help with homework in addition to real-world issues- I just like to know ahead of time when I'm doing it.

rocknbil

msg:4297726

5:58 pm on Apr 14, 2011 (gmt 0)

It's not so much how posters feel about it (really,) it is in the TOS as I mentioned in the Perl forum. But no matter - most of us will help you fix it but not revise it and repost it, which is mostly what happened here, so it's all good.

To really learn a valuable lesson from it, figure out why $id is null. :-)

pontifex

msg:4297743

6:25 pm on Apr 14, 2011 (gmt 0)

and do escape the params you get from the URLs, otherwise your mysql is wide open for a hacking attack... :-)

Global Options:

top

home

open messages

active posts

Home / Forums Index / Code, Content, and Presentation / Databases

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved

Databases Forum