SQL Placeholders - clarification

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?

Comment on SQL Placeholders - clarification Select or Download Code

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]
Re^2: SQL Placeholders - clarification by LanX (Cardinal) on Feb 28, 2021 at 12:24 UTC
> 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]
Re^3: SQL Placeholders - clarification by dsheroh (Monsignor) on Mar 01, 2021 at 08:31 UTC
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 (Abbot) 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 (Bishop) 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]
Re^2: SQL Placeholders - clarification by Bod (Hermit) on Feb 28, 2021 at 15:02 UTC
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]
Re^3: SQL Placeholders - clarification by marto (Cardinal) on Feb 28, 2021 at 16:51 UTC
"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^4: SQL Placeholders - clarification by 1nickt (Abbot) on Mar 01, 2021 at 14:00 UTC
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]


No such thing as a small change
	PerlMonks