Re: dbi mysql concat interpolation

I tried your SQL statement in mysql command line it works for me.

mysql> SELECT CONCAT('My','\'', 'QL');
+-------------------------+
| CONCAT('My','\'', 'QL') |
+-------------------------+
| My'QL                   |
+-------------------------+
[download]

 There are several ways to include quote characters within a string:

    A “'” inside a string quoted with “'” may be written as “''”.

    A “"” inside a string quoted with “"” may be written as “""”.

    Precede the quote character by an escape character (“\”).

    A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.

All is well. I learn by answering your questions...

Comment on Re: dbi mysql concat interpolation Download Code

Replies are listed 'Best First'.
Re^2: dbi mysql concat interpolation by Anonymous Monk on Feb 19, 2015 at 20:01 UTC
I tried your SQL statement in mysql command line it works for me. That's a different case - in the OP's case MySQL never even sees the backslash because it gets interpreted by Perl. Compare these two: `print "SELECT CONCAT('My','\'', 'QL')", "\n"; print "SELECT CONCAT('My','\\'', 'QL')", "\n"; __END__ SELECT CONCAT('My',''', 'QL') SELECT CONCAT('My','\'', 'QL')` [download]	[reply] [d/l]
Re^2: dbi mysql concat interpolation by lykich (Initiate) on Feb 19, 2015 at 18:07 UTC
Thanks. my $sth = $dbh->prepare("SELECT CONCAT('My','''', 'QL')"); It is work.	[reply]
Re^3: dbi mysql concat interpolation by Don Coyote (Hermit) on Feb 20, 2015 at 11:43 UTC
It is unsafe. Hello lykich and welcome to the monastery. Binding to `placeholders` `'?'` increases prevention of execution of malformed queries. Each `'?'` is replaced by the value relative to position in the execute statement. Firstly, construct your statements. `#!perl; use strict; use warnings; use 5.10.1; # features my @quotes = ( q/'/, q/"/ ); push @quotes, map q/'/, 0 .. int(rand(10)); push @quotes, map q/"/, 0 .. int(rand(10)); say 'quotes ', join( ' ', @quotes); my @Various_Apostrophes = map { $_ .= ( int(rand(40)+2) % 2 == 1 ) ? q!'! : q!"! ; } @quotes; say 'V_A ', join( ' ', @Various_Apostrophes); my $scquery = join('', q/'SELECT CONCAT( /, q/ ?,/ x (scalar(@Various_Apostrophes)-1), q/ ? )'/ ); say 'scq ',$scquery;` [download] Secondly, supply the preconstructed prepare and execute variables to the database methods. `my $sth = $dbh->prepare( $scquery ); $sth->execute( @Various_Apostrophes ); exit 0;` [download] In this way you separate code that handles query statements from code handling database requests. This makes safer the script and quickly debugs. ^{cp1252:chr(14): You cannot optimise code you have not yet written.}	[reply] [d/l] [select]