3 ms·

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

by setr 4y ago

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

jeltz 4y ago
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.
- setr 4y ago
  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
  - jeltz 4y ago
    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.
- josteink 4y ago
  > 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.
  - jeltz 4y ago
    Especially since everyone else had solved it since before PHP even existed. This problem was invented by PHP.
doctor_eval 4y ago
> 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.
josteink 4y ago
> 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 https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP
chriswarbo 4y ago
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)

by setr 4y ago

jeltz 4y ago

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.

setr 4y ago
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
- jeltz 4y ago
  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.
josteink 4y ago
> 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.
- jeltz 4y ago
  Especially since everyone else had solved it since before PHP even existed. This problem was invented by PHP.

doctor_eval 4y ago

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

josteink 4y ago

> 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 https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP

chriswarbo 4y ago

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)