That’s… not parameterization the way most people understand it. It’s text templating, which is different and fraught with risk as the OP discovered.
For comparison: the Microsoft SQL client code will not substitute an escaped version of the query parameters into the query text! It sends the query with the named placeholders first, and then the parameter values encoded separately so that there’s zero risk of this kind of thing happening.
Also, this makes it trivial for the database engine to cache each query independently of the specific query parameter values.
The origin of the whole idea, I believe, was to have _prepared_ statements, ie not to have to pay the price of parsing the query and determining the query plan.
Yes I know all this sounds like "premature optimization" in this day and age when we are used to casual conversation in natural language with the computer and forgot how hard of a task it is already just to parse a formal language like sql, compared to finding the customer which id is 123 in a btree.
Yes. And that is what a client should 100% do from the security standpoint. But since you mention caching - from the perf standpoint, it could sometimes be beneficial for the query planner to know the values before coming up with the query plan. Sometimes I have done little optimizations by replacing prepared statement placeholders with baked-in numbers or known enum values.
In .NET land that's handled by EF Core 9 using the EF.Constant(...) and EF.Parameter(...) wrappers that either (safely!) inline the value or force parameterisation.