3 ms·

> plain SQL in a software program leaves you wide open to injection, to text substitution errors The non-ORM SQL libraries / APIs that I know offer APIs where

by est31 4y ago

> plain SQL in a software program leaves you wide open to injection, to text substitution errors The non-ORM SQL libraries / APIs that I know offer APIs where you provide an SQL query template, where you e.g. list your arguments as \1 \2 etc, and then you also specify the values of those arguments. The API then fills in the arguments in a injection safe manner. I don't think anyone is arguing for raw string manipulation here.

listenallyall 4y ago
I think it is exactly what the top poster is arguing for. He didn't say he used an API, he specifically said raw SQL. A non-ORM SQL library is just as non-portable as an ORM, his primary complaint. And each will have at least a few of their own weird intricacies, his secondary complaint (if they didn't, there wouldn't be more than one per language).
arkh 4y ago
The SQL drivers offer it. Send your parameterized query to the DB engine then send the parameters. For example PostgreSQL offers the functions PQexecParams and PQprepare. https://www.postgresql.org/docs/14/libpq-exec.html https://www.postgresql.org/docs/14/libpq-exec.html > The primary advantage of PQexecParams over PQexec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping.
- jsmith45 4y ago
  Parameterizied queries do eliminate injection in the cases that they support, but it is not uncommon to need to so some level of query building manually, since not everything you might want to do is supported by parameterized queries. For example one cannot usually do "... order by ? ?", passing in a column name and ascending/descending. And this is commonly needed for say paginated tables where the user can choose which column to sort by. Similarly, I've worked with some advanced seaarch screens that would need to dynamically add additional clauses to "WHERE", and in the end, that will come down to some form of string concatenation, even if parameters get represented as "?" and passed separately. But unfortunately doing that with string concatenation leaves more opportunity for injection errors to slip in. Its actually rather unfortunate pretty much no SQL database allows passing a query as an already parsed AST. It would be really neat if a query builder pattern in software could instead of building up a string could construct a binary representation of the AST, such that no injection was possible, and simply hand it off to the engine. The AST would dictate that this value is a column name, and because we are already an AST, there is no need to escape this value, since the engine won't need to reparse it. Or this value is a string, but once again, no reparsing from string needed, so no escaping needed. (Serialization format could have a length prefix or whatever).

by est31 4y ago

listenallyall 4y ago

I think it is exactly what the top poster is arguing for. He didn't say he used an API, he specifically said raw SQL. A non-ORM SQL library is just as non-portable as an ORM, his primary complaint. And each will have at least a few of their own weird intricacies, his secondary complaint (if they didn't, there wouldn't be more than one per language).

arkh 4y ago

The SQL drivers offer it. Send your parameterized query to the DB engine then send the parameters. For example PostgreSQL offers the functions PQexecParams and PQprepare. https://www.postgresql.org/docs/14/libpq-exec.html https://www.postgresql.org/docs/14/libpq-exec.html > The primary advantage of PQexecParams over PQexec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping.

jsmith45 4y ago
Parameterizied queries do eliminate injection in the cases that they support, but it is not uncommon to need to so some level of query building manually, since not everything you might want to do is supported by parameterized queries. For example one cannot usually do "... order by ? ?", passing in a column name and ascending/descending. And this is commonly needed for say paginated tables where the user can choose which column to sort by. Similarly, I've worked with some advanced seaarch screens that would need to dynamically add additional clauses to "WHERE", and in the end, that will come down to some form of string concatenation, even if parameters get represented as "?" and passed separately. But unfortunately doing that with string concatenation leaves more opportunity for injection errors to slip in. Its actually rather unfortunate pretty much no SQL database allows passing a query as an already parsed AST. It would be really neat if a query builder pattern in software could instead of building up a string could construct a binary representation of the AST, such that no injection was possible, and simply hand it off to the engine. The AST would dictate that this value is a column name, and because we are already an AST, there is no need to escape this value, since the engine won't need to reparse it. Or this value is a string, but once again, no reparsing from string needed, so no escaping needed. (Serialization format could have a length prefix or whatever).