I have been programming PHP for about a year now, and I think it’s time I secure my clients websites by preventing SQL Injection. I have written a function that I can use pretty much anywhere user input is inserted into an SQL string. I know for the average login form some of it might be overkill, but I am using this function on a search engine I have created as well.
My code works great (meaning, I’m not asking how to FIX my code), but I am wondering how vulnerable it is to more advanced attacks (I guess I am asking if there is anything you guys might add or do differently)?
Here’s my code:
function filter_user_input($string) {
// Escapes special characters in the unescaped_string, taking into account the current
// character set of the connection so that it is safe to place it in a mysql query.
$string = mysql_real_escape_string($string);
// This removes additional bad words
// IS THIS NECESSARY ???
$badwords = array('drop','delete','insert','update','users','username','password');
$string = str_ireplace($badwords, "", $string);
// This converts multiple spaces into single spaces
// (which is important for fixing spacing if any words were removed above)
$string = preg_replace('/\\s+/', ' ', $string);
// This trims any spaces at beginning and end of variable
$string = trim($string);
// Finally, this checks to see if there are only spaces left, and if so sets $string to NULL
// (which important when searching a database for text, as a single space could return all entries in table)
$stringtest = preg_replace('/\\s+/', '', $string);
$stringcount = strlen($stringtest);
if ($stringcount == 0) {$string=NULL;}
return $string;
}
Thank you in advance, I appreciate your input! -Jesse
Just imagine what if sitepoint forum had such function in use. How can you post your code then?
Your whole function is useless, save for the very first function.
So, it must be no more than
function filter_user_input($string) {
// Escapes special characters in the unescaped_string, taking into account the current
// character set of the connection so that it is safe to place it in a mysql query.
$string = mysql_real_escape_string($string);
}
And don’t forget to enclose escaped value in quotes.
Also you can use prepared statements for the same purpose
You need no code but understanding.
First thing to understand is there is no such thing like SQL injection using data.
There is only proper sql syntax.
You should not use this function to protect.
It should be used to compose any SQL query. It must be bound to SQL, not to user input.
Once you do it, you can forget of injections, at least while you’re adding only data to your queries.
For the operator or identifiers - here goes real injection protection
No method is fully 100% secure. There are techniques though that can help to mitigate the worst of the issue, and mysql_real_escape_string is one of them.
Be aware that if you pass it strings that have already been escaped by magic quotes (happens automatically unless you turn it off), then the strings will be double escaped, which doesn’t look good.
The way to resolve that is to turn off magic quotes in your php.ini or .htaccess
If you don’t have access to those config areas, you can check if magic quotes is enabled and if it is, strip slashes from $_GET and $_POST inputs.
I noticed magic quotes is already on because I am getting double escaped quotes. Is there any reason why I shouldn’t just trust magic quotes? Or is it safer to turn them off and use mysql_real_escape_string?
Then why are you here? I thank you again for your help, but I am sure many people here would appreciate encouragement across the forum; rather than discouragement towards other members. Just my 0.02.
As you’ll see from the page, they are going to be removed from PHP by version 6.0, so taking steps now so that your code doesn’t rely on magic quotes is a wise move to make.
Great thank you for your help! I set the three magic quotes parameters in my php.ini to OFF, and now I am just using mysql_real_escape_string on most of my sql related user input (mostly on my login forms). I now see why it was stupid to use the following:
// This removes additional bad words
$badwords = array('drop','delete','insert','update','users','username','password');
$string = str_ireplace($badwords, "", $string);
However, as I mentioned in my first post, I have created a site search engine that searches across a table of products in a database. I know mysql_real_escape_string will prevent SQL injection, but since it adds slashes to any search queries that contain quotes, it hampers the result list because of the slashes. I know some people actually like to search using quotes to better refine their search (you can do this on google right?). What if I want to add this feature to my search function? Any advice on SQL injection prevention in a search engine situation?
What if I want to add this feature to my search function
You better learn this feature first. It is not as simple as sending quotes to the database as is.
You still think of input, not of SQL syntax.
This totally wrong approach will lead you real injection someday. I warrant it.
Once you fight imagined injections, you’ll fail to defeat a real one.
Escaping has nothing to do with searches, injections, quotes, anything you can imagine. It is technical thing. Syntax thing. It must be done, but it has no influence to the any application issue.
It is like gasoline. It required for your car to move, no matter which direction you want to travel!