To me, at least, capital-m-modern Perl is about a bunch of pathetic self-promoters trying to sell their books, courses, etc. Perl used to be about getting a job done; “Modern” Perl is a fashion show.

s{‘}{”}g

Of course, many SQL databases support backslash escaping instead of or as well as the SQL standard method.

The right way to embed data in SQL is to have a function whose input is a data object (in whatever form you’re dealing with in Perl space) and whose output is a string of SQL source constituting an expression that will evaluate to the in-database representation of the same data. If you’re dealing with a string (or anything that you represent as a string in SQL space) then you’d expect that output expression to be in the form of an SQL string literal. The concept generalises to all SQL data types, including ones that don’t have a literal syntax, and to all types of object that you deal with in Perl space.

Here’s the specific logic that I developed at $ork to represent a Perl octet string as an SQL octet string:

my %char_escape = ( "\0" => "\\0", "'" => "\\'", "\\" => "\\\\" );
sub sql_octetstring($) {
    my($value) = @_;
    die "not an octet string"
      unless Params::Classify::is_string($value)
      && $value =~ /\A[\x00-\xff]*\z/;
    $value =~ s(([\0\'\\]))($char_escape{$1})eg;
    return "'$value'";
}

This is specifically for MySQL; you need to experiment to write the correct equivalent for your particular database. Also, obviously, if you’re dealing with general Unicode then you’ll need an encoding layer on top of this.

You use it like “where title=@{[sql_octetstring($title)]}” or “values (@{[join(q(, ), map { sql_octetstring($film{$_}) } @fields)]})“. The babycart operator is brilliant for this sort of thing, because it gives you all the right cues about the nesting that is logically occurring.