SQL FullText Indexes and SQL Injection

Question

Is there any known danger with exposing the use of a FullText index to internal and possibly public users?

Assuming the queries are properly parameterized, is there any way that a user could abuse the inputs to trigger a SQL injection or denial of service attack?

// SQL Server
select * from content_table WHERE CONTAINS((Title, Subtitle, Body), @fullTextSearch);

// MySQL
select * from content_table WHERE MATCH(Title, Subtitle, Body) AGAINST (@fullTextSearch);

// Oracle
select * from content_table WHERE CONTAINS(Body, @fullTextSearch);

The trigger for this question is the large variety of inputs a user could specify and the fact that the different SQL servers have different query syntax and at least some (MySQL) will return a syntax error if an invalid query is specified.

• 'FORMSOF(INFLECTIONAL, model NEAR airplane)'
• 'NEAR((term1, term2),5) AND term3'
• 'NEAR((term1, term2),5) OR NEAR((term3, term4),2, TRUE)'
• '+join +(>left <right)'
• 'electric INPATH (/purchaseOrder/items/item/comment)'

Answer 1

When talking about SQL injection the risk is that someone can introduce SQL keywords into the query itself by adding SQL to a data parameter.

This is why separation of data and query is absolutely critical. This normally plays out by using placeholder values, as in:

SELECT * FROM content_table WHERE MATCH(Title, Subtitle, Body) AGAINST (?);

In the case of search there's often two levels you need to be aware of:

• The SQL layer where you're using raw SQL keywords to express the query conditions, such as WHERE x=? AND y=?
• The search layer where you're expressing conditions within a string, like WHERE CONTAINS(?) which has a bound data parameter '"computer software" NEAR hardware)'

Note that the second form has a syntax within a string, so if you're exposing that you're not at risk of SQL injection per-se, but you may end up receiving a lot of syntax errors caused by bad user input that you need to handle.

In the first case if you need to compose the query conditions you need to follow the usual rules:

• Do not permit:
  • Inclusion of unknown fields into the query.
  • Inclusion of unknown operators into the query.
• Maintain as strict a separation between query and data as is practical.

You may need to parse the request's data into components that can be recomposed into a SQL query. This can get messy, especially if you're allowing a lot of latitude in how things can be searched, so try and keep it as simple and testable as possible.

If you have unit tests, include one that's deliberately hostile and tries to introduce invalid or injection-type data into the query. Ensure the data is properly contained.

Note: If you're calling a stored procedure using placeholder values, but the stored procedure composes SQL statements using concatenation you're still at risk, so you need to be absolutely certain you're keeping the data separated from the query. If you have a query with zero user data introduced in it there is no risk of SQL injection.