I disagree with the part that there is always an alternative to building strings for SQL statements. Yes, all client libraries support prepared statements. But none of them support placing prepared statement fields in any part of the SQL statement.

Prepared statements work fine as long as you just have simple SELECT statements with a few WHERE values as parameters. They completely fail if you need to do any advanced SQL with anything dynamic. Like optionally add more sophisticated calculated properties to the SELECT fields, conditionally JOIN in extra tables, use any of the DB engine's more advanced XML or JSON parsing features in a conditional way, support choosing <, > or =, etc.

The real problem is doing ad hoc SQL string concatenation. In the situations you point out, one could (and should) write a simple formatting library that makes it easy to join string literals (the operators) with dynamic data, the same way one would write a basic prepare wrapper around a SQL driver that lacked prepared statements--a simple (and proper--not regex hack) format string parser that quotes and concatenates its arguments. Doing this ensures you'll end up structuring your code for easier auditing of code and data admixtures--there should literally be a single line in the entire code base calling the abominable "escape" routine for quoting and escaping special characters. Ironically, this is the type of thing that's really trivial in C because it's so simple to write a small state machine (while + switch loop, with a variable for escape state) for parsing the format string and building a new string character by character.

> every sane sql client library supports some form of prepared statements out of the box

this is true

> they are really simple to use

this is not. The amount of boilerplate I've written to properly parameterize a common pattern like

      SELECT * FROM FOO WHERE 
      FOO.SomeKey IN (/* user-provided list of chosen values goes here */)

is grotesque.

I'm not sure I would classify a sql client library that doesn't support arrays as "sane". This may have been annoying in the past, I would expect modern libraries to handle basic features like 'IN" automagically.

    list = ['bar', 'baz', "Robert'); DROP TABLE Students;--"]
    Foo.where(name: list)
    # Foo Load (0.3ms)
    #  SELECT "foo".* FROM "foo"
    #   WHERE "foo"."name" IN (?, ?, ?)
    #  [["name", "bar"],
    #   ["name", "baz"],
    #   ["name", "Robert'); DROP TABLE Students;--"]]

How do you parameterize something like 'WHERE foo in (a, b, c, ...)' where a,b,c... is a variable length list of parameters?

1) dynamically adjust the number of parameters in your prepared statement

  params = ", ".join(["?"] * count)
  template = "SELECT ... WHERE foo in (%s)" % (params)
  # proceed to prepare a statement from the query template

2) use a subquery, insert data somewhere if necessary

  SELECT ... WHERE foo in (SELECT foo FROM foos WHERE some_value = 42)

3) if the maximum number of parameters is low, you can keep it variable by enabling parts of the query with boolean parameters:

  SELECT ... WHERE (test_param1 AND foo = param1) 
                   OR (test_param2 AND foo = param2)
                   OR (test_param3 AND foo = param3)