text entry into mysql using perl

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

my dearest brother monks,

I am having trouble writing to a mysql database only when the text I'm trying to enter contains an " ' " (an apostrophe).
The connection is fine and I can easily write the time and date to the database, but when I put text with apostophe's (<---like that one) into my scalar $body or $header it crashes.

Below is the code I'm using to write into the database. Values for each scalar were defined earlier in the program.

$dsn = "DBI:mysql:database;localhost";
           
$dbh = DBI->connect($dsn,'user','password');
$sth = $dbh->prepare("INSERT INTO press_releases (time,date,ticker,hea
+der,body) VALUES ('$time','$date','$ticker','$header','$body');");
[download]

Example $body scalar that would instigate a crash would be: "I don't care."

how do i get the mysql to accept these scalars without crashing?

thanks,
cdherold

Comment on text entry into mysql using perl Download Code

Replies are listed 'Best First'.
(ar0n: use placeholders) Re: text entry into mysql using perl by ar0n (Priest) on Apr 16, 2001 at 05:23 UTC
One: use the DBI `quote` method: `my $sql = $dbh->quote($foo); $dbh->do($sql);` [download] Two: use placeholders: `my $sth = $dbh->prepare("INSERT INTO foo (bar, baz, quux) VALUES (?, ? +, ?)"); $sth->execute($bar, $baz, $quux);` [download] I prefer option two. Read <code>perldoc DBI</code for more info on placeholders. [ ar0n ]	[reply] [d/l] [select]
Re: (ar0n: use placeholders) Re: text entry into mysql using perl by cdherold (Monk) on Apr 16, 2001 at 05:45 UTC
I did what you said `$sth = $dbh->prepare("INSERT INTO press_releases (time,date,ticker,hea +der,body) VALUES (?,?,?,?,?);"); $sth->execute($time, $date, $ticker, $header, $body);` [download] which worked beautifully. Only thing is it worked twice, so I got the same thing written into the database two times. Did I do something wrong?	[reply] [d/l]
(ar0n) Re (2): (ar0n: use placeholders) Re: text entry into mysql using perl by ar0n (Priest) on Apr 16, 2001 at 05:50 UTC
Only thing is it worked twice, so I got the same thing written into the database two times. Did I do something wrong? Most likely. Are you using this in a loop? The statements above work fine. [ ar0n ]	[reply]
Re: text entry into mysql using perl by petethered (Pilgrim) on Apr 16, 2001 at 10:29 UTC
An real easy way to fix this problem is to simply alter your var Example: $body =~ s/([\'\`])/$1$1/gi; This covers both forms of apostrope, doubleing it which is insert friendly ( only one gets inserted ) Since im one of those people who like to reinvent the wheel ( or better put carry my blueprints around ) for form_parsing, i just make the conversion automagically while my forminput is being stuffed into my arrays. $value =~ /([\`\'])/$1$1/gi; Pete	[reply] [d/l] [select]