I'm doing a query against a MySQL table that has a column which has a fulltext index, so I need to do some raw-ish queries. Problem is that these open me up to SQL injection attacks. How do I avoid them -- bound variables? filtering of quotes and funny chars?
I create the index on a table already created with SQLAlchemy by: metadata.engine.execute("ALTER TABLE kb ADD FULLTEXT(message)") I later query against it with two exact matches and a MATCH...AGAINST the fulltext index: query = """ SELECT MATCH(message) AGAINST('%s') AS score, facility,severity,message FROM kb WHERE MATCH(message) AGAINST('%s') AND facility='%s' AND severity='%s' LIMIT %s """ query = query % (text,text,fac,sev,100) results = metadata.engine.execute(query).fetchall() This works nicely most of the time. If one of the parameters includes a single-quote, however, it breaks the query. This seems a avenue for an SQL injection attack. Problem is I can't figure out how to use SQLAlchemy's bound variables to re-implement the query. I can remove the SELECT part of the query string and do something like: query = """ MATCH(message) AGAINST('%s') AS score, facility,severity,message FROM kb WHERE MATCH(message) AGAINST('%s') AND severity='%s' """ query = query % (text,text,sev) results = select([query], engine=metadata.engine).execute().fetchall() But that doesn't help with the SQL injection. If I remove the AND portion from the query string and move it into the execute(): results = select([query], engine=metadata.engine).execute(severity=sev).fetchall() it doesn't help at all -- the SQL that's echoed indicates no "AND severity..." at all and I get too many results. Is there a way I can do the MATCH...AGAINST but using bound variables, or some other way that SQLAlchemy can protect me from injection attacks? If not, how do you recommend I sanitize the user-supplied query parameters so the query can't be exploited? Thanks. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---