Proper way to insert data into table with php and mysql?

Question

I am trying to insert data into table from a form submission. I've already insured that there is connection with the database. I would like to know if I am doing this properly. Here is my code.

   //INSERT INTO TABLE
   $name  = trim($_POST['name']); 
   $bio   = trim($_POST['bio']); 
   $email = trim($_POST['email']); 

      if(isset($name, $bio, $email)) {
          if($db->query("
            INSERT INTO users(name, bio,email, created,updated)
            VALUES ('{$name}', '{$bio}','{$email}', NOW())")) {
            echo 'number of rows effected by the INSERT:', $db->affected_rows; 

           }

       }

Please keep in mind, I am just new PHP, thanks for your help.

You should use [prepared statements](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496). — Lkopo, Sep 06 '14 at 10:45
This question appears to be off-topic because it is asking for a [codereview](http://codereview.stackexchange.com/). — Quentin, Sep 06 '14 at 10:45
Thanks Quentin, I will go ahead and posted on the codereview section. — Abulurd, Sep 06 '14 at 10:48
No, you are not doing it right. YOu have to escape your user input. Otherwise this can lead to syntax errors and is open to SQL injections. Use Prepared Statements — juergen d, Sep 06 '14 at 10:49

score 4 · Accepted Answer · answered Sep 06 '14 at 10:50

I'm presuming you've already created a PDO database connection in the lines preceding what you've posted.

The best and most secure way to do it is to use prepared statements and parameter binding.

$stmt = $db->prepare("INSERT INTO users(name, bio,email, created,updated)
        VALUES (:name, :bio, :email, NOW())");

You can then execute the query and bind values to the parameters with:

$stmt->execute(array("name"=>$name,
                     "bio"=>$bio,
                     "email"=>$email));

Alternatively, you can prepare the query as above and use the bindValue() function to bind values to the parameter and then execute it:

$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':bio', $bio, PDO::PARAM_STR);
$stmt->bindValue(':email', $email, PDO::PARAM_STR);
$stmt->execute();

Proper way to insert data into table with php and mysql?

1 Answers1