Hacker News Reader

Comment by jameshart | Hacker News Reader

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.

View on HN

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

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.

Make sure that junior programmer is a true Scotsman as well since they're more likely to have an apostrophe in their name.

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.

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);

    SELECT * FROM MyTable WHERE Name IN ?;  -- vaporware SQL syntax

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

Reply on news.ycombinator.com