http://www.perlmonks.org?node_id=1191623

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

Hi,.

I need to pass a value of a field like, abc;234.

I used my code as if ($ftype) { $Where = $Where . "AND G.FEAT_TYPE = '$ftype' "; } but it passes just abc and not abc;234

I also tried if ($ftype) { $Where = $Where . "AND G.FEAT_TYPE IN ('$ftype') "; }, but get the same result.

How do I do this?

Thanks,

Pragov

Replies are listed 'Best First'.
Re: Handling delimiters
by kennethk (Abbot) on May 30, 2017 at 19:29 UTC
    I'm guessing you are trying to assemble SQL; if this is incorrect, please provide additional context, since knowing what you are trying to pass to what is key to debugging your interface.

    If you are using DBI, it's much easier to use placeholders because the interface will handle the escaping. This will also reduce security risks and will make your code more maintainable. Your code might look something like:

    my $sql = <<EOSQL; SELECT * FROM G WHERE VAL1 = 'Y' AND G.FEAT_TYPE = ? EOSQL my $query = $dbh->prepare($sql); $query->execute($ftype);
    or, if you want something more dynamic,
    my $sql = "SELECT * FROM G WHERE VAL1 = 'Y'"; my @args = (); if ($flag) { $sql .= ' AND G.FEAT_TYPE = ?'; push @args, $ftype; } my $query = $dbh->prepare($sql); $query->execute($ftype);

    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Re: Handling delimiters
by 1nickt (Canon) on May 30, 2017 at 20:01 UTC

    Hi, in general it's a bad idea to build SQL by hand like that. As you have found, you quickly run into complications with quoting, special characters, etc.

    At a minimum you should build the SQL statement with "placeholders" and "bind values" (look for those terms in the DBI docs). But your life may be made easier if you use SQL::Abstract or SQL::Abstract::More.

    Hope this helps!


    The way forward always starts with a minimal test.
Re: Handling delimiters
by GotToBTru (Prior) on May 30, 2017 at 19:28 UTC

    Please use code tags, it makes your code so much easier to read.

    Who is "it" as in "It passes"? Is the substitution happening in Perl or in the DB driver? The semicolon is a significant character in SQL.

    But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)

      Sorry, Let me make it bit clear. I use $sql=$comFieldsV.$fromV.$Where; $comFieldsV ..I select all the fields. $fromV - the table with data $Where - I pass the filters. one of the filter conditions is  if ($ftype) { $Where = $Where . "AND G.FEAT_TYPE = '$ftype' "; } When I use a value like..abc, I get the data. But when I use abc;234 it passes only abc

      Thanks, Pragov

        You did not make it even a little bit clear, because you did not answer the question posed. Who is "it"? I think it will help you solve this problem if you figure out where the string "abc;234" is losing its second half. Hint: add

        print $Where . "\n";

        immediately after you create the string.

        Better still, look into using placeholders; they simplify things like quoting a great deal. Here's a simple example:

        my $stmt = <<'GPO'; select id_num id from reference where first_name = ? and shoe_size = ? GPO my $name = 'Howard'; my $shoe = 9; my $results_ref = $dbhandle->selectrow_hashref($stmt, undef, $name, $s +hoe); print $results_ref->{id};

        first_name is a string field, shoe_size is numeric, but the syntax for me is the same.

        But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)