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

Happy Holidays!

I think $slurping too much wine this weekend has dulled my abilities once again.

I'm trying to accept an array (actually multiple arrays but I'm trying to simplify the process first so I can understand it) from a form, properly quote each value and place it into a DB. Here's my feeble attempt:

my @quantity = param('quantity');
my $item_number = param("item_number");
#my @item_number = param("item_number");Not used until I understand ho
+w to pass one array in first. 

    my $quantity = $dbh->quote( $quantity );
    my $item_number = $dbh->quote( $item_number );

    foreach $quantity (@quantity ) {     
    
    my $sql = qq/
    INSERT INTO needed_details (quantity, item_number) VALUES ($quanti
+ty, $item_number)
     /; 
    my $rv = $dbh->do( $sql );   
    }

[download]

When I put two values into the array (e.g. @quantity =2,1), I get four entries into the DB. Two duplicates entries. Please help me understand my mistake. TIA

peppiv

Replies are listed 'Best First'.
Re: Accept array from Form and put in DB
by fruiture (Curate) on Dec 09, 2002 at 15:27 UTC

    The problem in your code is that $quantity appears to be undecleared when you first use it. strict has surely told you already.

    If you want do do something on every element of a list but you only have the code to do it on a scalar, use map:

    @quantity = map $dbh->quote($_) , @quantity.

    [download]

    And unrelated but important: check the return value of do() in order to notice errors. Forget these words if you have RaiseError/PrintError enabled.

    --
    http://fruiture.de
[reply]
[d/l]
[reply]
[d/l]
[select]
Re: Accept array from Form and put in DB
by slife (Scribe) on Dec 09, 2002 at 15:52 UTC

    Note that you should be extremely careful when accepting user data like this; always use Taint mode and launder user input very carefully. See the discussion in Ch 23 of Camel 3rd Ed. 'Detecting and Laundering Tainted Data'.

    Secondly, whenever you need to process SQL in a loop like this, consider using the prepare/execute idiom: 

    my $sth = $dbh->prepare(<<SQL)
INSERT INTO needed_details (quantity,item_number)
VALUES (?,?)
SQL
# Error-check here.

foreach my $quantity (@quantity) {
    $sth->execute($quantity,$item_number);
    # Error check here.
}

    [download]

    At the very least, this will save you the need to explicitly quote your data; if you envisage inserting many rows in a given program execute, this will save you the cost of the DBMS re-compiling the query on every pass (for those DBMSs that support query preparing).

[reply]
[d/l]
      Sorry, meant to point out that I'm using FreeTDS connecting Linux/Apache to Win2K/SQL Server 7. Placeholders don't work in this configuration (yet).

      I try not to clutter the page and make the post as brief as possible. But yes, I do use Taint and every other security measure I can think of. I also use strict and I declared the variable. Thanks for the help.

[reply]
Re: Accept array from Form and put in DB
by peppiv (Curate) on Dec 09, 2002 at 16:00 UTC
    Update
    When I put two values into the array (e.g. @quantity = [2,1]), I get four entries into the DB. Two duplicates entries. Please help me understand my mistake. TIA
[reply]
[d/l]
      The expression @quantity = [2,1] does not assign two values into the array, it instead assigns an anonymous array reference which contains two values into the array. Did you mean @quantity = (2,1) instead? Here is some more code to play with: 
      use strict;
use Data::Dumper;

my @wrong = [2,1];
my @right = (2,1);
my $ref   = [2,1];

print 'right: ', Dumper \@right;
print 'right: ', Dumper $ref;
print 'wrong: ', Dumper \@wrong;

      [download]
      Update: this is a common pitfall that probably all of us have stumbled on at least once ... i sure have!

      jeffa

      L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
        Yes, I did mean to type (2,1). I'm having a colorless day.

        I did enjoy your triplet paradiddle with high-hat. Fortunately being a guitar player, I only have to tap my foot, hold a single note and raise my pick hand in the air. (Hey I was an 80's rocker -> gimme a break).

[reply]

Back to Seekers of Perl Wisdom