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
-~----------~----~----~----~------~----~------~--~---
