http://qs321.pair.com?node_id=31074


in reply to DB Update w/ Hash

I doubt it will solve your problem, but I think you're getting bitten earlier on by a logic flaw. When you construct $sth, $user_info{UserId} is immediately interpolated. And that means that UserId will always be whatever it is at that point, and not the appropriate individual value from %user_info. You should be using a placeholder for $user_info{UserId}, too. The quoting issue seems odd, since the driver should automatically quote the placeheld value correctly (and it appears to be, by the query returned). Perhaps you could try interpolating them yourself with $dbh->quote, or supply us with the actual values of the variables in question if that doesn't work.

Update:

I played around with mysql a bit, and it appears that it doesn't like it when you quote the column name in the SET part of an UPDATE. Quoting fields works in WHEREs and maybe other places, so this may be a bug (I don't see it documented anywhere). Since placeholders automatically quote their values, I suppose you can't use placeholders here. Which will leave you with a performance hit, sadly, as you'll need to reinterpolate a new statement handle each time. But beware! If %keys comes from an untrusted source, you'll need to very carefully taint-check the fields. The following code should at least function.

foreach $key(keys %user_info) { next if $key eq "UserId"; $sth = $dbh->prepare(" Update tUser Set $key = ? Where UserId = ? "); $sth->execute($user_info{$key}, $user_info{'UserId'}); } $sth->finish();

Replies are listed 'Best First'.
RE: Re: DB Update w/ Hash
by Anonymous Monk on Sep 06, 2000 at 14:54 UTC
    What does taint-check mean?

      Taint-checking means using Perl with the -T command line flag. This puts Perl into a mode where it assumes that all data from the outside world is 'tainted' and it won't let you use it until you'll cleaned it up.

      This is a good thing.

      See perldoc perlsec for more details.

      --
      <http://www.dave.org.uk>

      European Perl Conference - Sept 22/24 2000, ICA, London
      <http://www.yapc.org/Europe/>
RE: Re: DB Update w/ Hash
by Jonas (Beadle) on Sep 05, 2000 at 06:04 UTC
    The reason I have the $user_info{UserId} set statically is I'm only changing one user at a time. Thus the conditional in the foreach loop.

    I'll try the $dbh-quote, though.

    Thanks