Hacker News Reader

Comment by nicoburns | Hacker News Reader

> 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.

View on HN

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.

    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.

> 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.

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

Reply on news.ycombinator.com