Best way to insert data into database

[V]

I saw a lot of ways to do this but I don't know which is best. I'm more conerned abot security issues.. The current method I'm using is this.

 

after the db connection functions I made variables for each form input

 

$movie_title=$_POST['movie_title'];
$movie_desc=$_POST['movie_desc'];
$genre_id=$_POST['genre_id'];

 

and then the insert

 

$sql="INSERT INTO movies (movie_title, movie_desc, genre_id)
VALUES
('".$movie_title."', '".$movie_desc."', '".$genre_id."')";

 

 

It works but it feels like it's missing some things. What are your thoughts?

[kenrbnsn]

Never insert raw data into the database. At least use the function mysql_real_escape_string.

 

<?php
$movie_title=mysql_real_escape_string($_POST['movie_title']);
$movie_desc=mysql_real_escape_string($_POST['movie_desc']);
$genre_id=mysql_real_escape_string($_POST['genre_id']);
?>

 

Ken

[V]

Thank for the tip! I heard mysql_real_escape_string is the best prevention for mysql injection attacks.

[Mchl]

It is not the best, but does pretty good work if you know it's limits.

[phant0m]

You should also check whether magic_quotes are enabled and reverse its effects if it's enabled.

[V]

thanks phant0m, isn't having magic quotes enabled a good thing?

[Mchl]

It is not. All it does is it applies addslashes to every variable in GET, POST and COOKIE arrays. And since addslashes() is NOT as secure as dedicated mysql function (or functions dedicated to other databases), it forces you to apply stripslashes and then escape these variables properly. Failing to do so will result in double escaping (slashes stored into database).

[phant0m]

No, it's not.

 

It will escape all quotes on Strings that are passed via either GET, POST or COOKIEs (gpc).

This will protect you against the most basic forms of SQL injection, but it's easily circumvented.

But what happens if your host decides to switch magic_quotes off? What if you move your script to a server where it's disabled by default? Your script will fail.

 

Now, if you use mysql_real_escape_string in combination with magic_quotes, you have an effect that you most likely don't want.

 

Consider the following:

You have a basic text form on your page, and a user writes "it's" (double quotes indicate the start and the end of the string)

When you, in PHP get to see the value, magic_quotes will have changed it to:

"it\'s"

When you then pass it to mysql_real_escape_string, you will get

"it\\\'s" (escaping the backslash with another \, and it also escapes the single quote once more)

mysql_real_escape_string allows you to store "it\'s" in its actual form in the database, and that's exactly what you'll get when you query it: "it\'s".

 

In the end, you have escaped your data twice.

 

As of PHP6, magic_quotes has been removed entirely. It was introduced to prevent SQL injections because a lot of casual coders were unaware of the dangers of using unprocessed input. As a result, new PHP users didn't even know that feature existed, and what it was for. They simply assumed you could safely put anything into a query, which lead to numerous security issues when the magic_quotes setting was changed.

Another such "safety" feature is register_globals, which is due to be removed as well.

[Mchl]

As of PHP6, magic_quotes has been removed entirely.

 

To be more exact, it was to be removed, but right now there are no plans for a PHP6 release at all.

[-Karl-]

I use a function when inserting data, to what I call, "sanitize" the data.

 

      function sanitize($string, $trim = false)
      {
          $string = filter_var($string, FILTER_SANITIZE_STRING);
          $string = trim($string);
          $string = stripslashes($string);
          $string = strip_tags($string);
          $string = str_replace(array('‘', '’', '“', '”'), array("'", "'", '"', '"'), $string);
          if ($trim)
              $string = substr($string, 0, $trim);
              
          $string = mysql_real_escape_string($string);
          
          return $string;
      }

[Mchl]

I use a function when inserting data, to what I call, "sanitize" the data.

 

      function sanitize($string, $trim = false)
      {
          $string = filter_var($string, FILTER_SANITIZE_STRING);
          $string = trim($string);
          $string = stripslashes($string);
          $string = strip_tags($string);
          $string = str_replace(array('‘', '’', '“', '”'), array("'", "'", '"', '"'), $string);
          if ($trim)
              $string = substr($string, 0, $trim);
              
          $string = mysql_real_escape_string($string);
          
          return $string;
      }

Don't overdo things:

filter_var($string, FILTER_SANITIZE_STRING);

already strips tags and enocdes any quotes, so there's no need to do that again.

You should only use stripslashes if you know there are slashes to strip. Doing this every time you're risking removing slashes that should be there (if this was for a forum for example, it would be impossible to input c:\windows\style\paths or \php\namespaces )

[-Karl-]

It's not over doing it, not for the purpose I use it for. It does exactly what I want I have no need to input slashes into the database I use it for.

[ignace]

filter_var($string, FILTER_SANITIZE_STRING);

 

I never understood why you would sanitize input at all? Really, you are only helping the attacker in his efforts. If the data isn't what you expected like a piece of text where you expected a number or a negative where you expected a positive number, then just reject it. The end-user is messing with the variables, shut him up.

[V]

These are great tips thanks everyone! @Phantom thanks for the example I understand the use now.

 

Should one also use any injection prevention with forms that send email but don't store anything in the database?