4 ms·

> The situation is complex. If requests are malformed, it is natural that security products wouldn’t consider them valid SQL, thus making them unnecessary to bl

by a-dub 5y ago

> The situation is complex. If requests are malformed, it is natural that security products wouldn’t consider them valid SQL, thus making them unnecessary to block. what?! "this doesn't look like valid sql, so let it pass and have the potentially vulnerable sql server with all the data and creds and tunnels on it parse and reject" sounds ridiculous to me! isn't the whole point of having a security proxy to check if requests are well-formed, and if they're not, block those requests so that the more complicated implementation in the actual server on the production host is protected from malformed requests that are potentially malicious? i get it, building a parser and validator that is independent of the one that is in the actual database server, yet implements the same language is hard... but in my mind, the whole point of using some sort of firewall is that it looks for quasi-valid things and blocks them! (where the parser in the database server, and the parser in the firewall, are two independently hardened pieces of software developed from the language spec that provide redundancy.) edit: ok, i think i see the complexity now. forgot that sql injections are not complete queries, but rather fragments of sql, so they're harder to detect since sql fragments can look so general. (ie; a strict parser for finding them is required otherwise all sorts of reasonable things would get blocked) isn't this why we have pre-tokenized sql queries? they've been around a long, long time.

Kalium 5y ago
How sure are you that you can get your parser and validator correct in every case? And how sure are you that you can correctly define and then notice "quasi-valid" at the edge of the defined spec and implementation? How sure are you of your version-specific bug-for-bug compatibility, beyond the edges of the spec? I find it helps to remember that a firewall should be just one layer. You should have others, each of which blocks things detectably malicious and lets the rest go. It's not reasonable to expect a firewall to stop any and all potentially malicious traffic. It's not a replacement for input validation and good development practices, even though I have had coworkers who thought this.
- a-dub 5y ago
  > I find it helps to remember that a firewall should be just one layer. totally agree.
gruez 5y ago
>isn't this why we have pre-tokenized sql queries? they've been around a long, long time. Because it's not a silver bullet. At least for postgres, they only allow parameters with literals. Something like SELECT * FROM users WHERE username = :username works fine, because :username is substituted with a string literal (eg. 'user@example.com'). However, you can't do SELECT * FROM users WHERE :column = :value (to do dynamic lookups), because you can't use :column to select the "username" or "email" column. Of course, using an ORM or query builder can potentially solve this issue, but getting those to work and/or refactoring your code to do so is non-trivial, and it's not guaranteed that they're safe from sql injections either.
- a-dub 5y ago
  > (to do dynamic lookups), because you can't use :column to select the "username" or "email" column. that's by design. in 99% of cases you should never, ever do this. maybe if you're building a database exploration/management/manipulation tool, sure... but for all other cases (including weird manual table sharding schemes), the queries should be literals that are included in static code (possibly codegened if there's too many of them). under no circumstances should a user string ever be used to construct a column name. > Of course, using an ORM or query builder can potentially solve this issue, but getting those to work and/or refactoring your code to do so is non-trivial, orms are really complicated. query builders are probably okay for solving the issue most of the time. if i was building production software that i thought may be targeted, i would either write all the queries by hand, or codegen them ahead of time. if i had to pick them at runtime based on user input, i'd have one lookup function that takes the user data and returns a handle to the prepared query. > and it's not guaranteed that they're safe from sql injections either. is that actually true? i'm pretty sure if you use a prepared/tokenized query, all your sql injection nightmares will go away. sanitize untrusted strings and use prepared queries. problem solved. personally i think database drivers should have a mode that removes any capability for doing queries with raw sql strings.
  - fabian2k 5y ago
    Let's say I have a table in my application, and I want the user to be able to select which columns of a very wide table to show. I could of course return all columns all the time and filter on the frontend, but the more efficient way would be to determine the columns to be fetched from user input. And at that point I need to derive column names from a user string. Of course this should be implemented as picking from a whitelisted set of column names.

by a-dub 5y ago

Kalium 5y ago

How sure are you that you can get your parser and validator correct in every case? And how sure are you that you can correctly define and then notice "quasi-valid" at the edge of the defined spec and implementation? How sure are you of your version-specific bug-for-bug compatibility, beyond the edges of the spec? I find it helps to remember that a firewall should be just one layer. You should have others, each of which blocks things detectably malicious and lets the rest go. It's not reasonable to expect a firewall to stop any and all potentially malicious traffic. It's not a replacement for input validation and good development practices, even though I have had coworkers who thought this.

a-dub 5y ago
> I find it helps to remember that a firewall should be just one layer. totally agree.

gruez 5y ago

>isn't this why we have pre-tokenized sql queries? they've been around a long, long time. Because it's not a silver bullet. At least for postgres, they only allow parameters with literals. Something like SELECT * FROM users WHERE username = :username works fine, because :username is substituted with a string literal (eg. 'user@example.com'). However, you can't do SELECT * FROM users WHERE :column = :value (to do dynamic lookups), because you can't use :column to select the "username" or "email" column. Of course, using an ORM or query builder can potentially solve this issue, but getting those to work and/or refactoring your code to do so is non-trivial, and it's not guaranteed that they're safe from sql injections either.

a-dub 5y ago
> (to do dynamic lookups), because you can't use :column to select the "username" or "email" column. that's by design. in 99% of cases you should never, ever do this. maybe if you're building a database exploration/management/manipulation tool, sure... but for all other cases (including weird manual table sharding schemes), the queries should be literals that are included in static code (possibly codegened if there's too many of them). under no circumstances should a user string ever be used to construct a column name. > Of course, using an ORM or query builder can potentially solve this issue, but getting those to work and/or refactoring your code to do so is non-trivial, orms are really complicated. query builders are probably okay for solving the issue most of the time. if i was building production software that i thought may be targeted, i would either write all the queries by hand, or codegen them ahead of time. if i had to pick them at runtime based on user input, i'd have one lookup function that takes the user data and returns a handle to the prepared query. > and it's not guaranteed that they're safe from sql injections either. is that actually true? i'm pretty sure if you use a prepared/tokenized query, all your sql injection nightmares will go away. sanitize untrusted strings and use prepared queries. problem solved. personally i think database drivers should have a mode that removes any capability for doing queries with raw sql strings.
- fabian2k 5y ago
  Let's say I have a table in my application, and I want the user to be able to select which columns of a very wide table to show. I could of course return all columns all the time and filter on the frontend, but the more efficient way would be to determine the columns to be fetched from user input. And at that point I need to derive column names from a user string. Of course this should be implemented as picking from a whitelisted set of column names.