4 ms·
Again, my problem is that the APIs for parameterized prepared statements in SQL servers are often ludicrously awful which leads programmers to throw up their ha
by Pxtl 11y ago
Again, my problem is that the APIs for parameterized prepared statements in SQL servers are often ludicrously awful which leads programmers to throw up their hands for non-trivial SQL queries and just say "screw it, I'll just concatenate strings together".
Which could have easily been avoided if the APIs weren't crap, or if the API had exposed a method to say "escape all special characters in this string because it's getting concatenated into a query".
I mean, I can't count the DB/client-language APIs where writing a query that involves an IN clause with a provided array so that ["foo", "bar", "baz"]
became
SELECT * FROM MyTable WHERE Name IN ('foo', 'bar', 'baz')
without writing a non-trivial amount of fiddly code to handle building a list of parameters.
That's an obvious use-case, but writing it in a parametric format is a gigantic PITA so developers just concat strings. It's not right but it's the expected outcome of a bad interface that drives people away from the best practices.
- jklowden 11y agoI have to agree with your assessment of SQL APIs. Funny how not a single one seems to have been designed by someone who'd seen printf & scanf. In their defense, your obvious use case is evidence of a database design problem and shouldn't be in the API. The thing `Name` is in should itself have a name or type or class or group or something, capturing the reason foo, bar, and baz go together. Then the SQL is SELECT * FROM MyTable WHERE NameType = 'foo_group'; For the ad hoc alternative, you need an easy way to insert your array into a temporary table (again, API deficiency) or supply a table parameter, so you can do INSERT INTO #T values -- [your array here] SELECT * FROM MyTable WHERE Name IN (SELECT t from #t); or SELECT * FROM MyTable WHERE Name IN ?; -- vaporware SQL syntax
- Pxtl 11y agoThe table-valued approach is always my preferred option, but often that's a where bad APIs become a nuisance. I mean, look at all this boilerplate: http://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net http://stackoverflow.com/questions/10409576/pass-table-value...