Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

DBI Query insert issue....

by SriniK (Beadle)
on Jul 03, 2012 at 16:22 UTC ( #979742=perlquestion: print w/ replies, xml ) Need Help??
SriniK has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I have used the following code to insert in to DB
$dbh->do("insert into Table (Ticket_ID,Subject,Status,Priority,Create +d_Time,Queue,Owner,Class_Type,Sent_Date) value (\'$Ticket_ID\',\'$Sub +ject\',\'$Status\',\'$Priority\',\'$Created_Time\',\'$Queue\',\'$Owne +r\',\'$Class_Type\',now());");
this code working fine for a time. But now its working if the subject($subject) contains a quote(') like $subject contain Can't get the Mail.
please help me on this.

thanks
Srinivasan

Comment on DBI Query insert issue....
Download Code
Re: DBI Query insert issue....
by runrig (Abbot) on Jul 03, 2012 at 16:28 UTC
Re: DBI Query insert issue....
by kennethk (Monsignor) on Jul 03, 2012 at 16:59 UTC
    You could manually escape your inputs using quote (as described in DBI), but that's still a lot of interpolation and kind of a mess. Cleaner would be to use Placeholders and Bind Values. For example, if I were writing your code, it might look more like
    my $sql = <<EOSQL; insert into Table ( Ticket_ID, Subject, Status, Priority, Created_Time, Queue, Owner, Class_Type, Sent_Date) values (?,?,?,?,?,?,?,?,now()); EOSQL my $query = $dbh->prepare($sql) or die $dbh->errstr; $query->execute($Ticket_ID, $Subject, $Status, $Priority, $Created_Time, $Queue, $Owner, $Class_Type, ) or die $dbh->errstr;
    Also note I corrected a typo in your SQL - in the future, make sure that what you post actually compiles/runs/demonstrates your issue, as described in How do I post a question effectively?.

    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

      Hi

      Thanks for replying...
      I thought i have given enough information about my problem.
      Anyway with ur answer i have solved my problem

      Thanks
      Srinivasan
        You did give enough information in this case, but errors in posted code frequently act as red herrings, where people trying to help get confused about what's actually at issue. As well, if posted code doesn't display exactly the errors reported, many potential helpers will skip your issue. Such an error can also be inadvertently copied into a suggested solution, resulting in difficulties. And finally, I have frequently found that the act of coming up with my example code to post tracks down exactly the issue I needed help with.

        #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Re: DBI Query insert issue....
by sundialsvc4 (Monsignor) on Jul 03, 2012 at 20:12 UTC
    Placeholders are hugely important!  The un-quoted question marks in the query string represent, in effect, variables that are consumed by the query when it runs.   Each question mark corresponds left-to-right with an entry in an array of values that is separately provided.   (There must be exactly enough.)   The values, therefore, are not “part of the SQL string” and so cannot be used to corrupt it ... thereby neatly avoiding the Bobby Tables problem.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://979742]
Approved by moritz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (11)
As of 2014-08-28 06:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (257 votes), past polls