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

when i read this value it gives 'i haven\'t attended'

If that's the case, then at the time of writing you inserted \'i haven\\\'t attended.\' to the database;
meaning it was still quoted twice. I assume that you either have another $dbh->quote() on that value somewhere in your code - or you are looking at old data in the database, not at the freshly inserted data.
Did you check if the new insert succeeded or maybe failed for some reason?

The data retrieved from the database (and not quoted after retrieving) should give i haven't attended - no backslashes.

Update:

in the database the value is stored as: i haven\

That data is corrupted. It was inserted as "i haven\\'" - the "'" survived and was interpreted as string terminator because it was oddly quoted: the quoting did not affect it, it only quoted the other backslash. That's strange, it looks like manual tampering to me. If that's not the case, I am at a loss here =(

Cheers, Sören

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

Replies are listed 'Best First'.
Re^4: escaping special characters in mysql queries
by Anonymous Monk on Nov 22, 2004 at 11:28 UTC
    Thanks..for the help
    i have one more question..
    if my string value is: be prepared! you can't do that "may be i will do it".
    the value being stored is: be prepared! you can't do that
    the value is being truncated..any clues..
    thanks
    kamesh

      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

        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 }

        i have marked the probable error place with #problem is here..i hope it will help u understanding the code..
      just an update.. if my string value is: be prepared! you can't do that 'may be i will do it'.
      it is giving output as expected..i.e full string..

        It seems I was guessing somewhat right then.
        Try assigning your string with the q// quoting operator; example:

        ... = q/be prepared! you can't do that "may be i will do it"./

        Turn on warnings, too.

        Cheers, Sören