Perl and MySQL problem 
Author Message
 Perl and MySQL problem

hi ive got a strange problem i cant seem to work out, im trying to update a
field for a certain in a mysql database using the insert sql command, most
of the time this works fine but on occastion i find that all the field for
all the records in the table have been updated!!! Im not sure why this is
occuring, its a cgi script so is obvously occuring under certain
circumstances when a visitor does a certain thing but without knowing what
they do im kinda screwed!
heres the snippit of what appears to be the problem code

$sth=$dbh->prepare("SELECT name from downloads WHERE name LIKE '%$name%';")
|| die "Prepare failed: $DBI::errstr\n";
$sth->execute() || die "Coultdnt execute query: $DBI::errstr\n";
$foundMatch=0;
$foundMatch=$sth->rows();

#if a match is found increment the number of downloads for the file
if($foundMatch)
{
  $sth=$dbh->prepare("SELECT downloads from downloads WHERE name LIKE
'%$name%';") || die "Prepare failed: $DBI::errstr\n";
  $sth->execute() || die "Coultdnt execute query: $DBI::errstr\n";
  $downloads=$sth->fetchrow_array();
  $downloads++;
  #save the new downloads var to the database
  $sth=$dbh->prepare("UPDATE downloads SET downloads=$downloads WHERE name
LIKE '%$name%';") || die "Prepare failed: $DBI::errstr\n";
  $sth->execute() || die "Coultdnt execute query: $DBI::errstr\n";
  &printNewDownloadWindow;

Quote:
}

This section of code is only run once per cgi session so as you can see im
quite baffled how it updates every record of teh table! Espcially as seeing
theirs a few hundred records which shouldnt ever be touched for quite some
time.


Tue, 21 Oct 2003 23:33:20 GMT  
 Perl and MySQL problem

[snip]

Quote:
> ... but on occastion i find that all the field for
> all the records in the table have been updated!!!

[snip]

Quote:
> #if a match is found increment the number of downloads for the file
> if($foundMatch)
> {
>   $sth=$dbh->prepare("SELECT downloads from downloads WHERE name LIKE
> '%$name%';") || die "Prepare failed: $DBI::errstr\n";
>   $sth->execute() || die "Coultdnt execute query: $DBI::errstr\n";
>   $downloads=$sth->fetchrow_array();
>   $downloads++;
>   #save the new downloads var to the database
>   $sth=$dbh->prepare("UPDATE downloads SET downloads=$downloads WHERE name
> LIKE '%$name%';") || die "Prepare failed: $DBI::errstr\n";
>   $sth->execute() || die "Coultdnt execute query: $DBI::errstr\n";

Looks like $name is empty at some point.  How about a test like

  unless ($name) { die "name not defined..." }

--
[W]hen the manager knows his boss will accept status reports without
panic or preeemption, he comes to give honest appraisals.
                               - F. Brooks, _The Mythical Man-Month_



Tue, 21 Oct 2003 23:55:52 GMT  
 Perl and MySQL problem

Quote:

> hi ive got a strange problem i cant seem to work out, im trying to update a
> field for a certain in a mysql database using the insert sql command, most
> of the time this works fine but on occastion i find that all the field for
> all the records in the table have been updated!!! Im not sure why this is
> occuring, its a cgi script so is obvously occuring under certain
> circumstances when a visitor does a certain thing but without knowing what
> they do im kinda screwed!
> heres the snippit of what appears to be the problem code

> $sth=$dbh->prepare("SELECT name from downloads WHERE name LIKE '%$name%';")
> || die "Prepare failed: $DBI::errstr\n";

If $name has no value than '%%' in SQL will match every name, thus an
update to all records.

HTH.

--
Vinny



Wed, 22 Oct 2003 00:09:09 GMT  
 Perl and MySQL problem

Quote:
>Looks like $name is empty at some point.  How about a test like

>  unless ($name) { die "name not defined..." }

That does not test if name is empty. That tests if name is true,
but the die reports defined instead..

"true", "empty" and "defined" mean different things, and require
different tests. Your's above tests if $name is "true", err "false"
since you have inverted the "if" to an "unless".

   unless ( length $name ) ...

_That_ tests if $name is "empty" (and also does not fail if $name should
happen to have a value of "0".

   unless ( defined $name )

That tests if $name is defined.

--
    Tad McClellan                          SGML consulting

    Fort Worth, Texas



Wed, 22 Oct 2003 00:19:54 GMT  
 Perl and MySQL problem
[snip]

Mea culpa.  I plead insufficient caffeine.

--
[W]hen the manager knows his boss will accept status reports without
panic or preeemption, he comes to give honest appraisals.
                               - F. Brooks, _The Mythical Man-Month_



Wed, 22 Oct 2003 01:34:18 GMT  
 Perl and MySQL problem

Quote:

> >Looks like $name is empty at some point.  How about a test like

> >  unless ($name) { die "name not defined..." }

> That does not test if name is empty. That tests if name is true,
> but the die reports defined instead..

> "true", "empty" and "defined" mean different things, and require
> different tests. Your's above tests if $name is "true", err "false"
> since you have inverted the "if" to an "unless".

>    unless ( length $name ) ...

> _That_ tests if $name is "empty" (and also does not fail if $name should
> happen to have a value of "0".

>    unless ( defined $name )

> That tests if $name is defined.

Ok, I already responded to this since I didn't see it was posted twice.

I just want to point out that you also do not want $name to contain a '%' or
'_' unless you specifically look for those and preceed them with a '\'.
MySQL will use those in the query so if I enter '%' (or any number of '%'s
for that matter), you will still update all records in the database.

By simply testing length, you may still end up updating everything.

Shay



Wed, 22 Oct 2003 08:23:46 GMT  
 Perl and MySQL problem

Quote:

> hi ive got a strange problem i cant seem to work out, im trying to update a
> field for a certain in a mysql database using the insert sql command, most

                                                    ^^^^^^^^^^^^^^^^^^
Well, actually, you are not showing an INSERT.  You are showing an
*UPDATE*.  

Quote:
> of the time this works fine but on occastion i find that all the field for
> all the records in the table have been updated!!! Im not sure why this is
> occuring,

The only sensible reason is that the WHERE clause in your UPDATE
statement is including all of the records in the table.  

Others have mentioned that you are probably ending up with a WHERE
clause that is matching everything.  

Quote:
> its a cgi script so is obvously occuring under certain
> circumstances when a visitor does a certain thing but without knowing what
> they do im kinda screwed!
> heres the snippit of what appears to be the problem code
> $sth=$dbh->prepare("SELECT name from downloads WHERE name LIKE '%$name%';")
>|| die "Prepare failed: $DBI::errstr\n";
> $sth->execute() || die "Coultdnt execute query: $DBI::errstr\n";
> $foundMatch=0;
> $foundMatch=$sth->rows();

This is an unreliable way to know if there are any selected records.
As a matter of fact, the DBI manual page specifically says that the
only way to know how many rows are returned from a SELECT query is to
retrieve them.  It is entirely possible for a driver to return -1 from
$sth->rows() when there are no rows in the SELECTed set.  The manual
page documents this behavior.  (Actually, the DBD::mysql driver seems
to return the correct number of rows for a SELECT in my tests with
very small sets, but the DBI manual page is explicit about not relying
on that behavior.)  

  my ($foundMatch) = $sth->fetchrow_array;

This will set $foundMatch to the value of the name column in the first
selected row from the downloads table.  If no rows match the WHERE
clause, it will be set to undef.  Maybe the paranoid would now check
defined-ness to know if any rows were selected.  

Incidentally, there is another possibility here.  The fetch*() methods
can encounter errors.  If you set RaiseError to true on your call to
connect(), then this code is fine.  If not, you really should check
for an error return from fetchrow_array().  

Quote:
> #if a match is found increment the number of downloads for the file
> if($foundMatch)
> {
>   $sth=$dbh->prepare("SELECT downloads from downloads WHERE name LIKE
> '%$name%';") || die "Prepare failed: $DBI::errstr\n";

This seems convoluted.  Maybe you wanted to do the select only once
and get both columns in one go.  Why do you select from the same table
twice with the same WHERE clause?  

Quote:
>   $sth->execute() || die "Coultdnt execute query: $DBI::errstr\n";
>   $downloads=$sth->fetchrow_array();

    ($downloads) = $sth->fetchrow_array();

The fetchrow_array() method returns a list.  Your code obfuscates that
fact.  

Quote:
>   $downloads++;
>   #save the new downloads var to the database
>   $sth=$dbh->prepare("UPDATE downloads SET downloads=$downloads WHERE name
> LIKE '%$name%';") || die "Prepare failed: $DBI::errstr\n";
>   $sth->execute() || die "Coultdnt execute query: $DBI::errstr\n";
>   &printNewDownloadWindow;
> }

Why are you using LIKE in the WHERE clause?  Are there more than one
rows that you expect to be updated?  What is the key of the downloads
table?  Is it name?  Then use equality in the WHERE clause of the
UPDATE query.  That will make sure you are only updating the row that
you intend.  

I'll have a go at an *untested* alternative based on what I think
you're trying to do:

  my $sel = $dbh->prepare(qq(
          SELECT name, downloads
          FROM   downloads
          WHERE  name LIKE '%$name%'
          ));   # RaiseError catches errors

  my $updt = $dbh->prepare(q(
          UPDATE downloads
          SET    downloads = ?
          WHERE  name      = ?
          ));   # RaiseError catches errors

  $sel->execute;     # RaiseError catches errors

  while ( my ($name, $count) = $sel->fetchrow_array ) {
      $updt->execute(++$count, $name);
  }

This code doesn't use the LIKE predicate; instead it matches an exact
key.  (That will eliminate the problem you describe.)  If your table
does not have unique values for the name column, this won't do what I
think you expect.  

--
Garry Williams



Thu, 23 Oct 2003 08:15:17 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Perl and MySQL problem

2. perl/dbi/mysql problem

3. Using perl's modules DBD:Mysql and DBI and mysql

4. Using perl's modules DBD:Mysql and DBI and mysql

5. PERL-DBI-mySQL - how to not DIE on mySQL error

6. Perl MySQL access problems

7. Perl DBI and Mysql LAST_INSERT_ID() Problem

8. perl DBI / Mysql / Apache / ikonboard problem

9. Perl MySQL access problems

10. Problems with mySQL and Perl

11. Problem with Mysql from perl

12. MySql and Perl simple problem

 

 
Powered by phpBB® Forum Software