I was having insane problems trying to CONCAT text during a database UPDATE to a text field. I want to use the database's (mysql in my case) CONCAT function because the data could be large & only wanted to pass in new lines & not the entire contents of the column.
So for a column called output I would CONCAT to it like so:
$row->update( { output => \"CONCAT(output, $new_stuff)" } );
Which is groovy as long as $new_stuff didn't contain single quotes or question marks. Single quotes throw off database quoting and question marks throw off DBI...
So there's a 'quote' function available via DBI which takes care of the single quotes - usually - but completely ignores question marks. Look at it in DBI.pm - it's pretty, um, bare-bones.
So I would call DBI's quote function & then use an ugly regex to prepend all question mark's that didn't already begin with a backslash with a backslash - and this worked - mostly.
There were several problem that would crop up intermittently & of course always at the exact wrong time. If a single quote was already preceded by a backslash calling DBI's quote made it worse & the update would fail (note DBI's quote function replaces all single quotes with 2 single quotes & then puts single quotes around the entire string.)
I also had problem with my regex seemingly not working in all cases to properly hide the question marks from DBI & would get errors occasionally about providing too many or too few parameters for the given number of question marks.
Welp after just dealing with the errors that would occasionally pop up I finally got off my butt & came up with a quoting fix that so far as been 100% reliable for any combinations of single quotes, question marks, & backslashes before them.
The secret is to totally get rid of those character & replace them with char(39) and char(63) for single quote and question mark respectively.
The deal is to delete any backslashes before any single quote or question marks in the output, the replace those character with their 'char' equivalents in-between single quotes and commas (as CONCAT can take multiple values).
Here's what it looks like:
sub concat_db {
my($row, $column, $value) = @_;
# get rid of an backshlased single quotes
$value =~ s/\\'/'/g;
# clean up single quotes
$value =~ s/'/', char(39), '/g;
# clean up '?'s
$value =~ s/\\\?/?/g;
$value =~ s/\?/', char(63), '/g;
# concat it
$row->update({ $column => \"CONCAT($column, '$value')" });
}
I put this code in my top-level DBIC::Schema class & call it thusly:
MYDB::concat_db($row, 'output', "My wacky \'output\' ??? 'yoyo' ");
& magically the correct quoting happens.
Here's what that string looks like after its been quoted:
'My wacky ', char(39), 'output', char(39), ' ', char(63), '', char(63), '', char(63), ' ', char(39), 'yoyo', char(39), ''
Note the first & last single quote get put there in the CONCAT statement itself. Happy Quoting!!