question about user input security on a search box

[php beginner]

Hello,

 

This is my first post here.  This looks like a great place!

 

I'm working on building a PHP/MySQL website that has a search box.  So if the site visitor typed "soccer tournament" it would find all of the stuff that has to do with soccer tournaments on the website.

 

I've determined that the site visitors will never need to type anything other than alpha-numeric characters to find what they are looking for.  So here is my basic strategy to protect the database against SQL-injection attacks:

 

1) convert all the characters from the user input to lowercase using strtolower()

2) replace all apostrophes or commas with a blank space using str_ireplace()

3) explode each separate word into its own array entry using explode()

4) any blank spaces that got placed into this array will be removed programmatically

5) check to see if each array entry is alphanumeric by using ctype_alnum()

6) if any part of the query fails the alphanumeric test (step 5), the user's search is blocked and not allowed to proceed.

7) if the query passes step 5, it's allowed to proceed and search the database for search results

 

This would make sure that something like this would pass:

top five soccer tournaments

 

But something like this would fail:

/php ? ` DROP TABLE

 

So my question is this: if the only search input that is allowed to "pass" and access the database are those that consist exclusively of alphanumeric characters, is that enough to guarantee prevention of an SQL-injection attack?

 

Or maybe phrased differently: is there a way to cause an SQL injection attack into a search box by using only alphanumeric characters?

 

I could also create further processing where troublesome words like DROP TABLE DELETE RECORD are scrubbed from the string.  But I think that restricting allowable database query text to consist only of alphanumeric characters will be enough to guard against SQL injection attacks.  But I don't know for sure, so that's why I am asking!

 

Thanks for any insights into this.  I know there are things like prepared statements and data binding.  I'm in the process of learning about those. But what I'm looking for here is an exact answer to my one specific question: is alphanumeric-only text in a search query against a mysql database safe by design?

[Psycho]

You are making it way harder than it needs to be. Allow the user to input anything they want and just use mysql_real_escape_string() on the input. If the user enters "/php ? ` DROP TABLE" it will be safe to run and they just won't get any matching results

 

EDIT: The only thing you might think about doing is splitting the words into separate pieces so if the user types in "soccer tournament" you could preset results that match "soccer" or "tournament"

[Ninjakreborn]

I agree, it does not have to be that involved to be very secure.  If you want to get a little more safe, play around with something like this: http://www.krio.me/cleaning-php-input-variables-before-mysql-insertion/