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/</</g; $description =~ s/>/>/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 }
In reply to Re^6: escaping special characters in mysql queries
by Anonymous Monk
in thread escaping special characters in mysql queries
by Anonymous Monk
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |