in reply to SQL Placeholders - clarification

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.


Replies are listed 'Best First'.
Re^2: SQL Placeholders - clarification
by LanX (Saint) 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

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