Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

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
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2017-11-23 19:21 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (337 votes). Check out past polls.