in reply to Re^4: escaping special characters in mysql queries
in thread escaping special characters in mysql queries

any clues

I am just guessing (so forgive me, I might be terribly mistaken) because you don't show the code assigning that string:

You aren't using use strict; and use warnings; yet; you assign the string variable in a some way that perl treats your double quote as the string terminator - and without warnings turned on, you don't learn that before it's too late.

To know what happened, show us the code.

Cheers, Sören

  • Comment on Re^5: escaping special characters in mysql queries

Replies are listed 'Best First'.
Re^6: escaping special characters in mysql queries
by Anonymous Monk on Nov 22, 2004 at 12:10 UTC
    the code snippet is here..its actually a big one..
    if ($section eq "submit_req") 
 {
   undef $error; 

   if ($q->param('authoritylist') eq 'Select Authority') {
     $error = "<li>Please select an Authority</li>";
    }
 

    if(($error)){    

       $errorString = qq|The following fields were missing or invalid:
+<br><ul> $error</ul>| ;
       $errorSection = qq|<TABLE border="1" cellspacing=0 cellpadding=
+2 width=100% bordercolor="#B5D6FB">
                           <TR VALIGN="Top">
                             <TD BGCOLOR="#07306C" ALIGN="center">
                                  <IMG SRC="/images/error.gif" BORDER=
+"0" ALT="!" WIDTH="32" HEIGHT="32">
                               </TD>
                              <TD width=100% bgColor="#f3f8fe" class="
+text">
                                 <FONT SIZE="2"><b>$errorString</b></F
+ONT>
                               </TD>
                           </TR>
                        </TABLE>
                       |;
        $template{'error'}  = $errorSection ;
        section('next_submit1');
    exit;
    }
   

 
   undef $error; 

  #  die_nice("I have reached here "); 

   if (get_setting_2("access_submit")){
        check_user();
   } 
   else { $ignorenav = 1;  }

   my $subject     = $q->param('subject');
   my $description = $q->param('description');
   my $username    = $q->param('username');
   my $priority    = $q->param('priority');
   my $category    = $q->param('category');
   my $subcategory = $q->param('subcategory');    
   my $file        = $q->param('file');
   my $cfile       = $q->param('file');
   my $email       = $q->param('email');
   my $cc       = $q->param('cc_email');
   my $extn       = $q->param('extn');    
   my $reqForApproval = $q->param('request_for_approval');
   my $projectIn  = $q->param('project');    


     #foreach(qw/1 extn/) { $error .= qq|<li>$_</li>| if $q->param($_)
+ eq ""; }
   my $sql = "select description from perlDesk_sub_categories where su
+b_cat_id=$subcategory";
   my $st  = $dbh->prepare($sql);
   my $rt = $st->execute();         
   while(my $ref = $st->fetchrow_hashref()){
    $subject =$ref->{'description'};
   }    
   if ($q-param('email') ne "" && $q->param('email') !~ /\./ && $q->pa
+ram('email') !~ /\@/){
        $error .= "<li>Invalid email address</li>";
   }
 
   if (defined $error) { ## if 1
        if (get_setting_2("access_submit")){        
             check_user();
        } 
        else { $ignorenav = 1;  }
        print "Content-type: text/html\n\n";
        my $statement = 'SELECT * FROM perlDesk_ticket_fields ORDER BY
+ dorder'; 
        my $sth = $dbh->prepare($statement) or die "Couldn't prepare s
+tatement: $DBI::errstr; stopped";
        $sth->execute() or die "Couldn't execute statement: $DBI::errs
+tr; stopped";
        while(my $ref = $sth->fetchrow_hashref()){
            $value = $q->param($ref->{'id'});
            $template{'form'} .= qq|<tr><td width="24%">$ref->{'name'}
+</td><td width="76%">  
                                  <input type="text" name="$ref->{'id'
+}" value="$value" size="35"></td></tr>
                                 |;
        }
        $sth->finish;

        my $statement = 'SELECT level FROM perlDesk_departments ORDER 
+BY level'; 
        my $sth = $dbh->prepare($statement) or die "Couldn't prepare s
+tatement: $DBI::errstr; stopped";
        $sth->execute() or die "Couldn't execute statement: $DBI::errs
+tr; stopped";
        while(my $ref = $sth->fetchrow_hashref()){    
             $template{'category'} .= "<option value=\"$ref->{'level'}
+\">$ref->{'level'}</option>";
        }
        $sth->finish;

        $template{'uname'} =  $Cookies{'id'} || "Unregistered";
        $template{'email'}       = $q->param('email');
        $template{'subject'}     = $q->param('subject');
        $template{'description'} = $q->param('desc');
           
        $template{'error'} =  qq|The following fields were missing or 
+invalid:<br><ul> $error</ul>|;         
        parse("$global{'data'}/include/tpl/submitreq");
    }## end if 1

    else {

    #~~ 
    # No Error: Continue Processing Submission
    #~~

        $current_time = time();

        if ($username ne "Unregistered"){
            $statement    = qq|SELECT lcall FROM perlDesk_users WHERE 
+username = "$Cookies{'id'}"|; 
            $sth = $dbh->prepare($statement)or die "Couldn't prepare s
+tatement: $DBI::errstr; stopped";
            $sth->execute() or die "Couldn't execute statement: $DBI::
+errstr; stopped";
                while(my $ref = $sth->fetchrow_hashref()){
                    my $newtime = $ref->{'lcall'};
                    $newtime = $newtime + $global{'floodwait'};
                    die_nice("You can only log a call every $global{'f
+loodwait'} seconds") if $newtime > $current_time;
             }
        }

        my $edescription = $description;
        $description  =~ s/</&lt;/g;
        $description  =~ s/>/&gt;/g;
        ## If this request is to be sent for approval, store username:
+email in ownership column and insert that of the approver
         ## in the call. Change status to 'APPROVE' and send mail to t
+he approver and the user only.
        my $reqStatus = "OPEN";
        my $reqOwnerShip = "Unowned";
        my $reqUserName = $username;
        my $reqEmail = $email;
        if($reqForApproval) {
            $reqStatus = "APPROVE";
            $reqOwnerShip = qq|$username:$email|;
            $userIdToApprove = $q->param('authoritylist');
            $statement    = qq|SELECT username,email FROM perlDesk_use
+rs WHERE id = $userIdToApprove|; 
            $sth = $dbh->prepare($statement)or die "Couldn't prepare s
+tatement: $DBI::errstr; stopped";
            $sth->execute() or die "Couldn't execute statement: $DBI::
+errstr; stopped";
            while(my $ref = $sth->fetchrow_hashref()){
                $reqUserName = $ref->{'username'}; 
                $reqEmail = $ref->{'email'}; 
            }
            $sth->finish; 
        }

        my @chars =  (A..Z,0..9,a..z);
                $key   =  $chars[rand(@chars)] . $chars[rand(@chars)] 
+. $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] 
+. $chars[rand(@chars)] . $chars[rand(@chars)] . $chars[rand(@chars)] 
+. $chars[rand(@chars)] . $chars[rand(@chars)];          

        # added by kamesh to fix bug-1
        #my $stat3 = qq<mysql_real_escape_string($description)>;
        #my $sth1 =  $dbh->prepare($stat3) or die "Couldn't prepare st
+atement: $DBI::errstr; stopped";
        #my $description1 = $sth1->execute() or die "Couldn't execute 
+statement: $DBI::errstr; stopped";



#problem is here
        my $stat1 = q/INSERT INTO perlDesk_calls VALUES ( ?,?, ?, ?, ?
+, ?, ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?, ?, ?, ?, ?)/;
        my $dsth = $dbh->prepare($stat1) or die "Couldn't prepare stat
+ement: $DBI::errstr; stopped";
        $dsth->execute("NULL",$reqStatus, $reqUserName, $reqEmail, $pr
+iority, $category, $subcategory, $subject, $description, $hdtime, $re
+qOwnerShip,"Unowned",  "hd", $current_time, $current_time, "1", "0", 
+"0", "0", $key) or die "Couldn't execute statement: $DBI::errstr; sto
+pped";;
        $callid = $dbh->{'mysql_insertid'}; 



        my $sdsth = $dbh->prepare( "INSERT INTO perlDesk_activitylog V
+ALUES ( ?, ?, ?, ?, ?)" );
        $sdsth->execute( "NULL", $callid, $hdtime, "User", "Request Lo
+gged" ) or die "Couldn't execute statement: $DBI::errstr; stopped";

         ## Insert into perlDesk_users table - extn.
         $statement    = qq|UPDATE perlDesk_users set ext=$extn, proje
+ct_in="$projectIn" WHERE username = "$Cookies{'id'}"|; 
         $sth = $dbh->prepare($statement)or die "Couldn't prepare stat
+ement: $DBI::errstr; stopped";
         $sth->execute() or die "Couldn't execute statement: $DBI::err
+str; stopped";
         $sth->finish;

        ## Insert into perlDesk_user_response table.

        my $stmtQsByCatId = $dbh->prepare("select sub_cat_qs_id from p
+erlDesk_sub_cat_questions where sub_cat_id=?");
        $stmtQsByCatId->execute($subcategory) or die "Couldn't execute
+ statement: $DBI::errstr; stopped";
        while(my $ref = $stmtQsByCatId->fetchrow_hashref()){
            my $nQsId = $ref->{'sub_cat_qs_id'};
                    # die_nice($nQsId);
            my $strAns = $q->param($nQsId);
                       # die_nice($strAns);

                 my $statm = qq|INSERT INTO perlDesk_user_response VAL
+UES (?,?,?)|;
              my $stmtInsert = $dbh->prepare($statm)or die "Couldn't p
+repare statement: $DBI::errstr; stopped";
                $stmtInsert->execute($callid,$nQsId,$strAns) or die "C
+ouldn't execute statement: $DBI::errstr; stopped";
        }

        ## Check for File
        if (defined $file && $file ne ""){
            my $path2 = get_setting_2(qq|file_path|);
            my $file_name = getFileNameFromFilePath($file);
            # Copy file from the path2/temp folder to the path2 folder
            #system ('cp $path2/temp/$file_name $path2/') or print STD
+ERR "couldn't exec mv folder: $!";
            if(-e (qq|$path2/$file_name|)) {
            
                execute_sql(qq|INSERT INTO perlDesk_files VALUES ("", 
+"$callid", "$callid", "$file_name", "$path2/$file_name")|);
            }
        } 
 
        $lby = $Cookies{'id'} || "Unregistered User";

        my $statement = 'SELECT * FROM perlDesk_ticket_fields ORDER BY
+ dorder'; 
        my $sth = $dbh->prepare($statement) or die "Couldn't prepare s
+tatement: $DBI::errstr; stopped";
        $sth->execute() or die "Couldn't execute statement: $DBI::errs
+tr; stopped";
        while(my $ref = $sth->fetchrow_hashref()){    
            my $fid = $ref->{'id'};
            my $sth = $dbh->prepare( "INSERT INTO perlDesk_call_fields
+ VALUES ( ?, ?, ?, ? )" ) or die $DBI->errstr;
            $sth->execute( "NULL", $callid, "$fid", "$q->param($fid)" 
+) or die $DBI->errstr;              
        }
        $sth->finish;

        $dbh->do(qq|UPDATE perlDesk_users SET lcall = "$current_time" 
+WHERE username = "$Cookies{'id'}"|);
    my $email1= "";
        if($reqForApproval){
        # The followin two lines changed as per the database.
                # $body =~ s/\{SUBMITEDBY\}/$username/g;
                # $body =~ s/\{PROBLEM\}/$subject/;
            if ($enablemail) {
        my $sth = $dbh->prepare("SELECT name FROM perlDesk_users WHERE
+ username = ?") or die "Couldn't prepare statement: $DBI::errstr; sto
+pped";
        $sth->execute($username) or die "Couldn't execute statement: $
+DBI::errstr; stopped";
        my $name = ($sth->fetchrow_array())[0];
        $sth->execute($reqUserName) or die "Couldn't execute statement
+: $DBI::errstr; stopped";
        my $approvername = ($sth->fetchrow_array())[0];
        $sth->finish;
                my $to       = $reqEmail;
                my $from     = $email;
                my $esubject = "Request for Authorization: Ticket Id #
+".$callid.": ".$category.": ".$subject;
                my $url       = qq|$global{'baseurl'}/staff.cgi|;
                my $body = get_setting_2(qq|approval_mail_msg|);
                $body =~ s/\{TIME\}/$hdtime/;
                $body =~ s/\{USERNAME\}/  $name/g;
                $body =~ s/\{APPROVERNAME\}/$approvername/g;
                $body =~ s/\{PROJECT\}/ $projectIn/;
                $body =~ s/\{EXTN\}/   $extn/;
                $body =~ s/\{CATEGORY\}/$category/;
                $body =~ s/\{SUBJECT\}/$subject/;
                $body =~ s/\{TICKETID\}/$callid/g;
                my $url = qq|URL: $global{'baseurl'}/pdesk.cgi|;
                $body =~ s/\{URL\}/$url/;    

                email ( To => "$to", From => "$from", Subject => "$esu
+bject", Body => "$body" , Cc => "$cc"); 
            }   
        }
        else {
           $statement = 'SELECT * FROM perlDesk_staff WHERE access LIK
+E "%' . "$category" . '::%" OR access LIKE "%GLOB::%" OR access = "ad
+min";'; 
           $sth = $dbh->prepare($statement)or die "Couldn't prepare st
+atement: $DBI::errstr; stopped";
            $sth->execute() or die "Couldn't execute statement: $DBI::
+errstr; stopped";
           while(my $ref = $sth->fetchrow_hashref()) {
        $email1 .= $ref->{'email'};
                if ($ref->{'notify'} == "1") {
                    if ($enablemail) {
                        my $to       = $ref->{'email'};
                        my $from     = $global{'adminemail'};
                        my $esubject = "New HD Request: ".$category;
                        my $url       = qq|$global{'baseurl'}/staff.cg
+i|;
                        my $msg      = qq|
There is a new help desk submission

Ticket Details
------------------------------------------
Ticket ID.............: $callid
Logged by.............: $lby
Category..............: $category
Subject...............: $subject

$edescription

------------------------------------------
    
URL: $url

Thank You
|;

                            email ( To => "$to", From => "$from", Subj
+ect => "$esubject", Body => "$msg" , Cc => "$m_cc");
                    $m_cc = "";
                        } ## if enable mail
                    } ## if notify
                } # end while
        }# end else

    $sth->finish;
    close (MAILNEWTPL);
    
#Added  by kamesh to fix bug-12
 if($reqForApproval){
        $content = qq|<table width="60%" align="center"><tr><td class=
+"text"><b>$LANG{'thankyou'}</b><br><br>$LANG{'senapp'}<br><br>$LANG{'
+callid'}: <b>$callid</b><br><Br><a href=$template{'mainfile'}?do=main
+>$LANG{'subrec2'}</a></td></tr></table>|;
 }
 else {
       $content = qq|<table width="60%" align="center"><tr><td class="
+text"><b>$LANG{'thankyou'}</b><br><br>$LANG{'subrec'}<br><br>$LANG{'c
+allid'}: <b>$callid</b><br><Br><a href=$template{'mainfile'}?do=main>
+$LANG{'subrec2'}</a></td></tr></table>|;
 }
 
#end of bug-12

    if ($enablemail) 
     {
        email ( To => "$global{'pageraddr'}", From => "$global{'admine
+mail'}", Subject => "URGENT SUPPORT REQUEST", Body => "Priority 1 Tic
+ket Logged - ID $callid - User $email" ) if $global{'pager'} && $q->p
+aram('priority') == "1";

       #my $body;
        #    open (MAILNEWTPL,"$global{'data'}/include/tpl/newticket.t
+xt");
        #        while (<MAILNEWTPL>) {
        #          lang_parse() if $_ =~ /%*%/;
        #          if ($_ =~ /\{*\}/i) 
        #            { 
         #            s/\{baseurl\}/$global{'baseurl'}/g;
        #                s/\{name\}/$lby/g;
        #                s/\{subject\}/$subject/g;
        #                s/\{description\}/$description/g;
        #                s/\{mainfile\}/$template{'mainfile'}/g;
        #                                 s/\{lang\}/$language/g;
        #                s/\{date\}/$hdtime/g;
        #                s/\{key\}/$key/g;
        #                s/\{id\}/$callid/g;
        #            }            
        #               $body .= "$_";
        #          }
        #       close(MAILNEWTPL);

       # my $mail_subject = "\{$global{'epre'}-$callid\} Help Desk Sub
+mission";
        my $mail_subject = "\{Ticket: $callid\} $subject";
my $body = get_setting_2(qq|new_ticket_mail_msg|);
$body =~ s/\{FROM\}/$global{'adminemail'}/;
$body =~ s/\{DATE\}/$hdtime/;
$body =~ s/\{USERNAME\}/$username/g;
$body =~ s/\{CATEGORY\}/$category/;
$body =~ s/\{PROBLEM\}/$subject/;
$body =~ s/\{TICKETNO\}/$callid/;
$body =~ s/\{VIEWKEY\}/$key/;
my $url = qq|URL: $global{'baseurl'}/pdesk.cgi?do=view&cid=$callid&tic
+ket=$key|;
$body =~ s/\{URL\}/$url/;    
#Commenting the admin email
           email ( To => "$email", From => "$global{'adminemail'}", Su
+bject => "$mail_subject", Body => "$body" );
      }    

     $template{'response'} = $content;
 
     print "Content-type: text/html\n\n";
     parse("$global{'data'}/include/tpl/general");

   } # End No Error

  }

    [download]

    i have marked the probable error place with #problem is here..i hope it will help u understanding the code..
[reply]
[d/l]

      That was a lot of code... and I did not quite understand it all.
      Could you identify the variable names that contain data that later turns out mangled?

      $sth->execute( "NULL", $callid, "$fid", "$q->param($fid)" )

      Here, and in other places too, the use of "" around variables is unnecessary.
      At "$q->param($fid)" it is definitely wrong and will do something unexpected. Leave them out where you can.
      There are problems with MySQL in particular where using them might speed up your statements, but that's hearsay for now, I haven't checked for sure.

      There's a bit of a security risk where you are using CGI form input as database input.
      You are using placeholders in the sql statement processing, that's good, it's doing at least something to prevent you from sql insertion;
      but it would make me feel happier if you tested for some expected/unexpected content of that input before you pass it to the database.

      Cheers, Sören

[reply]

In Section Seekers of Perl Wisdom