[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.
Kent
More information about the Tutor
mailing list