3 ms·

I've written a lot raw SQL in the dialect my language supports. This is fine for static queries. When queries are dynamic (maybe they come from an admin panel o

by emidln 5y ago

I've written a lot raw SQL in the dialect my language supports. This is fine for static queries. When queries are dynamic (maybe they come from an admin panel or some other part with lots of optionality), static SQL isn't enough. You then get to do one of three things: ad-hoc string manipulation, rely on a query AST->compiler (like SQLAchemy's core or HoneySQL), or an ORM. ad-hoc string manipulation is a real security and reliability nightmare, and if you don't have a good standalone query AST to SQL compiler, an ORM is the next best thing.

codetrotter 5y ago
> ad-hoc string manipulation is a real security and reliability nightmare Parameters. A couple of examples: https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries https://www.psycopg.org/docs/usage.html#passing-parameters-t... https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php https://www.php.net/manual/en/mysqli.quickstart.prepared-sta...
- lowercased 5y ago
  You can't order/sort by parameters.
- jteppinette 5y ago
  How do you parametrize things like dynamic joins, where clauses, field selections, or aggregations without string manipulation or gross duplication?
- emidln 5y ago
  Parameters have zero bearing on whether you should dynamically construct SQL strings. If parameters can solve all of your problems, you don't have a dynamic SQL query, you have user-submitted values in a WHERE clause. What can be parameterized depends wildly on the SQL database in question. I haven't used one that could parameterize table names (for use in dynamic JOINs or CTEs) and many cannot parameterize values outside of a WHERE clause. Dynamically selecting which function to call, clauses to add or subtract, and sort orders are just a slice of places parameters don't help. In short, parameters alone do no eliminate the need for a query builder. A good query builder should appropriately parameterize values as the underlying database supports and hopefully uses a type system or validation to constrict the domain of values it uses to construct parts of the expression that cannot be parameterized.

by emidln 5y ago

codetrotter 5y ago

> ad-hoc string manipulation is a real security and reliability nightmare Parameters. A couple of examples: https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries https://www.psycopg.org/docs/usage.html#passing-parameters-t... https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php https://www.php.net/manual/en/mysqli.quickstart.prepared-sta...

lowercased 5y ago
You can't order/sort by parameters.
jteppinette 5y ago
How do you parametrize things like dynamic joins, where clauses, field selections, or aggregations without string manipulation or gross duplication?
emidln 5y ago
Parameters have zero bearing on whether you should dynamically construct SQL strings. If parameters can solve all of your problems, you don't have a dynamic SQL query, you have user-submitted values in a WHERE clause. What can be parameterized depends wildly on the SQL database in question. I haven't used one that could parameterize table names (for use in dynamic JOINs or CTEs) and many cannot parameterize values outside of a WHERE clause. Dynamically selecting which function to call, clauses to add or subtract, and sort orders are just a slice of places parameters don't help. In short, parameters alone do no eliminate the need for a query builder. A good query builder should appropriately parameterize values as the underlying database supports and hopefully uses a type system or validation to constrict the domain of values it uses to construct parts of the expression that cannot be parameterized.