[BangPypers] sql query parameters

[Carl Karsten]

On Sat, Oct 10, 2009 at 10:05 AM, Noufal Ibrahim <noufal at gmail.com> wrote:
> On Sat, Oct 10, 2009 at 7:59 PM, Carl Karsten <carl at personnelware.com> wrote:
> [..]
>> I need to inject...
>>
>> The line should be:
>>
>> cursor.execute(query, ('burger', '2009-09-10 12:00:00'))
>
> Ah. Then it's just a case of the API doing the quoting internally
> which points to a better API than a better language.

Hopefully [1] it is not "quoting", for reasonable definitions of
quoting.   "serializing" maybe, but in the case of sqlite, which
executes in the same memory space as python, I am not sure the data is
even serialized.

This is described in the sql92 spec:
"""         Many SQL-statements can be written to use "parameters" (which are
         manifested in static execution of SQL-statements as <parameters>
         in <SQL statement>s contained in <procedure>s in <module>s or as
         <embedded variable name>s in <SQL statement>s contained in <em-
         bedded SQL host program>s). In SQL-statements that are executed
         dynamically, the parameters are called dynamic parameters (<dynamic
         parameter specification>s) and are represented in SQL language by a
         <question mark> (?).
""" http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Perhaps some code showing both sides of the connection will clarify:


db=[
{'a':'x', 'b':2, 'c':3},
{'a':'y', 'b':5, 'c':6},
{'a':'z', 'b':8, 'c':9},
]

def whack():
    db=[]
    print "blam-o!"

def server(command, parameters=() ):
    """
    command is something like "find b=5"
    """

    def whack():
        db=[]
        print "blam-o!"

    print "parsing", command.__repr__()
    words=command.split()
    if words[0]=='find':
        expression = words[1]
        print "1 finding", expression.__repr__()
        field,value = expression.split('=')
        print "2 finding", field, value.__repr__()
        if value=='?':
            value=parameters[0]
        else:
            value = eval(value)
        print "3 finding", field, value.__repr__()
        rows = [ row for row in db if row[field]==value]
        print "found ", rows
        return rows

server("find a='y'")
server("find a=?",('y',))
server("find b=8")
server("find b=?",(8,))
server("find c=?",('whack()',))
server("find c=wh1ack()")

The critical part:
       if value=='?':
            value=parameters[0]
        else:
            value = eval(value)

value=parameters[0] is very light weight.  the data is already in the
correct data type
eval(value) is cpu intensive, and as the last line shows, subject to injection.

yes, this is a contrived example, but it does a pretty good job
demonstrating the 2 ways of getting values to the server.  It does not
at all demonstrate the query optimization caching stuff, but I have no
clue how to show that in something this simple.



-- 
Carl K