Why are String Formatted Queries Considered So Magical?
in [Python]
|
From: Carl Banks on 27 Jun 2010 18:07 On Jun 24, 6:02 pm, Roy Smith <r...(a)panix.com> wrote: > In article <i00t2k$l0...(a)lust.ihug.co.nz>, > Lawrence D'Oliveiro <l...(a)geek-central.gen.new_zealand> wrote: > > > I construct ad-hoc queries all the time. It really isnt that hard to do > > safely. All you have to do is read the documentation > > I get worried when people talk about how easy it is to do something > safely. Let me suggest a couple of things you might not have considered: > > 1) Somebody is running your application (or the database server) with > the locale set to something unexpected. This might change how numbers, > dates, currency, etc, get formatted, which could change the meaning of > your constructed SQL statement. > > 2) Somebody runs your application with a different PYTHONPATH, which > causes a different (i.e. malicious) urllib module to get loaded, which > makes urllib.quote() do something you didn't expect. Seriously, almost every other kind of library uses a binary API. What makes databases so special that they need a string-command based API? How about this instead (where this a direct binary interface to the library): results = rdb_query(table = model, columns = [model.name, model.number]) results = rdb_inner_join(tables = [records,tags], joins = [(records.id,tags.record_id)]), columns = [record.name, tag.name]) Well, we know the real reason is that C, Java, and friends lack expressiveness and so constructing a binary query is an ASCII nightmare. Still, it hasn't stopped binary APIs in other kinds of libraries. Carl Banks
From: Roy Smith on 27 Jun 2010 18:20 In article <14e44c9c-04d9-452d-b544-498adfaf7d40(a)d8g2000yqf.googlegroups.com>, Carl Banks <pavlovevidence(a)gmail.com> wrote: > Seriously, almost every other kind of library uses a binary API. What > makes databases so special that they need a string-command based API? > How about this instead (where this a direct binary interface to the > library): > > results = rdb_query(table = model, > columns = [model.name, model.number]) > > results = rdb_inner_join(tables = [records,tags], > joins = [(records.id,tags.record_id)]), > columns = [record.name, tag.name]) > > Well, we know the real reason is that C, Java, and friends lack > expressiveness and so constructing a binary query is an ASCII > nightmare. Still, it hasn't stopped binary APIs in other kinds of > libraries. Well, the answer to that one is simple. SQL, in the hands of somebody like me, can be used to express a few pathetic joins and what I do with it could probably be handled with the kind of API you're describing. But, the language has far more expressivity than that, and a domain-specific language is really a good fit for what it can do. The problem is not so much that SQL queries are described as text strings, but that the distinction between program and data gets lost if you build the query as one big string. What you need (and which the Python API supplies) is the ability to clearly distinguish between "this text is my program" and "this text is a value which my program uses". Python has the same problem. If I had a text string, s, which I read from some external source, and wanted to interpret that string as an integer, I could do (at least) two different things. # Thing 1 myInteger = int(s) # Thing 2 myInteger = eval(s) for properly formed input, either one works, but thing 2 loses the distinction between program and data and is thus dangerous. Exactly like building a SQL query by smashing a bunch of strings together.
From: Ben Finney on 27 Jun 2010 19:35 Carl Banks <pavlovevidence(a)gmail.com> writes: > Seriously, almost every other kind of library uses a binary API. Except for the huge number that deal with text protocols or languages. > What makes databases so special that they need a string-command based > API? Because SQL is a text language. -- \ “In the long run, the utility of all non-Free software | `\ approaches zero. All non-Free software is a dead end.” —Mark | _o__) Pilgrim, 2006 | Ben Finney
From: Carl Banks on 27 Jun 2010 22:35 On Jun 27, 4:35 pm, Ben Finney <ben+pyt...(a)benfinney.id.au> wrote: > Carl Banks <pavlovevide...(a)gmail.com> writes: > > Seriously, almost every other kind of library uses a binary API. > > Except for the huge number that deal with text protocols or languages. No, not really. Almost all types of libraries have binary APIs, including those that deal with text protocols or language. Any control with string commands is something that's built on top of the binary API. And culturally, programmers interfacing those libraries expect to and are expected to use the binary API for low-level programming. RDBs, as a whole, either don't have binary APIs or they have them but no one really uses them. > > What makes databases so special that they need a string-command based > > API? > > Because SQL is a text language. Circular logic. I'm disappointed, usually when you sit on your reinforced soapbox and pretense the air of infinite expertise you at least use reasonable logic. Also, I was asking about databases. "SQL is a text language" is not the answer to the question "Why do RDBs use string commands instead of binary APIs"? Carl Banks
From: Carl Banks on 27 Jun 2010 22:51
On Jun 27, 3:20 pm, Roy Smith <r...(a)panix.com> wrote: > In article > <14e44c9c-04d9-452d-b544-498adfaf7...(a)d8g2000yqf.googlegroups.com>, > Carl Banks <pavlovevide...(a)gmail.com> wrote: > > > > > Seriously, almost every other kind of library uses a binary API. What > > makes databases so special that they need a string-command based API? > > How about this instead (where this a direct binary interface to the > > library): > > > results = rdb_query(table = model, > > columns = [model.name, model.number]) > > > results = rdb_inner_join(tables = [records,tags], > > joins = [(records.id,tags.record_id)]), > > columns = [record.name, tag.name]) > > > Well, we know the real reason is that C, Java, and friends lack > > expressiveness and so constructing a binary query is an ASCII > > nightmare. Still, it hasn't stopped binary APIs in other kinds of > > libraries. > > Well, the answer to that one is simple. SQL, in the hands of somebody > like me, can be used to express a few pathetic joins and what I do with > it could probably be handled with the kind of API you're describing. > But, the language has far more expressivity than that, and a > domain-specific language is really a good fit for what it can do. I'm not the biggest expert on SQL ever, but the only thing I can think of is expressions. Statements don't express anything very complex, and could straightforwardly be represented by function calls. But it's a fair point. > The problem is not so much that SQL queries are described as text > strings, No, it is the problem, or part of it. String commands are inherently prone to injection attacks, that's the main problem with them. > but that the distinction between program and data gets lost if > you build the query as one big string. That too. Carl Banks |