[SOLVED] Database Insert Security

[liebs19]

Hi guys,

 

I have a general question about security when inserting into a database. I just now realized that magic quotes in turned on. I discovered it while testing the formatting of my mail function. One of the words I typed had a single quote in it that got escaped. My next step is to insert the data into a MySQL database. I don't have the option to turn off magic quotes because I don't have access to the php.ini. Does this mean that every time I use data sent by a POST that I need to stripslashes?

 

Are the magic quotes enough security when inserting into a database? Should I be doing more to make sure that I am not the victim of a SQL injection? Should I also use htmlentities? Are the other measures I should be taking? At what point does this become overkill?

 

Thanks,

liebs19

[liebs19]

Anyone have any input or suggestions?

[darkfreaks]

stripslashes wont work with magic quotes on try using

 

mysql_real_escape_string

[liebs19]

Thats odd, stripslashes appears to be working for the e-mail. I took the slash out before the single quote. I'll try mysql_real_escape_string.

 

Should I also be using htmlentites and/or any other functions?

[hostfreak]

I use something like:

    function clean_input($input)
    {
        if (get_magic_quotes_gpc())
        {
            $input = stripslashes($input);
        }
        
        $input = mysql_real_escape_string($input); //Escapes special characters in a string for use in a SQL statement; Rember to always use single quotes around your variable in your SQL statement. Requires a connection.
        $input = strip_tags($input); //Strip HTML and PHP tags from a string; allowable_tags can be defined, example: strip_tags($input, '<p><a>')
        $input = htmlentities($input, ENT_NOQUOTES); //Convert all applicable characters to HTML entities. quote_style: ENT_NOQUOTES; Will leave both double and single quotes unconverted. Use html_entity_decode to: Convert all HTML entities to their applicable characters
        
        return $input;
    }

    $to_insert = clean_input($_POST['to_insert']);

[darkfreaks]

that works too 

[liebs19]

What is the reasoning for using strip_tags and then htmlentities?

[darkfreaks]

strip_tags strips all PHP and HTML code

[roopurt18]

Here is my version, which is recursive for arrays and has a few other options:

 

<?php
  // my_clean
  // $data - data to clean before inserting into db
  // $add_sq - true to add single quotes, false to just clean
  // $force_string - true to treat the data as a string
  // $skip_strip - true to skip the strip slashes segment
  // RETURN: properly escaped and db safe data
  function my_clean($data, $add_sq = true, $force_string = false,
                                                           $skip_strip = false){
    if(is_array($data)){
      foreach($data as $key => $val){
        $data[$key] = $this->my_clean($val, $add_sq, $force_string);
      }
    }else{
      $data = trim($data);
      if(!is_numeric($data) || $force_string){
        if(get_magic_quotes_gpc() && !$skip_strip){
          $data = stripslashes($data);
        }
        if($add_sq){
          $data = "'" . mysql_real_escape_string($data) . "'";
        }else{
          $data = mysql_real_escape_string($data);
        }
      }
    }
    return $data;
  }
?>

[liebs19]

strip_tags strips all PHP and HTML code

 

Ok, I get it now. I was having a brain lapse on just exactly how htmlentites worked. I was thinking of it as only getting rid of the tags. Not all of the other stuff it does.

[liebs19]

I guess its about time I started to create some classes for myself that include functions such as this. Thanks for all the tips guys.