4 ms·

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

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

boxed 3y ago
Sounds like just begging for SQL injection attacks.
- nicoburns 3y ago
  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.
  - boxed 3y ago
    That makes no sense. What are you interpolating? Some variable. And you now have to audit that THAT VARIABLE is safe.
    - nicoburns 3y ago
      > What are you interpolating? Some variable. Nope, I'm generally interpolating an inline expression consisting entirely of string literals.
      - boxed 3y ago
        > generally Does not inspire confidence.
        nicoburns 3y ago
        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.
    - squeaky-clean 3y ago
      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.
- ZitchDog 3y ago
  You can avoid this entirely with JavaScript's tagged template literals. Here is an example library: https://github.com/blakeembrey/sql-template-tag https://github.com/blakeembrey/sql-template-tag

boxed 3y ago

Sounds like just begging for SQL injection attacks.

nicoburns 3y ago
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.
- boxed 3y ago
  That makes no sense. What are you interpolating? Some variable. And you now have to audit that THAT VARIABLE is safe.
  - nicoburns 3y ago
    > What are you interpolating? Some variable. Nope, I'm generally interpolating an inline expression consisting entirely of string literals.
    - boxed 3y ago
      > generally Does not inspire confidence.
      - nicoburns 3y ago
        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.
  - squeaky-clean 3y ago
    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.
ZitchDog 3y ago
You can avoid this entirely with JavaScript's tagged template literals. Here is an example library: https://github.com/blakeembrey/sql-template-tag https://github.com/blakeembrey/sql-template-tag