Oracle Text: How to sanitize user input

Question

If anyone has experience using Oracle text (CTXSYS.CONTEXT) and wondering how to handle user input when the user wants to search for names that may contain an apostrophe.

Escaping the ' seems to work in some cases, but not for 's at the end of the word - s is in the list of stop words, and so seems to get removed.

We currently change simple query text (i.e. anything that's just letters) to %text%. We're only using:

contains(field, :text) > 0

So search a search for O'Neil works, but Joe's doesn't.

Anyone using Oracle Text dealt with this issue?

Answer 1

Which escaping method are you using, curvy brackets or backslash?

Refer to Special characters in Oracle Text

Answer 2

Forget about sanitizing. Why? Refer to http://en.wikipedia.org/wiki/SQL_injection .

What kind of database interface API are you using? Perl DBI, ODBC, JDBC support parameterized queries or prepared statements. If you're using a native DBI and it doesn't support it, then God bless you.