How can I quote a long string in Perl?

Question

I usually use simple quotes, but sometime I get very long lines which I can't break and also need to use escape characters, so I get something like this:

my $str = "select query_accession,query_tag,hit_accession,hit_tag,significance from summaryTables where query_id = \'$query_id\';"

I know there are various other ways for representing strings in Perl. What would you recommend?

UPDATE Thank you all guys for the SQL-related suggestions. I learn some valuable stuff, but, my question remains (as a general one, regardless of SQL): is there some operator that allows quoting without catching line breaks?

what I do now is something like:

my $str = "123 123 456 sdndfnd sdfdmd " .
 "dfsdjkfs 343489 dfjsdj 3 34kdfsk kd " .
 "fd kd9534 rfg 546 5";

which is quite ugly.

Answer 1

Look in perlop's Quote-Like Operators. Use qq for double quotes and q for single quotes.

Answer 2

In this particular case I'd recommend using placeholders, unless you're absolutely 100% sure that $query_id can never contain "funny characters"

Obligatory xkcd reference

Answer 3

In the example that you have given, there is no reason not to break the line and there is no need to escape the single quotation marks.

my $str = "select query_accession,
                  query_tag,
                  hit_accession,
                  hit_tag,significance
           from   summaryTables
           where query_id = '$query_id';"

But, as others have pointed out, for substitutions in SQL queries that are going to be passed to DBI you're much better off using placeholders.

Answer 4

As its SQL you're quoting then consider using something like SQL::Abstract to build your query.

For example:

use SQL::Abstract;

my $sql = SQL::Abstract->new;
my $query_id = 'xyzzy';  # arbitary value

my ($query, @bind) = $sql->select( 
    'summaryTables',
    [qw/ query_accession query_tag hit_accession hit_tag significance /],
    { query_id => $query_id },
);

This is what you would see in $query:

SELECT query_accession, query_tag, hit_accession, hit_tag, significance FROM summaryTables WHERE ( query_id = ? )

and in @bind:

xyzzy

So $query has already been built to accept SQL placeholder and @bind has the necessary values. So its then just usual DBI stuff like this to run the query:

my $sth = $dbh->prepare( $query );
$sth->execute( @bind );

Now you have all the safety and optimisation that SQL placeholders provide (see SQL injection Wikipedia entry)

Also see this previous SO question: Is there SQL parameter binding for arrays?

/I3az/

Answer 5

I like here documents, although some people despise them because the terminator has to appear at the beginning of the line despite your indent level.

my $str = <<"SQL";
  SELECT 
    query_accession,
    query_tag,
    hit_accession,
    hit_tag,
    significance
  FROM   
    summaryTables
  WHERE 
    query_id = ?
SQL

I also like formatting my SQL so I can easily see the structure in the statement.

Answer 6

No. All of Perl 5's string creation methods know about and include newlines. You can either use the concatenation operator as you did in your question, or abstract away the code needed to fix the problem:

#!/usr/bin/perl

use strict;
use warnings;

sub single_line {
    my @strings = @_;
    for (@strings) {
        s/\s+/ /g;
        s/^\s+|\s+$//g;
    }
    return wantarray ? @strings : $strings[0];
}


my $sql = single_line "
    select query_accession,query_tag,hit_accession,hit_tag,significance
    from summaryTables
    where query_id = ?;
";

print "[$sql]\n";

Answer 7

For general text building (re: your update) an exotic answer is to use a template engine. Its a bit like a printf on steroids!

Here is an example using Template Toolkit:

sub buildtt {
    use Template;
    my $tt = Template->new( START_TAG => '{', END_TAG => '}' );
    $tt->process( \$_[0], $_[1], \my $output );
    return $output;
}

my $str = buildtt '{a} {b} {c}' => {
    a => "123 123 456 sdndfnd sdfdmd",
    b => "dfsdjkfs 343489 dfjsdj 3 34kdfsk kd",
    c => "fd kd9534 rfg 546 5",
};

You can also build it like this:

my $str2 = buildtt '{all.join(" ")}' => {
    all => [ "123 123 456 sdndfnd sdfdmd",
             "dfsdjkfs 343489 dfjsdj 3 34kdfsk kd",
             "fd kd9534 rfg 546 5" ],
};

And here is an example with some quoting, etc:

my $str3 = buildtt '{all.join(" ")}' => {
    all => [ "no quoted text here",
             "here's some and here's some more",
             q{$str2 was "buildtt"},
             $str2 ],
};

A better example is something like this with your original SQL text:

my $sql = buildtt 'select {v.join(",")} from {t} where {q}' => { 
    v => [qw/ query_accession query_tag hit_accession hit_tag significance /],
    t   => 'summaryTables',
    q   => '( query_id = ? )',
};

Also see:

• SO question: What should I use instead of printf in Perl?
• String:TT CPAN module
• List of template engines (not complete) on Perl5 wiki

And finally going from exotic to weird you could even create a new quote-like operator with PerlX::QuoteOperator:

use PerlX::QuoteOperator q_strip_newline => {
    -emulate => 'q',
    -with    => sub ($) {
        my $txt = shift;
        $txt =~ s/\n//g;
        $txt;
    },
};

my $str = q_strip_newline{123 123 456 sdndfnd sdfdmd
dfsdjkfs 343489 dfjsdj 3 34kdfsk kd
fd kd9534 rfg 546 5};

/I3az/

Answer 8

While there is no built in operator to do this, here's another approach that might work:

(my $str = qq(
123 123 456 sdndfnd sdfdmd 
dfsdjkfs 343489 dfjsdj 3 34kdfsk kd 
fd kd9534 rfg 546 5
)) =~ s/\n//g;

Or in two-step form:

my $str = qq(
123 123 456 sdndfnd sdfdmd 
dfsdjkfs 343489 dfjsdj 3 34kdfsk kd 
fd kd9534 rfg 546 5
);
$str =~ s/\n//g;

Both are ugly, but functional.