Hacker News Reader

Comment by NewEntryHN | Hacker News Reader

Any serious application beyond the example given in this article will include conditional SQL constructs which go beyond SQL query parameters and will therefore require string formatting to build the SQL.

Think a simple UI switch to sort some result either ascending or descending, which will require you format either an `ASC` or a `DESC` in your SQL string.

The moment you build SQL with string formatting is the moment you're rewriting the SQL formatter from an ORM, meeting plenty of opportunities to shoot yourself in the foot.

View on HN

> The moment you build SQL with string formatting is the moment you're rewriting the SQL formatter from an ORM, meeting plenty of opportunities to shoot yourself in the foot.

I used to think this, but at my last company we ended up rewriting all these queries to use conditional string formatting as we found it much more readable. The key was having named parameter binding for that string, so you didn't have to worry about matching up position arguments. That along with JavaScripts template string interpolation actually made the string-formatted version pretty nice to work with.

Sounds like just begging for SQL injection attacks.

Values were still provided separately. The string-interpolated SQL would include a placeholder just like static SQL does. That's pretty easy to audit for in code review: no variables in interpolated code.

That makes no sense. What are you interpolating? Some variable. And you now have to audit that THAT VARIABLE is safe.

> What are you interpolating? Some variable.

Nope, I'm generally interpolating an inline expression consisting entirely of string literals.

> generally

Does not inspire confidence.

In the rare case that you're interpolating a variable, you'd need to audit it in review. This is similar to carefully auditing the rare use cases of raw SQL expression when using an ORM.

    sortable_fields = ["name", "age", "gpa"]
    
    selected_filter = sortable_fields[form.filterIndex]

    if form.sortBy == "asc":
      query += "ORDER BY {} ASC"
    elif form.sortBy == "desc":
      query += "ORDER BY {} DESC"

Doesn't have any opportunity for SQL injection unless you have rogue programmers able to change code running in prod.

You can avoid this entirely with JavaScript's tagged template literals. Here is an example library: https://github.com/blakeembrey/sql-template-tag

There’s a world between a query builder and an ORM. The point of ORMs isn’t to build queries, if that’s the only need might as well just use a query builder which is a lot more lightweight and doesn’t come with all the downsides of orms

The OP literally says to ignore query builders, not just ORMs. When they state “just write SQL” that’s their actual thesis.

What you describe just needs a query builder (e.g. in Java something like jOOQ), not necessarily an ORM.

OK but TFA is not just against ORMs, it’s also against query builders. That’s what GP is replying to.

Depends on the abstraction... for example .Net's extensions for LINQ are pretty good at this, I haven't generally used the LINQ syntax, but the abstraction for query constructs are pretty good, combined with Entity Framework. Of course, there's a lot that I don't care for and would prefer Dapper. In the end, the general environment of .Net dev being excessively "enterprisey" has kept me at bay the past several years.

Just looked at LINQ and it looks like Ecto [0] used a lot of its ideas for inspiration! I haven't used LINQ, but in Ecto, there are so many useful constructs for composing queries. If you get a stinker of a query, you have multiple escape hatches such as fragments [1] or just writing the queries directly as needed [2].

For beginners, the Elixir language constructs can be a little clunky, but once you get it, it's so productive and I miss that productivity when doing more advanced queries in other languages.

[0]: https://hexdocs.pm/ecto/Ecto.Query.html [1]: https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments [2]: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4

IMHO the real proper solution is to have an SQL parser, so you can have your SQL represented as an AST, do some operations on it, then compile it back to a query.

Sadly, I'm not aware of any good solutions to this. SQLAlchemy Core can build an operates on an AST, but it doesn't parse raw SQL into a query (so one has to write their queries in Python, not SQL). Some parser libraries I've seen were able to parse the query but didn't have much in terms of manipulations and compiling it back.

Reply on news.ycombinator.com