DBI and some complicated quotes

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?

Comment on DBI and some complicated quotes Select or Download Code

Replies are listed 'Best First'.
Re: DBI and some complicated quotes by davido (Cardinal) 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{'} ); [download] ...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. Dave	[reply] [d/l]
Re^2: DBI and some complicated quotes by tobyink (Canon) on Sep 21, 2012 at 08:59 UTC
I think you really meant... `$sth->execute( q{‘}, q{'} );` [download] `perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'`	[reply] [d/l]
Re^3: DBI and some complicated quotes by davido (Cardinal) on Sep 21, 2012 at 09:09 UTC
Oh....., I suppose. ;) (Updated now) Dave	[reply]
Re: DBI and some complicated quotes by tobyink (Canon) 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 [download] ... or ... $dbh->do("UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,'‘','' +'')"); [download] ... or ... $dbh->do('UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,\'‘\', +\'\'\'\')'); [download] ... or ... $dbh->do(q(UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,'‘',' +'''))); [download] 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'`	[reply] [d/l] [select]