How to sanitize php crud words ?

Hi Experts!

Could you point the better way to sanitize crud words in php user's inputs?
I've been using sql_regcase but it's now depracated.

Thanks in advance
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
You can probably just use the PCRE expressions - modest changes to the REGEX code are usually all that is needed - adding delimiters and flags like "i" for case-insensitive matching.
http://php.net/manual/en/book.pcre.php
0
gr8gonzoConsultantCommented:
Sanitize how? Are you talking about removing words like "UPDATE" from a user's input? You really shouldn't need to sanitize those kinds of things specifically - if you're worried about SQL injection, then you should be using prepared statements or escaping the values before you use them as a parameter inside a SQL query. You really shouldn't ever have any user in a SQL query but outside of a parameter:

SELECT $user_input_fields FROM $user_input_table

...for example, is never a good idea and is asking for trouble. If you want to post the area of code where you're trying to use user data with a query, that might help us give better guidance.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Hello

Here is the peace of code I'm using to sanitize user's input.

/** 
  * DoFilter 
  *  
  * @param  mixed $value 
  * @param  array $modes 
  * @return mixed 
  * @static 
  * @since  1.0 
*/ 
 static protected function _doFilter($value, $mode) { 
  
         switch ($mode) { 
             case 'html': 
                 $value = strip_tags($value); 
                 $value = addslashes($value); 
                 $value = htmlspecialchars($value); 
                 break; 
          
             case 'sql': 
                 
                 // sql_replace 'depracated'
                 $value = preg_replace(sql_regcase('/(from|select|insert|delete|update|where|drop table|show tables|#|\*| |\\\\)/'),'',$value); 
                 $value = trim($value); 
                 break; 
         } 
  
         return $value; 
     } 

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

gr8gonzoConsultantCommented:
Okay, but how are you actually USING this function? Can you provide a code example of the usage of this _doFilter function?

Right off the bat, I see at least one problem. I could run "DROP     TABLE" and the code would be looking for one space between the words, so multiple spaces would get past the filter.  However, you shouldn't EVER be trusting user's input to construct a SQL query.

The only place a user's input should be able to be used / sanitized is in the parameter of a query.

Bad:
$query = "SELECT " . YourClass::_doFilter($user_input, 'sql') . " FROM sometable";

Acceptable:
$query = "SELECT fields FROM sometable WHERE field='".addslashes($user_input)."';";

If you try to use user input outside of parameter values, you run an extremely high risk of missing something and being vulnerable to SQL injection.  You are basically giving up control of your query to users in the first example, while the second example controls the entire query but lets the user's input only specify some criteria.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Hello

Here's a piece of code, that sanitezes the user inputs



<?php 
// EF 2015 : Sanitização
require_once('../library/sanitize/sanitize.php');
?>

<head>
.....
</style></head>

<body>
<table width="761" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
...          
   
       <?php ob_start();	  
		  
		  ini_set("display_errors", "OFF");
          
          //EF 2015  Call of Sanitize
          //$page = $_GET['page'];
          $page = Sanitize::filter($_GET['page']); 

		  switch ($page) {
		          
				  case 1:
                            ......

Open in new window


The complete class:

<?php 
/** 
 * Classe que contem os métodos que iram 
 * filtrar as entradas enviadas via GET e POST 
 * 
 * @filesource 
 * @author      Pedro Elsner <pedro.elsner@gmail.com> 
 * @license     http://creativecommons.org/licenses/by/3.0/br/ Creative Commons 3.0 
 * @abstract 
  * @version     1.0 
 */ 
 abstract class Sanitize { 
  
 /** 
  * Filter 
  *  
  * @param  mixed $value 
  * @param  array $modes 
  * @return mixed 
  * @static 
  * @since  1.0 
 */ 
   static public function filter($value, $modes = array('sql', 'html')) { 
  
         if (!is_array($modes)) { 
             $modes = array($modes); 
         } 
  
         if (is_string($value)) { 
             foreach ($modes as $type) { 
               $value = self::_doFilter($value, $type); 
             } 
             return $value; 
         } 
  
         foreach ($value as $key => $toSanatize) { 
             if (is_array($toSanatize)) { 
                 $value[$key]= self::filter($toSanatize, $modes); 
             } else { 
                 foreach ($modes as $type) { 
                   $value[$key] = self::_doFilter($toSanatize, $type); 
                 } 
             } 
         } 
  
         return $value; 
     } 
  
/** 
  * DoFilter 
  *  
  * @param  mixed $value 
  * @param  array $modes 
  * @return mixed 
  * @static 
  * @since  1.0 
*/ 
 static protected function _doFilter($value, $mode) { 
  
         switch ($mode) { 
             case 'html': 
                 $value = strip_tags($value); 
                 $value = addslashes($value); 
                 $value = htmlspecialchars($value); 
                 break; 
          
             case 'sql': 
                 
                 // sql_replace ficou depracated
                 $value = preg_replace(sql_regcase('/(from|select|insert|delete|update|where|drop table|show tables|#|\*| |\\\\)/'),'',$value); 
                 //$value = preg_replace("/(from|select|insert|delete|update|where|drop table|show tables|#|\*|--|\\\\|..\|\)/i","",$value);
                 $value = trim($value); 
                 break; 
         } 
  
         return $value; 
     } 
  
} 

Open in new window

Maybe I'm misconceptiong sometrhing.
Thank you for the attention you could give!
0
gr8gonzoConsultantCommented:
So it looks like you're trying to use a general-purpose utility for sanitizing stuff. That's probably a little better than what I was originally thinking you were doing, but it still seems a little overkill.

For example, you have this code:
 $page = Sanitize::filter($_GET['page']); 
		  switch ($page) {		          
				  case 1:

Open in new window


To me, that says that you have a system that expects a URL like: "mypage.php?page=1" where the "page" parameter is an ID number.

If "page" is always a number, you COULD just cast it to an integer like this:
$page = (int)$_GET["page"];
		  switch ($page) {		          
				  case 1:

Open in new window


This way, you are not trying to perform all sorts of heavy sanitizing functions and regular expressions on a simple number, and the final result will always be a number. If someone tries to say page=abc, then $page will simply be the number 0 (zero) because "abc" is not a valid integer.

To me, general-purpose sanitizing functions are usually a bad idea, because you're not thinking as much about the data. You're trying to just hand off the data sanitation to some generic class to do the job, and then you're using whatever it gives you. However, if that generic class doesn't do its job properly, then you could be trusting data that is not safe.

You should always know what you EXPECT the user input to look like, and you should sanitize data according to those expectations. Your system will perform better and you'll be in much better control of your security.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Just give me one more time... I'm going to see where else I sanitized that way
0
Ray PaseurCommented:
No points for this, please, because it's just restating what @gr8gonzo said very well, but the standard security mantra is "Accept only known good values."  That is not the same as "reject all known bad values."  The reason for the difference is that you already know what the good values are, but you may not have encountered all of the bad values.  You cannot outguess the bad guys - they will always have something new in their attacks.  So don't play into their hands - just make up the rules to be exactly the way you want them, and then you will have control.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Thank you for the advices!

In another place a form  is filled by the user's with:

name/  email/  phone number/  city/ uf/  subject/  message text

I read that with no sanitizing it could be transformed into a 'spam platform'.

So I test every textbox filled with this function:

function test_input($data)
{
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);

//    $data = preg_replace("/(from|select|insert|into|delete|update|from|where|drop|table|show tables|#|\*|--|\\\\|-|=|..\|\)/i",
//        "", $data);
//
    $data = preg_replace(sql_regcase('/(from|select|insert|delete|update|where|drop table|show tables|#|\*| |\\\\)/'),'',$data);


    return $data;
}

Open in new window


Any other suggestions?
0
Ray PaseurCommented:
Yes, here are some suggestions.  Do not use external data in a query string under any circumstances until it is sanitized.  And do not use external data to create a query verb.  Use some kind of code or use a RESTful routing algorithm to send the request to an appropriate script that contains a pre-canned query.  If you want a SELECT query, you can write the SELECT query in advance and only use the request data for placeholders in the WHERE clause.  If you want a DELETE query, you can write the query in advance and use the request data in the WHERE clause, and in the case of queries that mutate the database, you can ignore queries that are not from the POST method.

More information on CRUD and table maintenance is available in this article.
http://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html
0
gr8gonzoConsultantCommented:
So in that particular example you provided, let's say a user with the email address frommage187@gmail.com (probably not an actual, real address - just something I came up with) is trying to send you a message that says:

Hi,
I saw that you moved and I wanted to update my address book and change where you are located. Thanks!
- Fooey Barris

Your sanitizing function is going to strip out "from" in the email address, and "update" and "where" in the person's message. The message itself might still be legible, but you're not going to be able to respond without the person's real email address.

Is there any reason to really sanitize the contents of an email message and remove SQL keywords?

Not usually, because if you're inserting the contents into a database properly, then all you need to do is ensure that you are escaping slashes properly (and there is a separate function for that) so that people can't add SQL injection into their message contents. By sanitizing things blindly, you are potentially damaging valuable data and potentially still being exposed to risks that aren't yet discovered.

If you're going to make use of visitor-entered data in your database queries, you simply need to create your queries ahead of time, escape the visitor-entered data properly, and then use the escaped version in your query parameters.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Very, very good and elucidative explanation.
0
Ray PaseurCommented:
This might be useful:
http://shiflett.org/blog/2007/dec/php-advent-calendar-day-13

The meaning of the second phrase, "escape output," is simply that you run any browser output through htmlspecialchars() or similar to prevent sending unwanted Javascript to the client browser.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Again,  Very, very good and elucidative explanations.
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.