Hacker News Reader

Comment by dvh | Hacker News Reader

Is this some kind of sick joke I'm too old to understand? Ffs stop gluing together SQL strings! PHP had PDO for decades now, use it!

Oh but PDO doesn't support params in "limit" statement. Than make it support it! Improve PDO's support for "in" parameters as well so that I can pass damn array.

Hack add a "strict mode" to PDO that makes any kind of value in SQL invalid statement.

Running some magic AI than randomly blocks valid SQL is not a solution.

View on HN

When I first learned about escaping to avoid SQL injection, I was suspicious the whole concept was demented — why can’t I just tell the database that this thing isn’t SQL code?

Then I learned about parameterization, and had my suspicions justified but I didn’t understand why it wasn’t the standard answer.

Finally it turned out PHP mysql lib apparently didn’t support it for ages, and thus young minds have been poisoned by the ridiculous notion of string escaping for decades.

It's still a thing even today with shit like html escaping; I don’t understand why we’re so explicit on so many things in programming, and then as soon as we try talking to another system we just drop everything back to stringly typed nonsense

The answer to your question is that PHP's mysql library was pretty much alone, even at the time, in not supporting parametrization. Also your idea for automatic html escaping had been the norm in the Rails community for like 10 years and the JS community is also pretty good, but here I am unclear why not all language communities followed suit.

I don’t think I’m even in favor of automatic string escaping; string escaping generally is a ridiculous concept, for any place where we’re not intentionally mixing code & data e.g. a user-supplied regex — if it’s a user-supplied string that we intend to stuff into the middle of a regex as simple text… we should be capable of simply saying that’s what we want to do. Instead of smearing backslashes everywhere.

Keep the inputs out-of-band and this whole problem doesn’t even exist as a concept

Which is what the PostgreSQL protocol does. It supports sending the query and its parameters separately. HTML on the other hand does not support that.

> The answer to your question is that PHP's mysql library was pretty much alone, even at the time, in not supporting parametrization.

And at the same time everyone and their grandmother was making websites using PHP and MySQL.

There should have been enough incentive to improve this early on.

Especially since everyone else had solved it since before PHP even existed. This problem was invented by PHP.

> Sometimes in 2017, while blotus, bui and I were brainstorming what mitigations we wanted in Snuffleupagus, we really wanted to kill SQL Injections, but it's a hard problem.

Killing SQL injection is not a hard problem. Parsing this statement, however, is.

> It's still a thing even today with shit like html escaping; I don’t understand why we’re so explicit on so many things in programming, and then as soon as we try talking to another system we just drop everything back to stringly typed nonsense

To be fair, most web-browsers today supports Content Security Policy[1], where you can explicitly declare that NO, nothing inside this HTML is ever allowed to be executed as code. The only code allowed comes from script tags, from these domains, which you may optionally require to match a specific hash.

It's not that hard.

Ofcourse the application has to be built to deliver the front-end payload that way for this to work, but it's not exactly rocket-science.

[1] https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP

The concept of escaping is perfectly fine; the demented part is having it turn strings into strings, which allows it to be applied any number of times (including zero).

That can easily be avoided by producing a different type of thing to the input, e.g.

    escapeHTML  : String -> HTMLChunk
    escapeSQL   : String -> SQL
    escapeShell : String -> Shell
    etc.

and having our "execute" API requires that thing as input, e.g.

    returnResponse : HTML -> ()
    runQuery       : SQL -> ResultSet
    runCmd         : Shell -> (ExitCode, Stdout, Stderr)

This way, we're forced to escape (to get a valid argument for the API), and we can't double-escape (since escaping functions require a string as input)

Also does PDO not fall back to 'emulating' prepared-statements by ultimately sending an interpolated query in certain circumstances with unknown implications?

Killing bug classes like SQL injections is for situations where you can't always do the right thing, like using proper parameterised queries. Maybe you have an old codebase you haven't or won't finish rewriting. Maybe you can't afford people who don't write SQL injection bugs. Maybe you can but they still eventually accidentally add such bugs and you want to be sure.

Reply on news.ycombinator.com