3 ms·

Sadly it is not always possible in general to completely avoid constructing queries programmatically (which always comes down to string concatenation at some le

Sadly it is not always possible in general to completely avoid constructing queries programmatically (which always comes down to string concatenation at some level of the stack) as parameterized queries have some limitations. For example engines don’t always support a list parameter, meaning an “in” clause with varying number of entries can’t work. (Some might allow table variable parameters as a workaround, but not all). Another common reason for needing dynamic query construction varying sort by. Once again many databases don’t let you do “order by ? ?” (First being column name, second ASC/DESC). But a good query builder abstraction with knowledge of your specific DBMS, can help avoid injection here, especially if it does a sanity check of reparsing the generated query and comparing it against an AST it built of the target query. Even when using such a system though, it can make sense to have the query builder build a parameterized query, and use that for passing as many parameters as possible, just as defense in depth. (Avoids bugs in escaping code of query builder, for example.)

vlovich123 4y ago
ASC/DESC doesn’t technically need parameterization. The point about parameterizing and not query building is about not query building directly from user inputs. It’s fine to build the query fine when you control every part of the input used to build the string. Parameterization is only really for when you need to process a user string, particularly. One mitigation for the column name is to validate it’s in the enumeration of available column names first. Now of course, if you’re trying to be super strict about parameterization so that static tools / linters / code policy during review don’t complain that’s one thing. But it’s not an invalid choice to utilize domain knowledge to simplify the problem if your tooling doesn’t let you do the right thing.
- jsmith45 4y ago
  Right. Concatenating in insufficiently constrained user inputs are dangerous (and trying to constrain a general string enough is usually too risky). If you can prove that a user input is a harmless value (a valid column name for the table for an order by, an integer, etc) then concatenation is safe. On the other hand, it is still not ideal have a bunch of code where you are directly concatenating strings to generate SQL, since people making later changes are likely to end up modifying code to concatenate unsafe input. Hence why having a good query builder api can be valuable. It can hide away the string concatenation, and if well designed, can force that a value but in the order by position is a column name, etc, ideally while making it hard or impossible to have insufficiently constrained user input thrown in. For example, if a language supports distinguishing string literals (or compile time constant strings), and variable string values, it is possible to create a query builder api where arbitrary user provided strings simply cannot become part of the query. (e.g. The only paths that let non-constant strings into the query are those that highly constrain the strings to provably safe values).

vlovich123 4y ago

ASC/DESC doesn’t technically need parameterization. The point about parameterizing and not query building is about not query building directly from user inputs. It’s fine to build the query fine when you control every part of the input used to build the string. Parameterization is only really for when you need to process a user string, particularly. One mitigation for the column name is to validate it’s in the enumeration of available column names first. Now of course, if you’re trying to be super strict about parameterization so that static tools / linters / code policy during review don’t complain that’s one thing. But it’s not an invalid choice to utilize domain knowledge to simplify the problem if your tooling doesn’t let you do the right thing.

jsmith45 4y ago
Right. Concatenating in insufficiently constrained user inputs are dangerous (and trying to constrain a general string enough is usually too risky). If you can prove that a user input is a harmless value (a valid column name for the table for an order by, an integer, etc) then concatenation is safe. On the other hand, it is still not ideal have a bunch of code where you are directly concatenating strings to generate SQL, since people making later changes are likely to end up modifying code to concatenate unsafe input. Hence why having a good query builder api can be valuable. It can hide away the string concatenation, and if well designed, can force that a value but in the order by position is a column name, etc, ideally while making it hard or impossible to have insufficiently constrained user input thrown in. For example, if a language supports distinguishing string literals (or compile time constant strings), and variable string values, it is possible to create a query builder api where arbitrary user provided strings simply cannot become part of the query. (e.g. The only paths that let non-constant strings into the query are those that highly constrain the strings to provably safe values).