http://qs321.pair.com?node_id=11128901

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

Over on Re^6: Splitting the records into multiple worksheets, hippo pointed out the error of my ways and I have been going through some code that's being produced to implement placeholders. Can I please check that I am now on the right lines and doing things better...

I had this line of code... 

$dbh->do("INSERT INTO Web_Page SET template = '$request', test = '$tes
+t', source = '$data{'source'}', Visitor_idVisitor = $cookie{'_ls_visi
+t'}") unless $$vars{'testpage'};

[download]
All the variables are generated within the code except $data{'source'} which is derived from the HTTP query string and therefore potentially unsafe.

I have replaced that line of code with this... 

unless ($vars->{'testpage'}) {
    my $query = $dbh->prepare("INSERT INTO Web_Page SET template = '$r
+equest', test = '$test', source = ?, Visitor_idVisitor = $cookie{'_ls
+_visit'}");
    $query->execute($data{'source'});
}

[download]
Is that the best approach or should I be using placeholders for every variable, even those I have declared and therefore know are safe?

Replies are listed 'Best First'.
Re: SQL Placeholders - clarification
by dave_the_m (Monsignor) on Feb 28, 2021 at 12:21 UTC
    Use placeholders for everything. Perhaps you don't have as much control over the variables as you think you have? Maybe in future someone will change one of the variables in an unsafe way? By making them placeholders you get automatic protection all of the time with no cognitive effort.

    Dave.

[reply]
      > you get automatic protection

      Plus automatic escaping of (wanted) metacharacters for free

      Cheers Rolf
      (addicted to the Perl Programming Language :)
      Wikisyntax for the Monastery

[reply]
        I'm sure you know this, LanX, but, for those who might not, I've gotta nitpick: Placeholders do not escape metacharacters. When using placeholders, the placeholder values are sent to the database completely separately from the query structure, which removes any need for escaping of any characters - data cannot be confused for commands because the data and the commands are not sent together.

        I know this seems like an academic distinction, but I consider it an important one because referring to placeholders as a way of escaping metacharacters encourages people to think of escaping and placeholders as equivalent, so, if placeholders are inconvenient, then, meh, escaping is just as good - which is false. They are not equivalent. Proper escaping can provide excellent protection against injection attacks, but placeholders provide absolute protection.

        (There is one exception to this: Database backends which don't actually support placeholders, so the DBD layer has to fake it by using escaping internally. But it's still better to use placeholders in such cases, both for compatibility with databases which do support real placeholders and because the person who wrote the database driver probably knows more than you do about what's needed to properly escape data for that particular backend.)

[reply]
Re: SQL Placeholders - clarification
by Discipulus (Canon) on Feb 28, 2021 at 12:27 UTC
    Hello Bod

    Yes! use placeholders everywhere! ;)

    L*

    There are no rules, there are no thumbs..
    Reinvent the wheel, then learn The Wheel; may be one day you reinvent one of THE WHEELS.
[reply]
[d/l]
Re: SQL Placeholders - clarification
by haukex (Archbishop) on Feb 28, 2021 at 13:26 UTC

    As the others have said, yes, don't interpolate any of those variables into the SQL. If you find you want to use placeholders in places where they are not supported, like say table names (which is pretty uncommon anyway!), then you may want to look at SQL::Abstract.

    use SQL::Abstract;
my $sql = SQL::Abstract->new;
my $table = 'Web_Page';
my ($stmt, @bind) = $sql->insert($table, { template => $request,
        test => $test, source => $data{source},
        Visitor_idVisitor => $cookie{_ls_visit} });
my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);

    [download]
[reply]
[d/l]

      I don't think I have ever found it necessary to dynamically select table names. Field names I do sometimes - usually to put a telephone number in a mobile or landline field depending on the format.

      Thanks for drawing my attention to SQL::Abstract. Nice to know it is there but I cannot see me needing to use it anytime soon.

[reply]

        "Nice to know it is there but I cannot see me needing to use it anytime soon."

        Indeed, it's a great tool to be aware of and keep the concept of tucked away until you need it, most of the time I don't have call for it, but am grateful it's there when I do :)

[reply]
Re: SQL Placeholders - clarification
by marto (Cardinal) on Feb 28, 2021 at 12:37 UTC

    Once again, yes, use them for everything, note what the documentation says about this, also n.b. the 'performance' sub section.

[reply]
Re: SQL Placeholders - clarification
by dsheroh (Monsignor) on Mar 01, 2021 at 08:34 UTC
    Once more reason to use placeholders even for variables whose values are completely under your control is that it helps to build and maintain a habit of always using placeholders, which can help to avoid any temptation to quietly slip in an interpolation that "should be safe" or "is just a quick and dirty prototype" somewhere down the line.
[reply]
Re: SQL Placeholders - clarification
by Anonymous Monk on Feb 28, 2021 at 16:22 UTC
    Another benefit, especially useful for bulk operations, is that you can "prepare" the SQL query once, with its placeholders, and then re-execute that prepared query-handle as many times as you wish, substituting different placeholder values each time.
[reply]

Back to Seekers of Perl Wisdom