Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 22nd, 2012, 04:00 PM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default MATCH...AGAINST question

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.

Last edited by masterlayouts; May 22nd, 2012 at 04:18 PM.
Reply With Quote
  #2 (permalink)  
Old May 22nd, 2012, 04:39 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
The Following User Says Thank You to Rod Stephens For This Useful Post:
masterlayouts (May 23rd, 2012)
  #3 (permalink)  
Old May 23rd, 2012, 03:34 AM
Authorized User
Points: 143, Level: 2
Points: 143, Level: 2 Points: 143, Level: 2 Points: 143, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2012
Posts: 32
Thanks: 9
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #4 (permalink)  
Old May 24th, 2012, 02:05 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pattern match help! cooltechie BOOK: Beginning Regular Expressions 1 October 18th, 2012 07:04 PM
template match heightsofdelites XSLT 3 May 27th, 2010 04:01 AM
template match doesnt match the required node Tomi XSLT 2 March 12th, 2007 06:24 AM
Help!! i need to get a closest match tseng Access VBA 3 February 4th, 2005 06:44 PM
match containing variable csbdeady XML 1 October 25th, 2004 05:57 AM



All times are GMT -4. The time now is 12:22 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.