in reply to Problem constructing an SQL query with LIKE clauses

...input type='hidden' name='sqlc' value='$SQLCommand'

If your script is for general consumption, I hope you're using -T and are untainting $SQLCommand. Otherwise, someone can slip something truly evil into your SQL stream.

I recommend that you use bind variables in the query, like 

  $SQLCommand = <<SQL;
  SELECT Client, Product, License, Issue, Data, Id, Call
  FROM support
  WHERE Client LIKE ?
    AND Product LIKE ?
    AND Issue LIKE ?
    AND Cause LIKE ?
    AND Call LIKE ?
    AND SupportDetails LIKE ?
  SQL

[download]
Then, when you execute the prepared query, you can wrap the variables. You'll need some logic to do it, so that you don't end up with '%%' if the variable is "". I do something like 
  $sth->execute(like_arg(1, $Client, 1),
                ... etc ... );

[download]
I'll leave like_arg() as an exercise.

Replies are listed 'Best First'.
Re: Re: Passing data from one perl script to another.
by sdyates (Scribe) on Apr 17, 2002 at 00:49 UTC
    Ok, I must be really tired and am not seeing the obvious. I did not think I would need to use placeholders here, but can obviously see the advantage. I was hoping to avoid changing the entire script.

    you have $STH->execute (like_arg(....
    I tried $sth->execute($Client,$Product,....)
    and it failed. I tried looking for like_arg and realise it must be something obvious I cannot grasp. I guess I have been going at this too long... Trying to get this ready for tomorrow. DId not see my bug until end of day and need the functionality. I also thought of adding \ into the string, but realise that would be sloppy and I do not want that.

    I am ready for the next tid bit of info if you are willing to forward it
    Simon

[reply]
      Mea culpa for being obscure. By "left as an exercise" I meant that like_arg() was a piece that you would need to supply yourself.

      The idea is to form the wildcard string at runtime. It will look something like the following (from memory): 

        sub like_arg {
      my ($prewild, $string, $postwild) = @_;
      return "%" if $string eq "";
      return "%" . $string . "%" if $prewild and $postwild;
      return "%" . $string if $prewild;
      return $string . "%" if $postwild;
      "";
  }

      [download]
[reply]
[d/l]
[select]
Re: Re: Passing data from one perl script to another.
by sdyates (Scribe) on Apr 17, 2002 at 01:46 UTC
    Here is more of the script in question: . . 
    } elsif ($Product) {
        sql();
        $port = $Product; $Field = 'Product';
        ProcessSQL();

.
.
.
sub sql {
        $SQLCommand = <<SQL;
        SELECT Client,Product,Issue,Cause,Date,id,Call
        FROM support
        WHERE Client LIKE ?
                AND Product LIKE ?
                AND Issue LIKE ?
                AND Cause LIKE ?
                AND SupportDetails LIKE ?
                AND Date LIKE ?
SQL
}

.
.
.

sub ProcessSQL {
        ExecuteSQL();
        print;
        close_dbi($dbh);
}
.
.
.
sub ExecuteSQL {
        $dbh = open_dbi();
        print "<body bgcolor='#FFFFCC'>
        <TABLE BORDER=0 width=840 cellpadding=2 align=center bordercol
+or='#FF9933' bgcolor='#FF9933'>
<TR><TH><font face=Arial size=2>Edit</TH><TH><font face=Arial size=2>R
+eg. No.</TH><TH><font face=Arial size=2>Product</TH><TH><font face=Ar
+ial
 size=2>Issue</TH><TH><font face=Arial size=2>Cause</TH>
                <TH><font face=Arial size=2>Date</TH><th><font face=Ar
+ial size=2>Case</th></TR>
        <font face='Courier'>
        <form name='form1' method='POST' action='ISeeSupportViewDetail
+s.pl'>
        <p>";
        my $sth = $dbh->prepare("$SQLCommand") or err_trap("Cannot pre
+pare the load_category_names query");
        $sth->execute($Client,$Product,$Issue,$Cause,$SupportDetails,$
+Date) or err_trap("Cannot execute the SQL query to SQL COMMAND");
        $i=1;
        while (($Client,$Product,$Issue,$Cause,$Date,$filler,$Call) = 
+$sth->fetchrow_array) {
                print "<Tr>
                <td width='15' height='20' bordercolor='#FFFFCC' bgcol
+or='#FFFFCC'>";
                print ("<input type='radio' name='radio' value='$i' >"
+);
                print "</td>
                <td width='60' height='20' align='left' bordercolor='#
+FFFFCC' bgcolor='#FFFFCC'><font face=
                        'Arial' size='1'>&nbsp;$Client</td>
                <td width='100' height='20' align='left' bordercolor='
+#FFFFCC' bgcolor='#FFFFCC'><font face=
                        'Arial' size='1'>&nbsp;$Product</td>
                <td width='220' height='20' align='left' bordercolor='
+#FFFFCC' bgcolor='#FFFFCC'><font face=
                        'Arial' size='1'>&nbsp;$Issue</td>
                <td width='220' height='20' align='left' 
.
.
.

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom