Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

DBI and some complicated quotes

by Cody Fendant (Friar)
on Sep 21, 2012 at 04:04 UTC ( #994803=perlquestion: print w/replies, xml ) Need Help??
Cody Fendant has asked for the wisdom of the Perl Monks concerning the following question:

I want to replace some things in MySQL fields.

Unfortunately those things are quotes.

So I have a statement like this:

UPDATE table_name SET `fieldname` = REPLACE(`fieldname`,'','''')

which replaces a "smart quote" type apostrophe with a regular one (MySQL's way of escaping a quote is to double it).

That statement you can see above works from the command line, but not if I use DBI's do() function.

Any suggestions? Any way in which I can pass in quotes without having problems with DBI quoting the quotes around the quotes?

Any other way to attack this problem?

Replies are listed 'Best First'.
Re: DBI and some complicated quotes
by davido (Archbishop) on Sep 21, 2012 at 04:13 UTC

    You need to look at the section in the DBI documentation called "Placeholders and Bind Values"

    my $sth = $dbh->prepare( 'UPDATE table_name SET `fieldname` = REPLACE( + `fieldname`, ?, ? )' ); $sth->execute( q{}, q{'} );

    ...this assuming you're correct when you say it works from the command line. You may have to check your database-specific DBD::* POD to see if there are any particulars you need to know.


      I think you really meant...

      $sth->execute( q{}, q{'} );
      perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'

        Oh....., I suppose. ;) (Updated now)


Re: DBI and some complicated quotes
by tobyink (Abbot) on Sep 21, 2012 at 08:56 UTC

    "MySQL's way of escaping a quote is to double it."

    Actually, that's not something specific to MySQL; it's part of the SQL standard.

    davido's answer is a good one. In the spirit of "there's more than one way to do it"...

    $dbh->do(<<'GO'); UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,'','''') GO

    ... or ...

    $dbh->do("UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,'','' +'')");

    ... or ...

    $dbh->do('UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,\'\', +\'\'\'\')');

    ... or ...

    $dbh->do(q(UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,'',' +''')));

    Lastly, if you've got non-ASCII characters in your source code (such as your "smart quote"), make sure your editor is set up to save your script as UTF-8; and make sure you include the pragma use utf8 somewhere near the top of your script.

    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://994803]
Approved by GrandFather
[marto]: did that pg-GPU engine ever go mainstream?
[erix]: pgstrom? no, I don't think so
[erix]: although I have to admit its project page looks reasonable uptodate

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (11)
As of 2018-05-24 14:10 GMT
Find Nodes?
    Voting Booth?