[Tutor] OT SQL (but through Python...)
kent37 at tds.net
Fri Feb 25 15:47:59 CET 2005
Liam Clarke wrote:
> 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
> for (column, values) in fields.items():
> if not values:
> #Don't search for None/NULLs
> reqs.append('%s = %%s' % column)
> values = self.checkForBad(values)
> request = initReq + " and ".join(reqs)
> table_data = self.execRequest(request, flatTuples)
> return table_data
> 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
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