1

Is there a way to properly sanitize SQL inputs before passing them to the cursor.execute command in python? I know you are supposed to be able to make a construct like the following:

cursor.execute("insert into Attendees values (?, ?, ?)", (name, seminar, paid) )

to properly do this, but it's not working with my ODBC driver (4D V11) and pyodbc.

To be more specific, I am trying to write an INSERT statement, using the above format. When I run the code a record is inserted, but only the numerical variables are populated in the resulting record- the strings are blank. If I switch ODBC drivers (and databases, obviously) to pqodbc and a Postgresql database set up identically (at least for the fields I am trying to insert) the same code runs perfectly. This would imply to me that the problem is in the 4D driver, not in Python. That said, it is unlikely that the 4D driver is going to be fixed any time soon, so I am looking for other workarounds. Any suggestions? Thanks

Tim Penner
  • 3,551
  • 21
  • 36
Israel Brewster
  • 21
  • 2
  • If you're stuck with a poorly-supported module, you're probably best off trying to fix the module yourself. – Glenn Maynard Mar 03 '11 at 17:50
  • 2
    (But if it doesn't even handle *strings*, it's probably nowhere close to a production-quality module and you shouldn't be using it at all.) – Glenn Maynard Mar 03 '11 at 17:51
  • The python _module_ I am using is pyodbc. I think that's fine. It's the system-level 4D ODBC _driver_ that has issues. As that is proprietary code, I have to come up with a workaround. – Israel Brewster Mar 03 '11 at 22:16

1 Answers1

0

Unfortuately, as there are a lot of SQL-like dialects, there's not a consistent way of achieving sanitization. That's why a good driver should export a sanitization facility.

Python has a good set of string handling functions, so it should be pretty easy to fix your specific case, as suggested by your question comments.

A nice thing you could do while implementing this is looking at some driver's escaping system: it may contain some useful trick ;-)

Happy hacking!

Dacav
  • 13,590
  • 11
  • 60
  • 87