Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Flexible Update SQL

by bichonfrise74 (Vicar)
on Dec 02, 2010 at 00:43 UTC ( #874784=perlquestion: print w/replies, xml ) Need Help??
bichonfrise74 has asked for the wisdom of the Perl Monks concerning the following question:


I am trying to construct a flexible update SQL so that I can just pass the parameters that I want and it will construct the update SQL correctly for me. I have the code below.
#!/usr/bin/perl use warnings; use strict; update_table( 'test_name', 'test_phone', 'test_city', 'test_country' ); update_table( 'test_name', 'test_phone', '', 'test_country' ); update_table( 'test_name', '', 'test_city', '' ); sub update_table { my ($name, $phone, $city, $country) = @_; my $sql = qq! UPDATE table SET ! . qq! phone = '$phone', ! . qq! city = '$city', ! . qq! country = '$country' ! . qq! WHERE name = '$name' !; print $sql; return 1; }
If I pass all the parameters to the subroutine, it gives me the correct update SQL. The problem starts when I start passing some null parameters.

For example, if I start passing some null parameters, the subroutine would create it like this:
UPDATE table SET phone = 'test_phone', city = '', country = 'test_country' FROM ...
The correct one that I am hoping for is this (without the city assuming that I pass a null value for this parameter):
UPDATE table SET phone = 'test_phone', country = 'test_country' FROM ...
So, to summary the problems:

  • How can I pass one or more null parameters to my subroutine and it will not add it in the SQL that it will create?
  • The subroutine should also know how to place the comma otherwise it will not be able to create the correct SQL.

  • Replies are listed 'Best First'.
    Re: Flexible Update SQL
    by Your Mother (Chancellor) on Dec 02, 2010 at 02:25 UTC

      You can do whatever you want by hand of course but this stuff gets hairy quickly and I would strongly recommend taking the learning curve hit up front for DBIx::Class or something similar. If you want a bit more more raw try SQL::Abstract.

      use SQL::Abstract; my $sql = SQL::Abstract->new; my %data = ( name => 'test_name', phone => 'test_phone', city => 'test_city', country => 'test_country' ); my ( $statement, @bind ) = $sql->update("phone", \%data); printf("SQL --> %s\n %s\n\n", $statement, join(",", @bind) ); delete @data{qw( phone city )}; ( $statement, @bind ) = $sql->update("phone", \%data); printf("SQL --> %s\n %s\n\n", $statement, join(",", @bind) ); __END__ SQL --> UPDATE phone SET city = ?, country = ?, name = ?, phone = ? test_city,test_country,test_name,test_phone SQL --> UPDATE phone SET country = ?, name = ? test_country,test_name
    Re: Flexible Update SQL
    by GrandFather (Sage) on Dec 02, 2010 at 01:24 UTC

      Use named parameters:

      #!/usr/bin/perl use warnings; use strict; update_table( name => 'test_name', phone => 'test_phone', city => 'test_city', country => 'test_country' ); update_table( name => 'test_name', phone => 'test_phone', country => 'test_country' ); update_table(name => 'test_name', city => 'test_city'); sub update_table { my (%params) = @_; die "name parameter is required by update_table ()\n" if !exists $params{name}; my @fieldNames = grep {$_ ne 'name'} keys %params; my @fields = map {"$_ = *"} @fieldNames; my $sql = "UPDATE table SET "; my @values = (@params{@fieldNames}, $params{name}); $sql .= join ', ', @fields; $sql .= " WHERE name = '*'"; print "$sql [values are @values]\n"; return 1; }


      UPDATE table SET country = *, city = *, phone = * WHERE name = '*' [va +lues are test_country test_city test_phone test_name] UPDATE table SET country = *, phone = * WHERE name = '*' [values are t +est_country test_phone test_name] UPDATE table SET city = * WHERE name = '*' [values are test_city test_ +name]

      Note too that this uses place holders which should always be used to avoid quoting issues (and injection attacks for untrusted data sources).

      True laziness is hard work
    Re: Flexible Update SQL
    by PeterPeiGuo (Hermit) on Dec 02, 2010 at 00:58 UTC

      Look up "sql place holder". The query should really be written with placeholders similar to the following:

      my $sql = "UPDATE table SET phone = ?, city = ?"

      Peter (Guo) Pei

        But this does not solve the problems:
      • passing null parameters...
      • placement of the comma...


          Actually, placeholders *do* handle the null parameter. Constructing the update statement can do the rest...

          my @fields = qw(name phone city country); my $SQL = "update table set " . join(", ", map { "$_=?" } @fields) . "where ...."; my $ST=$DB->prepare($SQL); while (...) { my @values = (.....); $ST->execute(@values); }


          When your only tool is a hammer, all problems look like your thumb.

    Re: Flexible Update SQL
    by Anonymous Monk on Dec 02, 2010 at 00:55 UTC
      One way is

      my $sql = qq! UPDATE table SET ! . ( defined($phone) ? qq! phone = '$phone', ! : '')

      Remember, you should really be using placeholders to prevent SQL injection attacks.

        Remember, you should really be using placeholders to prevent SQL injection attacks.
        There can only be SQL injection attacks if the OP would be passing in data from untrusted sources to the subroutines.

        There is, however, a much better to use placeholders. It'll take care of dealing with undef values.

        I had the same approach that you suggested but there is a problem with this... For example, if the phone and country are null, then the SQL that will be constructed will be:
        UPDATE table SET city = 'test_city', FROM ...
        This will be incorrect because of the comma after the 'test_city.'

        I am not worried about SQL injection attack since this is really something internal and will not be exposed to the outside world.

    Re: Flexible Update SQL
    by bichonfrise74 (Vicar) on Dec 02, 2010 at 18:41 UTC
      Just wanted to thank everyone for their suggestions.

    Log In?

    What's my password?
    Create A New User
    Node Status?
    node history
    Node Type: perlquestion [id://874784]
    Approved by GrandFather
    [Corion]: On the other hand, the Radeon 580 RX would cost 380 instead of 430 ... But still, that's ATi and I've been unhappy with ATi drivers the two times I had one in my desktops

    How do I use this? | Other CB clients
    Other Users?
    Others studying the Monastery: (9)
    As of 2017-08-21 08:41 GMT
    Find Nodes?
      Voting Booth?
      Who is your favorite scientist and why?

      Results (319 votes). Check out past polls.