4 ms·

Little Bobby Tables strikes again: https://xkcd.com/327/ https://xkcd.com/327/

by samch 2y ago

permo-w 2y ago
really feels like SQL should have never been written in such a fundamentally insecure manner, or immediately fixed once it was discovered that it was
- tacker2000 2y ago
  SQL was devised far before web apps or the internet were even a thing…
- kchr 2y ago
  SQL in itself is not the weak point in this case (or any of the other cases of a successful SQLi attack). The problem is the treatment of user-controllable input data and using that data as part of a SQL query without properly sanitising/escaping special characters first.
- akoboldfrying 2y ago
  How would you "fix" it, while still allowing people to write ad hoc queries?
  - nucleardog 2y ago
    Don't allow non-parameterized queries at all? Like right at the protocol and parser level? Strip "literal value" as a token right out of the query parser. Then a simple interactive client could do something like: ``` > select * from users where username = :username username? admin +----+----------+----------+ | id | username | password | +----+----------+----------+ | 7 | admin | 12345 | +----+----------+----------+ ``` While a fancier client could, in fact, transparently translate queries exactly as you write them today--pull out the values, replace them placeholders, then send the query and values over the wire. ``` > select * from users where username = 'admin' sent as: query: select * from users where username = :placeholder1 placeholder1: admin ``` There's, of course, nothing stopping any given library or application from doing the same thing, but the vast majority of the time I'd wager this is happening because someone tried the obvious and simple thing (string concatenation) and it worked and they stopped there. Anyone who knows enough to write their own SQL parser or even think to go find a library to do this is probably going to know why they absolutely should not be doing this.
    - akoboldfrying 2y ago
      Well, I agree that this would be a force acting in the direction of Good, though it's hard to gauge how much >There's, of course, nothing stopping any given library or application from doing the same thing would happen. People already use a library to talk to the RDBMS back end; a "convenience wrapper" library that adds literals back into the grammar sounds like something that might easily become popular, and then you're back to square one. The question of how best to nudge people away from these footguns is certainly interesting, and applicable to other languages (e.g., HTML). Another option would be to allow, say, BASE64-encoded literals only.

Little Bobby Tables strikes again: https://xkcd.com/327/ https://xkcd.com/327/

by samch 2y ago

Little Bobby Tables strikes again: https://xkcd.com/327/ https://xkcd.com/327/

really feels like SQL should have never been written in such a fundamentally insecure manner, or immediately fixed once it was discovered that it was

tacker2000 2y ago
SQL was devised far before web apps or the internet were even a thing…
kchr 2y ago
SQL in itself is not the weak point in this case (or any of the other cases of a successful SQLi attack). The problem is the treatment of user-controllable input data and using that data as part of a SQL query without properly sanitising/escaping special characters first.
akoboldfrying 2y ago
How would you "fix" it, while still allowing people to write ad hoc queries?
- nucleardog 2y ago
  Don't allow non-parameterized queries at all? Like right at the protocol and parser level? Strip "literal value" as a token right out of the query parser. Then a simple interactive client could do something like: ``` > select * from users where username = :username username? admin +----+----------+----------+ | id | username | password | +----+----------+----------+ | 7 | admin | 12345 | +----+----------+----------+ ``` While a fancier client could, in fact, transparently translate queries exactly as you write them today--pull out the values, replace them placeholders, then send the query and values over the wire. ``` > select * from users where username = 'admin' sent as: query: select * from users where username = :placeholder1 placeholder1: admin ``` There's, of course, nothing stopping any given library or application from doing the same thing, but the vast majority of the time I'd wager this is happening because someone tried the obvious and simple thing (string concatenation) and it worked and they stopped there. Anyone who knows enough to write their own SQL parser or even think to go find a library to do this is probably going to know why they absolutely should not be doing this.
  - akoboldfrying 2y ago
    Well, I agree that this would be a force acting in the direction of Good, though it's hard to gauge how much >There's, of course, nothing stopping any given library or application from doing the same thing would happen. People already use a library to talk to the RDBMS back end; a "convenience wrapper" library that adds literals back into the grammar sounds like something that might easily become popular, and then you're back to square one. The question of how best to nudge people away from these footguns is certainly interesting, and applicable to other languages (e.g., HTML). Another option would be to allow, say, BASE64-encoded literals only.