Why are String Formatted Queries Considered So Magical?
in [Python]
|
From: Stephen Hansen on 28 Jun 2010 00:02 On 6/27/10 8:48 PM, Carl Banks wrote: > I don't know the exact details of all of these, but I'm going to opine > that at least some of these are easily expressible with a function > call API. Perhaps more naturally than with string queries. For > instance, set operations: > > query1 = rdb_query(...) > query2 = rdb_query(...) > > final_query = rdb_union(query1,query2) > > or > > final_query = query1& query2 But, see, that's not actually what's going on behind the scenes in the database. Unless your "query1" and "query2" objects are opaque pseudo-objects which do not actually represent results -- the query planners do a *lot* of stuff by looking at the whole query and computing just how to go about executing all of the instructions. The engine of a SQL database is a pretty sophisticated little pieces of coding. Because SQL is declarative, the engine is able to optimize just how to do everything when it looks at the full query, and even try out a few different ideas at first before deciding on just which path to take. (This is an area where parametrized queries is even more important: but I'm not sure if MySQL does proper prepared queries and caching of execution plans). If you go and API it, then you're actually imposing an order on how it processes the query... unless your API is just a sort of opaque wrapper for some underlining declarative structure. (Like ORM's try to be) -- ... Stephen Hansen ... Also: Ixokai ... Mail: me+list/python (AT) ixokai (DOT) io ... Blog: http://meh.ixokai.io/
From: Carl Banks on 28 Jun 2010 00:12 On Jun 27, 8:52 pm, Stephen Hansen <me+list/pyt...(a)ixokai.io> wrote: > Then there's the type of SQL that results in DBA's having jobs-- and > deservedly so. Its *really* a very flexible and powerful language > capable of doing quite a lot to bend, flex, twist, and interleave that > data in the server while building up a result set for you. All right, I get it. I'm not talking about SQL, I'm talking about RDBs. But I guess it is important for serious RDBs to support queries complex enough that a language like SQL is really needed to express it--even if being called from an expressive language like Python. Not everything is a simple inner joins. I defer to the community then, as my knowledge of advanced SQL is minimal. We'll just have accept the risk of injection attacks as a trade off, and try to educate people to use placeholders when writing SQL. Carl Banks
From: Owen Jacobson on 28 Jun 2010 00:25 On 2010-06-28 00:02:57 -0400, Stephen Hansen said: > On 6/27/10 8:48 PM, Carl Banks wrote: >> I don't know the exact details of all of these, but I'm going to opine >> that at least some of these are easily expressible with a function >> call API. Perhaps more naturally than with string queries. For >> instance, set operations: >> >> query1 = rdb_query(...) >> query2 = rdb_query(...) >> >> final_query = rdb_union(query1,query2) >> >> or >> >> final_query = query1& query2 > > But, see, that's not actually what's going on behind the scenes in the > database. Unless your "query1" and "query2" objects are opaque > pseudo-objects which do not actually represent results -- the query > planners do a *lot* of stuff by looking at the whole query and > computing just how to go about executing all of the instructions. I believe that *is* his point: that we can replace the SQL language with a "query object model" that lets us specify what we want without resorting to string-whacking when our needs are dynamic, without changing the rest of the workflow. This is obviously true: each RDBMS does something very much like what Carl is proposing, internally. However, implementing such an API usefully (never mind comfortably) in a cross-language way is... difficult, and an RDBMS that can only be used from Python (or even from Python and other Smalltalk-like languages) is not terribly useful at all. -o
From: Carl Banks on 28 Jun 2010 00:43 On Jun 27, 9:02 pm, Stephen Hansen <me+list/pyt...(a)ixokai.io> wrote: > On 6/27/10 8:48 PM, Carl Banks wrote: > > > I don't know the exact details of all of these, but I'm going to opine > > that at least some of these are easily expressible with a function > > call API. Perhaps more naturally than with string queries. For > > instance, set operations: > > > query1 = rdb_query(...) > > query2 = rdb_query(...) > > > final_query = rdb_union(query1,query2) > > > or > > > final_query = query1& query2 > > But, see, that's not actually what's going on behind the scenes in the > database. Unless your "query1" and "query2" objects are opaque > pseudo-objects which do not actually represent results That's exactly what they are. Nothing is actually sent to the database until the user starts retrieving results. This is fairly common thing for some interfaces to do. For instance, OpenGL almost always returns immediately after a command is posted without doing anything. The driver will queue the command in memory until some event happens to trigger it (maybe a signal from the graphics that is is done processing commands, or the queue being full, or an explicit flush request from the user). Incidentally, OpenGL has its own DSL for per-vertex and per-pixel operations (known as vertex and fragment shaders) that replaces an older binary API. I daresay it's a little less at risk for an injection attack, seeing that the shaders run on the GPU and only run simple math operations. But you never know. Carl Banks
From: Ben Finney on 28 Jun 2010 00:53
Carl Banks <pavlovevidence(a)gmail.com> writes: > On Jun 27, 8:33 pm, Ben Finney <ben+pyt...(a)benfinney.id.au> wrote: > > Carl Banks <pavlovevide...(a)gmail.com> writes: > > > I'm disappointed, usually when you sit on your reinforced soapbox and > > > pretense the air of infinite expertise you at least use reasonable > > > logic. > > > > Kindly stop inventing straw men to attack; I deny the position you're > > painting for me. > > No, this is not a straw man, you are 100% percent guilty of circular > logic as I accused you of. The straw man you attacked is as I quoted above. The claim of circular logic is a separate point, and I addressed it in the rest of the message. Like you, I stripped the part of the message that I was not responding to specifically. > Plus, I will not kindly do anything for you unless you kindly stop > being condescending and self-righteous when answering questions and > start treating people with respect. I always endeavour to treat people with respect, and I leave it to the independent reader to decide how successful I am in that endeavour. Respect for a person, though, entails subjecting that person's statements to criticism where appropriate. Don't mistake exposure of flaws for self-righteousness, nor criticism for condescension. This isn't a forum for discussing my style, so I'll limit this message to merely addressing these slurs. -- \ “The long-term solution to mountains of waste is not more | `\ landfill sites but fewer shopping centres.” —Clive Hamilton, | _o__) _Affluenza_, 2005 | Ben Finney |