MATCH...AGAINST question

masterlayouts · May 22nd, 2012, 04:00 PM

Hello sir. I would like to ask you a question regarding the sanitation of the user input (from form) in queries that involve fulltext search with MATCH ... AGAINST.

Usually I sanitize all user input, for example I may use something like

Code:

$q = htmlspecialchars(strip_tags(trim($_POST['userInput'])));

I may also use something like mysql_real_escape() if in a hurry or probably prepared statements.

However, in a search like if I want to search for "John's Bar" in a Boolean mode (query bellow it's not, but the idea should be clear), if I sanitize the input I think it will not produce the same expected results. At the other end there are the bad guys that may try sql injection attempts or who knows what else.

Code:

$query = 'SELECT article FROM articles WHERE MATCH(title, articles, tags) AGAINST($q)';

vs

Code:

$query = 'SELECT article FROM articles WHERE MATCH(title, articles, tags) AGAINST($_POST["userInput"])';

Does the database handle by itself the input for example escaping the quotes?
Should the input be sanitized even if not used outside the WHERE ... MATCH...AGAINST clause? (no output on the page, no further use in subsequent queries.
As always I look forward to hearing your thoughts and follow your best advise. Thank you and I apologies if there are syntax errors as I am very tired. I hope I wrote something that make sense.

Rod Stephens · May 22nd, 2012, 04:39 PM

Hi MasterLayouts ;-)

Good question. Your query is *probably* safe but I would only assume so if your users are responsible people that you know and trust, like coworkers who are trying to do their jobs. If the program will be accessed by outside users, for example in the Internet, I would assume someone will eventually do something either stupid or malicious and get you in trouble.

I try to sanitize input if possible but, as you mention, you can have trouble when a valid value contains special characters like quotes.

Some database tools provide a method to get around this issue. In .NET you can build a Command object that represents a query and attach Parameter objects to it. The query holds placeholders (SELECT * FROM People WHERE Name='@') and the parameter objects hold the values to plug in for the placeholders. Then if the user enters something crazy like X' OR TRUE, the database will not interpret the value and instead look for names that actually match X' OR TRUE. That won't happen so the user won't be able to break into the database by using SQL injection in that way.

I don't know if your database tools support this type of query. Most of my work is with SQL Server and other databases using the .NET database libraries where this technique works.

In MySQL, you may be able to use mysql_real_escape_string. It escapes special characters, although this post:

How to prevent SQL injection with dynamic tablenames? indicates that it may have trouble with back ticks.

You might be able to write your own code to escape every non-alphanumeric character. Or you might be able to remove the most unusual characters and escape those that remain.

Another trick that can be useful is to execute the query in a transaction and see how many rows it affects. If you expect it to affect 1 row but the database indicates that it will affect 15,000 rows, you can roll back the transaction. Often a SQL injection attack will try to return more rows than should be allowed so again, if you expect a query to return 1 - 10 rows but it returns 800, don't show the results to the user.

masterlayouts · May 23rd, 2012, 03:34 AM

It is a very interesting observation the fact that mysql_real_escape_string() does not take into consideration the backtick (`). Obviously strip_tags() and htmlspecialchars() have other purposes. Wow!

Practically it means that all websites created with the mysql extension are vulnerable. I mean... really vulnerable... The only solution would be to use some str_replace() to fix the issue and probably very few people were thinking to `. After all, I have no knowledge of a PHP book bringing to attention this issue. No wonders php recommends not to use it anymore. They recommend mysqli or PDO.

Now mysqli as defense mechanism against sql injections is using prepared statements. They say there is no chance of injection using such parametrized statements (as far as used correctly).

The equivalent to your example:

Code:

SELECT * FROM People WHERE Name='@'

will be

Code:

SELECT * FROM People WHERE Name=?

So I guess my query should look like:

Code:

SELECT article FROM articles WHERE MATCH(title, articles, tags) AGAINST(?)

Besides this do you recommend more?

I would prefer not to use a regular expression and a function to eliminate the problematic characters unless I need to.

On an unrelated note, I was eager to read other books you wrote. I am not surprised that the other books have maximal reviews. For somebody that is used with php and has very little experience with OOP, is it difficult to make the step to a higher level language as C#? Is it something more than the syntax to worry about it?

Rod Stephens · May 24th, 2012, 02:05 PM

Quote:

Practically it means that all websites created with the mysql extension are vulnerable. I mean... really vulnerable...

Well, it's not quite that grim, but there is a danger. You can further reduce the chances of a problem by restricting the queries that the user can perform. For example, if users select items from a list instead of typing them in, then they can't enter dangerous garbage.

And you can check the number of records selected to make sure the result makes sense. And compiled statements with objects representing parameters make things a lot safer.

Quote:

On an unrelated note, I was eager to read other books you wrote. I am not surprised that the other books have maximal reviews. For somebody that is used with php and has very little experience with OOP, is it difficult to make the step to a higher level language as C#? Is it something more than the syntax to worry about it?

If you're pretty good with php or some other language, then you shouldn't have too much trouble moving to Visual Basic, C#, Java, or C++. In fact, you'll probably find Visual Studio (used by C#, Visual Basic, and Microsoft's version of C++, which is called Visual C++) to be amazingly helpful once you get used to it.

As far as my books go,
Stephens' C# Programming with Visual Studio 2010 24-Hour Trainer and
Stephens' Visual Basic Programming 24-Hour Trainer provide introductions to C# and Visual Basic respectively. They're aimed at complete beginners with no programming experience so you may find them too easy. Or you may want to give one a try and just blast through it as quickly as you can, and then move on to a harder book.

My book
Visual Basic 2010 Programmer's Reference is a more advanced book that takes a more in-depth look at Visual Studio. It's not as tutorial but you may be able to learn to program Visual Basic from it because of your previous experience. But it is Visual Basic. If you'd rather learn C# or some other language, I don't have a more advanced book for that.

As for which language should you learn, they're all good choices, although C# and Java seem to be in a bit more demand right now.