[CS-FSLUG] sql queries

[Vincent Danen]

* [2008-12-20 18:24:44 -0600] Timothy Butler wrote:

>> You will also need to read up on sql injection and do some processing 
>> of $pg before using it in an query.  The example I just presented is 
>> vulnerable to injection.
>
>
> 	And boy is that a pain! I've been slowly phasing out PHP-Nuke sites,  
> because they seem to be a walking injection target -- even more recent  
> versions...

Use PEAR's MDB2 for that.  There are also a few other tricks.  Here's
some code snippets from a web application I'm currently working on.

/*
  * function to sanitize $_REQUEST
  */
function sanitize_request($user_request)
{
     $system_request = array();

     foreach ($user_request as $key=>$val)   
     {
         if (isset($val))
         {
             if (is_array($val))
             {
                 foreach ($val as $key2=>$val2)
                 {
                     if (isset($val2))
                     {
                         $newval2                     = htmlspecialchars($val2);
                         $system_request[$key][$key2] = $val2;
                     }
                 }
             } else
             {
                 $newval               = htmlspecialchars($val);
                 $system_request[$key] = $newval;
             }
         }
     }
     return($system_request);
}

Then first thing in index.php, or any other page, use:

$form = sanitize_request($_REQUEST);

Then only reference stuff via $form['variable_name'].

So for instance, if you use page=foo, then $form['page'] is equal to
"foo".  Using the htmlspecialchars will help get rid of any funny stuff.

Also, with MDB2, for the db stuff it's even easier.  For instance:

$system_sql = sprintf("SELECT id FROM table WHERE something = %s", $db->quote($form['page']));

Let $db->quote() do the appropriate sanitization.  Of course, this just
gives you an idea... read the MDB2 documentation for all the fun stuff
you can do with MDB2, but it's a database abstraction layer that works
extremely well, and I find using quote() like that works amazingly well,
especially in conjunction with using htmlspecialchars() on absolutely
everything that comes from the user (be it a cookie, GET, POST,
whatever).

-- 
Vincent Danen @ http://linsec.ca/