Escaping Apostrophe

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

I have a situation where I'm looping through to generate a SQL statement to update a table. Wha'ts happening though is The site name has an apostrophe in the value and the perl script throws an error at that point.

 SQL = UPDATE LIR_BUT_SITE
   SET ROW_IDENTIFIER =  '4'
, M_CUSTOMER_ABBREV =  'IBHW'
, M_SITE_KEY =  'R_LIR_BUT_SITE-'
, RESP_CENTER =  'Global Custom Svcs GCSC'
, M_SITE_NAME =  'MZ3481-CHILDREN'S HOSPITAL WALTHAM'
[download]

Here is the piece of code that generates the SQL

 for $idx (0..$#hdr) {
                if ($row[$idx] ne "") {
                        if ($found ne "N") {
                                $updstmt .= ", ";
                                $insstmt .= ", ";
                                $valstmt .= ", ";
                        }
                        $found = "Y";

                       
                        $updstmt .= $hdr[$idx]." =  '".$row[$idx]."' \
+r\n";
[download]

The values for the update are generated by the $row$idx.How do I put that $row$idx within double quotes or how do i make the perl interpreter escape any apostrophes in that field. Can some one suggest a solution?

Thanks.

Comment on Escaping Apostrophe Select or Download Code

Replies are listed 'Best First'.
Re: Escaping Apostrophe by haukex (Archbishop) on Oct 20, 2017 at 13:49 UTC
My first suggestion is to not re-invent the wheel, see for example SQL::Abstract (Update: example). Also, you should always use placeholders wherever possible - see Bobby Tables! DBI provides `quote` and `quote_identifier`, but I'd consider those a last resort if the above is not applicable for whatever unlikely reason.	[reply] [d/l] [select]
Re^2: Escaping Apostrophe by perl_gvenk (Initiate) on Oct 20, 2017 at 14:02 UTC
Not trying to reinvent the wheel. But this is the code we have in just one of many places, using abstract or anything else will require a lot of changes through the code. All I need is a quick and dirty solution with what I have.	[reply]
Re^3: Escaping Apostrophe (updated) by AnomalousMonk (Archbishop) on Oct 20, 2017 at 16:08 UTC
Quick, dirty — and, per other replies, kinda crazy (and also untested): `my ($row_sub_i_esc_sq = $row[$idx]) =~ s{ ' }{\\}xmsg; $updstmt .= $hdr[$idx]." = '". $row_sub_i_esc_sq ."' \r\n";` [download] And BTW: It looks like you're dealing with single-quote characters, not apostrophes. Update: Oops... Meant to write `s{ (?= ') }{\\}xmsg;` and the substitute-on-assignment expression is also wrong, so finally (I think): `(my $row_sub_i_esc_sq = $row[$idx]) =~ s{ (?= ') }{\\}xmsg; $updstmt .= $hdr[$idx]." = '". $row_sub_i_esc_sq ."' \r\n";` [download] Thanks choroba. Of course, that's what always happens when I post untested stuff. Give a man a fish: `<%-{-{-{-<`	[reply] [d/l] [select]
Re: Escaping Apostrophe by Your Mother (Archbishop) on Oct 20, 2017 at 15:35 UTC
DBI->quote is probably what you want. I agree with the comments already made however that eschewing placeholders is madness and any DB code that works without them, or `quote` at least, is insecure and depending on the data pipeline, very risky.	[reply] [d/l]
Re^2: Escaping Apostrophe (quote) by LanX (Saint) on Oct 20, 2017 at 16:55 UTC
I just wanted to mention quote() together with this link Backslashes with DBI quote( ) and MySQL which might be helpful for the OP. Cheers Rolf _{(addicted to the Perl Programming Language and ☆☆☆☆ :) Je suis Charlie!}	[reply]
Re: Escaping Apostrophe by LanX (Saint) on Oct 20, 2017 at 14:31 UTC
If your intention is to parse SQL, see http://search.cpan.org/~rehsack/SQL-Statement-1.412/lib/SQL/Parser.pm. We just had a discussion about how to (not) parse strings with embedded quotes, it's not trivial Re^4: Parser Performance Question (updated). Anyway I strongly support haukex' comment to use SQL-placeholders, anything else is insane. Cheers Rolf _{(addicted to the Perl Programming Language and ☆☆☆☆ :) Je suis Charlie!}	[reply]
Re: Escaping Apostrophe by Your Mother (Archbishop) on Oct 21, 2017 at 02:59 UTC
Follow up story. I inherited a code base—for a financial website no less—with scads of string building SQL handling like this. It was a dangerously insecure mess. I wanted to refactor, rather than extend and muddy it even more, with DBIx::Class but it wasn't installed and as a contractor, I couldn't do it or even get a request answered. I noticed that SQL::Abstract was already there. But then I had the same, fairly wise, hesitancy you do. I was almost guaranteed to break a money making app and maybe get my contract cancelled if I started attempting to introduce better practices at the expense of a working app. So, I started finishing the tests I'd been writing focusing on testing the SQL generating routines. When I had complete coverage, which did not take long, I started to refactor with SQL::Abstract. A couple days later it was all done. I did indeed break several things at first and misunderstood a few parts so my new code was wrong but the tests told me my mistakes. It went into production a week or so after I started without any problems at all.	[reply]