Function to avoid SQL injections & other threats

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
BooGiE_MaN
Forum Newbie
Posts: 5
Joined: Wed Jan 24, 2007 2:36 pm
Location: Cape Town, South Africa

Function to avoid SQL injections & other threats

Post by BooGiE_MaN »

Hi
Just joined and btw it spits out an error which doesn't make sense (I managed to get here after all):
Failed sending email :: PHP ::
DEBUG MODE
Line : 236
File : emailer.php
Anyway, want to know from you guys if the following function would make input safe, but allow me to develop relatively quickly:

Code: Select all

function makesafe(){
foreach ($_POST as $k => $v)
{
		if (is_array($v))
			{ $_POST[$k] = trim(mysql_real_escape_string(htmlentities($v, ENT_QUOTES))); }
		else
			{ $_POST[$k] = trim(mysql_real_escape_string(htmlentities($v, ENT_QUOTES))); }
/// question: would I be safe only going: 
// $_POST[$k] = trim(htmlentities($v, ENT_QUOTES));
	}
}
Usage would be, eg:

Code: Select all

if(isset($_POST['Submit'])){
makesafe();
$sql = "insert into `table` set `field` = '$_POST[value]' .............
Btw, I use htmlentities so that a rich-text editor can be used. I then use stripslashes(html_entity_decode()) to output the HTML. Any comments re security?

I read in one of the posts that $_GET items should be dealt with also; would you recommend (from the site-wide included file):

Code: Select all

foreach ($_GET as $k => $v)
{
		if (is_array($v))
			{ $_GET [$k] = trim(mysql_real_escape_string($v, ENT_QUOTES)); }
		else
			{ $_GET [$k] = trim(mysql_real_escape_string($v, ENT_QUOTES)); }
	}
}
Your thoughts???
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

input doesn't need to be htmlentities()-ized. You do that on output. mysql_real_escape_string() is for input.

Code: Select all

$name = (isset($_POST['name'])) ? mysql_real_escape_string($_POST['name']) : null;
// now insert into db
Then when you want to output:

Code: Select all

$name = htmlentities($mysql_result['name']);
echo $name;
Some reference:
http://pixelated-dreams.com/uploads/mis ... tSheet.pdf
viewtopic.php?t=29269
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Oh yea, one more... http://phpsec.org/

Not the greatest resource yet, but I imagine it will be in the near future.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Your function won't work if you pass it an array. You need to recursively call your function to deal with arrays.

In my applications, I do something like this, but keep in mind this is for outputting content, not inputting into the database. All you'd need to do is replace htmlentities() with mysql_real_escape_string() though.. enjoy.

Code: Select all

public function escape($input, $charset = 'UTF-8') 
{
   if (is_array($input))
   {
      $return = array();
      foreach($input as $key => $value)
      {
         if (is_array($value)) 
         {
            $return[$key] = $this->escape($value, $charset);			
         }
         else
         {
            $return[$key] = htmlentities($value, ENT_QUOTES, $charset);
         }
      }
				
      return $return;
   }
   else
   {
      return htmlentities($input, ENT_QUOTES, $charset);
   }
}
BooGiE_MaN
Forum Newbie
Posts: 5
Joined: Wed Jan 24, 2007 2:36 pm
Location: Cape Town, South Africa

Post by BooGiE_MaN »

Thanks guys
Out of curiosity, will using htmlentities() on its own (without mysql_real_escape_string()) prevent SQL injections?
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Post by matthijs »

@jcart: isn't it possible with your code to overload php's memory by feeding a very big deep nested array as input, and with that crashing/slowing the script/server?

@Bookieman: no, html_entities is a function used to escape output to HTML. mysql_real_escape_string is a function to escape output to a mysql db.
BooGiE_MaN
Forum Newbie
Posts: 5
Joined: Wed Jan 24, 2007 2:36 pm
Location: Cape Town, South Africa

Post by BooGiE_MaN »

htmlentities() converts ' " > etc to ' " > respectively.

Are there still characters that can't be converted and can cause SQL injections?
The thing is I don't want to have to go stripslashes() every time. With htmlentities I can just echo the data (if it is not actually HTML in which case you go html_entity_decode()).
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Post by matthijs »

BooGiE_MaN wrote:htmlentities() converts ' " > etc to ' " > respectively.

Are there still characters that can't be converted and can cause SQL injections?
The thing is I don't want to have to go stripslashes() every time. With htmlentities I can just echo the data (if it is not actually HTML in which case you go html_entity_decode()).
You don't have to use strip_slashes. Mysql_real_escape_string only escapes special characters. There are no extra slashes added to the data.

It's really very simple. Output to mysql: always use Mysql_real_escape_string or prepared statements. Output HTML: always use htmlentities(). That really is best practice. leave one out and you take unnecessary risk.
BooGiE_MaN
Forum Newbie
Posts: 5
Joined: Wed Jan 24, 2007 2:36 pm
Location: Cape Town, South Africa

Post by BooGiE_MaN »

If you echo data into a form element such as a textbox the slashes will appear and you'll need to stripslashes()
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Post by matthijs »

That has nothing to do with mysql_real_escape_string. That is caused by the evil doings of magic quotes.

To get rid of that, turn them off in php.ini or on script level with a function like this:

Code: Select all

if(get_magic_quotes_gpc()) {
    $in = array(&$_GET, &$_POST, &$_COOKIE); 
    while (list($k,$v) = each($in)) { 
      foreach ($v as $key => $val) { 
        if (!is_array($val)) { 
           $in[$k][$key] = stripslashes($val); continue; 
        } 
        $in[] =& $in[$k][$key]; 
      } 
    } 
    unset($in);
}
BooGiE_MaN
Forum Newbie
Posts: 5
Joined: Wed Jan 24, 2007 2:36 pm
Location: Cape Town, South Africa

Post by BooGiE_MaN »

great thanks matt
This will work on a server with magic quotes on or off right?
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Post by matthijs »

yes. It's a script from PHP architects guide to PHP security (Ilia Alshanetsky, great book). If you include this at the top of your script it will remove slashes in case magic quotes is on.
Post Reply