Comment by tialaramex | Hacker News Reader

Comment by tialaramex | Hacker News Reader

Parent Full thread

It reminds of the quote about Americans sometimes falsely attributed to Churchill saying they'll always do the right thing, "once they've tried everything else" (since it isn't a real quote you'll find a variety of phrasings)

Parametrisation is the Right Thing™ and yet people love inventing new half-arsed workarounds when they could just use parameterisation.

Elsewhere in security, historically you would find any number of hacks to try to avoid needing to use a good Mode Of Operation for your block cipher. People would rather expend mental effort and protocol complexity on trying to still do ECB when that's the Wrong Thing™ than just use the Right Thing™ (these days that's an inherently Authenticated Encryption Mode of Operation like GCM or GCM-SIV)

Or look at all the work trying to shore up human memorable shared secrets (passwords) when you almost never actually needed this and could have instead deployed stuff like WebAuthn.

Parametrization is not enough, there are plenty of scenarios where you need to programmatically construct queries based on user input in more complex ways than parameterized queries allow - including subqueries, lists (IN clauses), sort orders, and perhaps others.

But instead of inventing giant piles of workarounds, put your energy into fixing the database software.

> you need to programmatically construct queries based on user input

You still need to build the queries and have control of what parametres are in use. Why not build the parameters list dynamically as well?. There are some databases or drivers that have limitations where you need to sanitize data yourself, but I'm not sure if that applies to the majority of the scenarios.

This is a simple example of code for constructing dynamic queries:

    query = "... WHERE country = :country";
    params.set("country", country);

    if (userId != null) {
        query += " AND user_id = :userId";
        params.set("userId", userId);
    }

It may become more complex as you add sub queries or if the database does not support adding a list as a parameter, but the principle is the same. You can always just build the list programatically using parameters instead of concatenating the values given by the user directly.

If there are drivers or databases that does not support this, you will need to sanitize the data yourself of course. But I don't think that is the case for most users.

(btw, database drivers that support named parameters are much more convenient than using "?")

I guess there may be SQL frameworks or ORMs that make that hard. I worked on something that allowed parameterisation of any user supplied value, no matter how complex the query. It wasn't that hard, but we could make it do whatever we wanted.

Reply on news.ycombinator.com