3 ms·
> I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap. I don't know, but what I do know is that ple
by dozzie 11y ago
> I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap.
I don't know, but what I do know is that plenty of people still don't
understand how to use SQL APIs for their languages. Perl's DBI has variable
placeholders (similar to %x sequences in printf()) for dozen years already.
Python's DB-API 2.0 (PEP 249) has placeholders, too. Heck, even PHP has those.
And placeholders make any input sanitization unnecessary, because it's
the library that does the escaping part of constructing a query. And this way
is more convenient, too, as string concatenation rarely results in pretty
code.
- Etheryte 11y agoI think the issue stems from the fact that the thing you're likely to Google is "how to X in SQL", not "how to X in language Y SQL api".
- qb45 11y ago> And placeholders make any input sanitization unnecessary, because it's the library that does the escaping part of constructing a query. I quietly hope that they don't send concatenated query full of escapes, but some binary encoding of the query structure followed by serialized data parameters. Otherwise there would be a whole business of exploiting those escapers. And wasted bandwidth.
- dozzie 11y agoAccording to how DBI, DBAPI2, and PDO are designed, it's database driver's job to add escaping as appropriate (if not using binary encoding; but this is driver's call, too), so there's not much to exploit. Remember that you don't only quote single-quotes with a backslash, you also need to quote backslashes in raw input, too. Only the bandwidth could be of any concern, but I don't remember any database with a text-based communication protocol.
- lmm 11y ago> Only the bandwidth could be of any concern, but I don't remember any database with a text-based communication protocol. Huh? MySQL at least you can telnet into and query with a text protocol, no?
- lgas 11y agoNo.
- qb45 11y agoThere is much to exploit if the driver itself is buggy. Theoretically it's possible that between various 8bit/16bit encodings, locales, collations, client/server configuration mismatches somebody could find a creative way to smuggle some unescaped quotemark. Binary protocol which parses SQL commands client-side and treats strings as blobs of bytes removes any chance of SQL injection for good.
- Pxtl 11y agoOkay, here's some pseudocode that drives me nuts in every one of these SQL APIs. I have a Group and a list of names of persons. Group myGroup = "Groupia" Person[] myPersons = ["Foo", "Bar", "Baz"] I want to use that to generate the following query: SELECT * FROM dbo.Person WHERE name IN ('Foo', 'Bar', 'Baz') AND GroupName = 'Groupia' Every language that tries to do the printf-style approach sucks for this. In many of these cases these are OOP languages. How hard is it to provide an API that gives me concatenation-like semantics? It would be easy to design an API to allow putting the parameter calculation with concatenation-like semantics but without the problems of concatenation. For example (using Pythonic multi-line strings): query = new query() query.Text = """ FROM dbo.Person WHERE name IN (""" + query.ParamList(myPersons) + """) AND GroupName = """ + query.Param(MyGroup) + """ """ resultSet = Query.Execute() or somesuch nonsense - easier still if you're using a language that has good support for string interpolation. No worrying about lining up your parameters with their placeholders in the string which is functionally illegible in a long query. Good, legible programming involves keeping things that are related close to each other, so good SQL generation would logically involve having parameter's meaning as close as possible to their usage. printf-like syntaxes fail at this.