my $sth = $dbh->prepare("SELECT CONCAT('My', '''', 'QL')");
[download]

You should use placeholders, though, to avoid the hard work:

my $sth = $dbh->prepare('SELECT CONCAT(?, ?, ?)');
$sth->execute(qw(My ' QL));
[download]

+1 for suggesting placeholders/bind-values. Takes the guesswork out of how to construct a safe, valid interpolated string, and could be more efficient if the execute may be called multiple times for a given prepare. It's just a good habit too; someday there would be a temptation to interpolate something into the SQL that isn't a string literal, and at that point you start getting into potential SQL injections.




Dave

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. 

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]

Thanks.

my $sth = $dbh->prepare("SELECT CONCAT('My','''', 'QL')");

It is work.

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.}