4 ms·

How are you turning the user supplied string Gijs in 't Veld into the SQL string literal string 'Gijs in \'t Veld' ? Is your method for doing so guar

How are you turning the user supplied string Gijs in 't Veld into the SQL string literal string 'Gijs in \'t Veld' ? Is your method for doing so guaranteed to always produce a valid SQL string literal which represents the supplied string? Even if the user supplied string contains, for example: * control characters * NULs * non-ASCII characters * Unicode combining characters * surrogate pairs ...? Wouldn't it be nice not to have to worry about that? Good. Then use a mechanism that lets you send the parameters as data outside of your SQL, such as parameterized prepared statements.

paulasmuth 11y ago
>> How are you turning the user supplied string into the SQL string literal string? Is your method for doing so guaranteed to always produce a valid SQL string literal which represents the supplied string? Yes. This is trivial. Any self-respecting junior programmer should be able to write this routine.
- biot 11y ago
  Make sure that junior programmer is a true Scotsman as well since they're more likely to have an apostrophe in their name.
- jameshart 11y ago
  With respect, most junior programmers, whether self respecting or otherwise, would have trouble even recognizing that there is a need to cover some of those cases I mentioned, let alone successfully accommodating them. Most likely you'll get a return "'" + replace(str, "'", "\\'") + "'" which only after the first bug report gets turned into a return "'" + replace(replace(str,"\\","\\\\"),"'","\\'") + "'" and that will have been arrived at after a lot of trial and error and incorrect numbers of backslashes. And if you think that is robust, you're likely in for a surprise when someone sends you some unicode data.
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 ago
  I 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 ago
    The 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...

paulasmuth 11y ago

>> How are you turning the user supplied string into the SQL string literal string? Is your method for doing so guaranteed to always produce a valid SQL string literal which represents the supplied string? Yes. This is trivial. Any self-respecting junior programmer should be able to write this routine.

biot 11y ago
Make sure that junior programmer is a true Scotsman as well since they're more likely to have an apostrophe in their name.
jameshart 11y ago
With respect, most junior programmers, whether self respecting or otherwise, would have trouble even recognizing that there is a need to cover some of those cases I mentioned, let alone successfully accommodating them. Most likely you'll get a return "'" + replace(str, "'", "\\'") + "'" which only after the first bug report gets turned into a return "'" + replace(replace(str,"\\","\\\\"),"'","\\'") + "'" and that will have been arrived at after a lot of trial and error and incorrect numbers of backslashes. And if you think that is robust, you're likely in for a surprise when someone sends you some unicode data.

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 ago
I 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 ago
  The 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...