Hacker News Reader

> make it easy to parameterise SQL values

It usually is easy to parameterize things like values used in WHERE clauses. It's often much harder to work with dynamic query conditions (e.g. optional filters on a particular column). I don't believe I've seen an approach that does this in a way that can provably provide any sort of safety guarantee.

Do you have an example? I can only imagine three scenarios:

- SQL logic. We should be able to write this as a literal query, parameterised as needed, e.g. (made up syntax)

    query(
      addParam(
        "MyParam",
        $myParam,
        SQL"SELECT foo FROM tbl WHERE @MyParam IS NULL OR bar = @MyParam"
      )
    )

 - PHP logic. This is just ordinary control flow, like anything else, e.g.

    query(
      ($myParam === null)
        ? SQL"SELECT foo FROM tbl"
        : addParam("MyParam", $myParam, SQL"SELECT foo FROM tbl WHERE bar = @MyParam")
    )

 - A mixture of SQL logic and PHP logic. This seems inherently unsafe to me, so it's not surprising that safety guarantees can't be proven. My point is that such things should be be made difficult ("artificially", if needed), such that nobody would choose to go down that route when another option is available.

I'm not quite sure what you're asking for an example of. But suppose you have a table tbl(foo, bar, baz). Depending on user input, you may want to query on any combination of foo, bar, and baz. With this and larger number of columns, it becomes impractical to have conditions for every combination.

One approach would be to construct a list of conditions as well as a list of parameters to be substituted. Shown below without any particular language syntax, but hopefully comprehensible.

conditions = ("foo=?", "bar>?") parameters = (fooValue, barValue)

Then when building the SQL, you join the conditions together with AND and substitute in the parameters. This works in the sense that you are still prevented from injection. But it's rather messy. I suppose perhaps you can actually do what I'm discussing with some ORMs in a reasonably clean way. But my point is that most SQL interfaces make it easy to parameterize a single set of fixed values, but hard to do so for table and column names. Arguably this is a feature not a bug since you probably want to avoid such parameterization anyway. But having a safer way to do so would be nice.

You are aware that PHP was made popular roughly 20 years ago, right? In any case, I agree with your theoretical approach, however, I think you are ignoring the reality of "coders" out there. Security is still a niech topic in CS. Most people still want to get their work done, not caring about security at all.

> Most people still want to get their work done, not caring about security at all.

That's exactly why tools and languages should be designed for security, so that their users don't have to care.

I also disagree about 'not caring about security at all': security is opposite to functionality, since it prevents things rather than enabling them. If developers truly didn't care about security, we would see far more use of 'eval' as a way to plug systems together. Instead, we see a huge amount of effort spent on defining data and interchange formats, parsing parameters, branching on their values, etc.

For example, any time an API/URL provides options like '?sortField=age&sortDirection=DESC', that indicates developers who do care about security. In contrast, we can make a far more flexible API by accepting arbitrary code instead, like '?postProcess=(x) => x.sortBy((elem) => elem.age).reverse'; and this is much easier to implement, since we could just send it to 'eval()'.

>I don't consider this to be a good defence; in fact, I'd argue that makes PHP itself insecure.

Agree with you completely on that. PHP, is in fact, insecure by design.