5 ms·

> (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 d

by 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.
- a-dub 5y ago
  in terms of performance, i would err on the side of reducing work for the database, assuming the middleware/rpc layer is written in a language that has a fast runtime. i suspect that using a smaller number of prepared queries at the cost of shipping more data around (especially if the number of rows per query is limited) would be more performant than custom queries. prepared queries avoid repetitive string parsing of the query language and give the db backend more opportunities to optimize. less entropy in the overall workload also can result in better utilization of caching. if there are a few common display filters, additional prepared queries could be added to optimize them. in practice, it's unlikely that there would be a lot of entropy in column selection.
  - gruez 5y ago
    >i suspect that using a smaller number of prepared queries at the cost of shipping more data around (especially if the number of rows per query is limited) would be more performant than custom queries. that depends entirely on how many columns/rows you're returning. > prepared queries avoid repetitive string parsing of the query language and give the db backend more opportunities to optimize. On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement.
    - a-dub 5y ago
      > On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement. sessions should live for the lifetime of the processes of the backend application server, so they would get updated every time you deploy. if you don't have a long living backend, then usually a long living proxy is used. if you're setting up a new session on every request, it seems there's not really much point to worrying about performance! anyway, every application is different and it's worth benchmarking. if i were building something today that used a sql backend, i would err on the side of using prepared statements as much as possible, both for security and performance reasons. maybe string queries could make sense, but they're both more expensive to run and more likely to result in a vulnerability, even if they were coded carefully at first implementation.
- function_seven 5y ago
  I've had to do similar in the past. I ended up enumerating all the columns, intersecting that set with the ones submitted by the user, and using the result in the SELECT clause of the query. Not as easy as just dropping in the user's choices, but guarantees they have no control over the resulting query string.

by a-dub 5y ago

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.

a-dub 5y ago
in terms of performance, i would err on the side of reducing work for the database, assuming the middleware/rpc layer is written in a language that has a fast runtime. i suspect that using a smaller number of prepared queries at the cost of shipping more data around (especially if the number of rows per query is limited) would be more performant than custom queries. prepared queries avoid repetitive string parsing of the query language and give the db backend more opportunities to optimize. less entropy in the overall workload also can result in better utilization of caching. if there are a few common display filters, additional prepared queries could be added to optimize them. in practice, it's unlikely that there would be a lot of entropy in column selection.
- gruez 5y ago
  >i suspect that using a smaller number of prepared queries at the cost of shipping more data around (especially if the number of rows per query is limited) would be more performant than custom queries. that depends entirely on how many columns/rows you're returning. > prepared queries avoid repetitive string parsing of the query language and give the db backend more opportunities to optimize. On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement.
  - a-dub 5y ago
    > On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement. sessions should live for the lifetime of the processes of the backend application server, so they would get updated every time you deploy. if you don't have a long living backend, then usually a long living proxy is used. if you're setting up a new session on every request, it seems there's not really much point to worrying about performance! anyway, every application is different and it's worth benchmarking. if i were building something today that used a sql backend, i would err on the side of using prepared statements as much as possible, both for security and performance reasons. maybe string queries could make sense, but they're both more expensive to run and more likely to result in a vulnerability, even if they were coded carefully at first implementation.
function_seven 5y ago
I've had to do similar in the past. I ended up enumerating all the columns, intersecting that set with the ones submitted by the user, and using the result in the SELECT clause of the query. Not as easy as just dropping in the user's choices, but guarantees they have no control over the resulting query string.