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

I have been racking my brain for hours here. I know where the problem is occuring, and think I know why, but cannot hink of why.

Here is the string I want to pass: $SQLCommand = "SELECT Client,Product,Issue,Cause,Date,id,Call FROM support WHERE Client like '%$Client%' and Product like '%$Product%' and Issue like '%$Issue%' and Cause like '%$Cause%' and Call like 'SWAT%' and SupportDetails like '%$SupportDetails%'"; At the end of the script, when I have those variables filled, I then use:  ...input type='hidden' name='sqlc' value='$SQLCommand' However, when I print the variable sqlc in the other script, all I get is: SELECT Client,Product,Issue,Cause,Date,id,Call FROM support WHERE Product like Is there a perl procedure I should use to "wrap" the variable so the special charaters will not get lost? Or am I completely lost on this one?

Please advise me

simon

Edit by dws for formatting and appropriate title.

Replies are listed 'Best First'.
Re: Passing data from one perl script to another.
by dws (Chancellor) on Apr 16, 2002 at 22:51 UTC
    ...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.

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

Back to Seekers of Perl Wisdom