Avoiding SQL injection with raw-ish queries (MySQL FULLTEXT search)

[Chris Shenton]

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.

[Chris Shenton]

I finally discovered the "Using Bind Parameters in Text Blocks"
section of the SQLAlchemy manual -- very useful and very easy to use.
Perhaps this will help others who are trying to search against MySQL's
FULLTEXT index safely. FWIW, I'm doing this in Pylons.

Here's what I ended up doing:

t = metadata.engine.text("""
SELECT ROUND(MATCH(message) AGAINST(:message), 2) AS score,
facility,severity,message,explanation,solution,significance,os
FROM kb
WHERE MATCH(message) AGAINST(:message)
AND facility=:facility
AND severity=:severity
LIMIT :limit
""")
c.results = t.execute(message=text, facility=fac, severity=sev, limit=100).fetchall()

If you echo the SQL it's using, you can see how it quotes any query
parameters that have quotes in them. Slick.

Thanks for such a nice tool!

[Michael Bayer]

yeah i was going to say, bind params in literal text are always in
the :param style (since im an oracle veteran) and they get converted
later....glad you found it