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

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

Hey folks,

I'm using a hash, %user_info, to make an update to a a database I've built. I have already connected to the database just fine. 

    $sth = $dbh->prepare("
            Update tUser
            Set ? = ? 
            Where UserId = $user_info{UserId}
            ");

    foreach $key(keys %user_info)
        {
        if ($key ne "UserId")
            {
            $sth->execute($key, $user_info{$key});
            }
        }
    $sth->finish();

[download]
I get the error: 
DBD::mysql::st execute failed: You have an error in your SQL syntax ne
+ar ''LastName' = 'Smith'
                        Where UserId = 1
                        ' at line 2 at /usr/usr_update line 197.

[download]
I'm positive that it's because the field is in quotes. Is there a better way to do this, or is there a command to take the quotes away?

Thanks in advance,
t

Replies are listed 'Best First'.
Re: DB Update w/ Hash
by athomason (Curate) on Sep 05, 2000 at 05:53 UTC
    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();

    [download]
[reply]
[d/l]
[select]
      What does taint-check mean?
[reply]

        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/>
[reply]
      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

[reply]
Re: DB Update w/ Hash
by ferrency (Deacon) on Sep 05, 2000 at 19:53 UTC
    I believe MySQL doesn't like to allow you to bind to column names.

    update mytable set ? = 'blah'; # disallowed
update mytable set myfield = ?; # allowed

    [download]
    Also, looping over multiple fields and executing one query for each is most likley going to be a huge performance loss. You'd probably be better off constructing one query, even if you need to prepare it every time. You might try something like this:

    my %user_info;

my $query = "update tUser Set ".
    (join ", " map {"$_ = $user_info{$_}"} keys %user_info).
    " where UserID = $user_info{UserID}";

    [download]
    This uses interpolation instead of query binding. If you always specify your mysql columns in the same order, you could use binding instead:

    # Warning, untested code, to be used for inspiration only

my %user_info;

my $query = "update tUser Set ".
    (join ", " map {"$_ = ?"} sort keys %user_info).
    " where UserID = $user_info{UserID}";
my $sth = $dbh->prepare($query) or die;
$sth->execute(map {$user_info{$_}} sort keys %user_info);

    [download]
    Alan
[reply]
[d/l]
[select]
Re: DB Update w/ Hash
by agoth (Chaplain) on Sep 05, 2000 at 13:01 UTC
    I would wade through 
    perldoc DBI

    [download]
    but I have always assumed, based on vague memory of having read it somewhere once that ? placeholders were for values only.
    If you use ? for a column name then the SQL engine is going to have to re prepare your statement anyway isnt it?? therefore defeating the object of using the placeholder.

[reply]
[d/l]
Re: DB Update w/ Hash
by Jonas (Beadle) on Sep 05, 2000 at 06:32 UTC
    Thanks a lot.

    I had just tried something similar to that. And I'll be sure to taint check it.

[reply]

Back to Seekers of Perl Wisdom