[Tutor] OT SQL (but through Python...)

[Kent Johnson]

Liam Clarke wrote:
> Hi, 
> 
> Well thanks Kent, after a bit of puzzlement I feel like I'm getting it. 
> Pysqlite takes care of correct quotation marks for me, but it's only
> good for parameters.

Right, you still hard-code the rest of the query.
> 
> so to generate 'select * from foo if A = "Bat"' I can hand cx.execute
> 'Bat', but I still have to
> insert A.

Yes, assuming you mean 'still have to insert A into the SQL'

> So, my select statement generation now looks like this, bit rough at mo.
> 
> def searchTable(self, table, fields):
>         initReq = 'select * from %s where ' % table
>         reqs=[]
>         flatTuples=[]
>         for (column, values) in fields.items():
>             if not values:
>                 #Don't search for None/NULLs 
>                 continue
>             reqs.append('%s = %%s' % column)
>             values = self.checkForBad(values)
>             flatTuples.append(values)
>             
>         request = initReq + " and ".join(reqs)
>         table_data = self.execRequest(request, flatTuples)
>         table_data.append(self.tableColumns[table])
>         return table_data 

Looks good.

> 
> So it'll pass a string stating 'select * from foo where A = %s and B =
> %s' to execute, along with the two parameters to insert. But, it runs
> each user entered value through this first:
> 
> def checkForBad(stringA):
>         if ';' in stringA:
>             stringA.replace(';', '')
>         return stringA

Should be
   stringA = stringA.replace(...)
as strings are immutable.

> 
> to catch semi-colons, there will be no legitimate reason for them in this db. 
> Are there any other special characters I should be checking for? Is
> there anyway to allow semi-colons without  opening up the nasty
> vunerability, as I may need semicolons galore one day...

I don't think you need checkForBad() at all. When you pass the parameters to the db, it becomes the 
db's responsibility to do any necessary quoting.

Kent