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

Kent Johnson kent37 at tds.net
Fri Feb 25 15:47:59 CET 2005

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.


More information about the Tutor mailing list