Writing to DB with quotes

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

Hi Monks,

I'm trying to do what I thought was some pretty simple stuff, but I got thwarted by my lack of knowledge!

I'm trying to write a variable into a database.

But this variable has single quotes and it won't go.

Is there some sort of "quote" hierarchy that I don't know about ... for instance, could I use [] as quotes, () don't seem to work.

My current code is below. It works for putting strings into the database like 123, but not for stuff with quotes.

Any tips?

$trend= "<img src='images/down.jpg'>";

$sth_2 = $dbh->prepare("UPDATE options SET trend=$trend WHERE ticker =
+ '$ticker';");
    
$sth_2->execute();
[download]

Chris Herold

Comment on Writing to DB with quotes Download Code

Replies are listed 'Best First'.
Re: Writing to DB with quotes by davidrw (Prior) on Jun 29, 2006 at 00:01 UTC
use placeholders: `$sth_2 = $dbh->prepare("UPDATE options SET trend = ? WHERE ticker = ?" +); $sth_2->execute( $trend, $ticker );` [download] this also lets you reuse the prepared statement: `$sth_2->execute( $other_trend, $other_ticker );` [download]	[reply] [d/l] [select]
Re^2: Writing to DB with quotes by lhoward (Vicar) on Jun 29, 2006 at 12:12 UTC
I would like to re-iterate, use placeholders. It will get you better performance & protection from SQL injection problems. Les	[reply]
Re: Writing to DB with quotes by madbombX (Hermit) on Jun 29, 2006 at 01:37 UTC
cdherold, The DBI module comes with a method to do just this type of quoting that you are looking for. For instance: `$trend="<img src='images/down.jpg'>"; my $qtrend = $dbh->quote($trend); my $qticker = $dbh->quote($ticker); $sth_2 = $dbh->prepare("UPDATE options SET trend=$qtrend WHERE ticker += $qticker"); $sth_2->execute();` [download] It also isn't necessary to put a ';' in the prepare statement. If you check out this chapter from the O'Reilly Perl DBI reference and search for 'quotes', it will discuss escaping them.	[reply] [d/l]
Re: Writing to DB with quotes by Moron (Curate) on Jun 29, 2006 at 11:59 UTC
The only problem is that SQL needs the string to be bounded by quotes as well, not just Perl. `$trend = '"<img src=' . "'images/down.jpg'>" . '"';` [download] -M Free your mind	[reply] [d/l]
Re: Writing to DB with quotes by Anonymous Monk on Jun 29, 2006 at 13:54 UTC
omit the ; at the end, verify db column datatypes...	[reply]