Did I do PDO right? First timer at prepared statements

Question

Just changed my previous question to reflect PDO changes everyone told me to make. Am I doing this right? Error reporting right? Is everything secure?

try{
$connection = new PDO('mysql:host=supertopsecret;dbname=supertopsecret;charset=utf8mb4', 
'supertopsecret', 'supertopsecret');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

//Query 1 - Insert Provider's Name
//if(isset($_POST['submit'])){ delete this? do I still use this? halp
$stmt1 = $connection->prepare("INSERT INTO 
`providers`(provider_first_name,provider_last_name,date_added)
VALUES (:providerfirstname, :providerlastname, NOW())");

//bind parameters:
  $stmt1->bindParam(':providerfirstname', $providerfirstname);
  $stmt1->bindParam(':providerlastname', $providerlastname);

//insert row
  $providerfirstname = $_POST['providerfirstname'];
  $providerlastname = $_POST['providerlastname'];

  $stmt1->execute();

//Query 2 - Insert Practices
$prov_id = $connection->lastInsertId();
  /*Get all values of practice_name[]:*/
  $practicename = $_POST['practice_name'];
  for ($i = 0; $i < count($practicename); $i++) {
      if ($practicename[$i]) {
          $practice_name_data = $practicename[$i];


      $stmt2 = $connection->prepare("INSERT INTO 
practices(prov_id,practice_name) VALUES (:prov_id,:practice_name)");

      $stmt2->bindParam(':prov_id', $prov_id);
      $stmt2->bindParam(':practice_name', $practice_name_data);

      $stmt2->execute();
     }
  }


echo '<center><h3><br><br><br>Thank you! Your provider has 
successfully been submitted to the database!</center></h3></br>';

} catch(PDOException $e){
echo "Sorry, there was an problem submitting your provider to the 
database. Please try again or copy and paste the error code below to 
the \"Report a Problem\" page and we will try to correct the problem. 
</b></br></br> Error: " . $e->getMessage();
die();
}
$connection = null;

1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 11You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 — mb9393, Jul 11 '19 at 19:55
Thanks for the help everyone, just updated my code above with PDO, feedback welcomed!! first time trying it — mb9393, Jul 12 '19 at 01:10

score 0 · Answer 1 · answered Jul 11 '19 at 19:57

0

You should use prepared statements instead of escaping yourself, see How can I prevent SQL injection in PHP?. But it's probably '$practicename[$i]'. It would be '{$practicename[$i]}', but easier:

foreach($practicename as $value){
   if($value!=""){
       $value =  mysqli_real_escape_string($connection, $value);
       $query2 = mysqli_query($connection,
       "INSERT INTO `practices`(prov_id,practice_name) 
        VALUES ('$prov_id','$value')");
   }
}

But again, abandon this and use Prepared Statements!

answered Jul 11 '19 at 19:57

AbraCadaver

78,200
7
66
87

Okay, I will switch from mysqli escapes to PDO and see if that fixes it, it's about time I started doing that anyway I guess, thank you – mb9393 Jul 11 '19 at 20:02
PDO is preferred and I would switch but see my second link for mysqli. – AbraCadaver Jul 11 '19 at 20:03
how do my prepared statements look? i updated code above – mb9393 Jul 12 '19 at 01:27

score 0 · Answer 2 · edited Jul 11 '19 at 22:39

0

Check this it may help you. Use PDO for insert.

$connection = new PDO("mysql:host=xxxx;dbname=xxxx;", "xxxx", "xxxx"); //database connection

for ($i = 0; $i < count($practicename); $i++) {
    if ($practicename[$i]) {
        $practice_name_data = $practicename[$i];

        $statement = $connection->prepare('INSERT INTO practices(prov_id,practice_name) VALUES (:prov_id,:practice_name)');

        $statement->bindParam(':prov_id', $prov_id);
        $statement->bindParam(':practice_name', $practice_name_data);
        // etc.

        $statement->execute();
    }
}

edited Jul 11 '19 at 22:39

Dharman

30,962
25
85
135

answered Jul 11 '19 at 20:19

CodeBreaker

395
2
9

hugely helpful, I've never used PDO so I'm a little lost, thank you!! – mb9393 Jul 11 '19 at 20:48
Welcome to the PDO.Cheers! :) :) @mb9393 – CodeBreaker Jul 11 '19 at 20:51
if I use this, i ditch using mysqli_real_escape_string entirely? – mb9393 Jul 11 '19 at 20:53
You could use `foreach` here instead of `for` loop. – Dharman Jul 11 '19 at 22:40
how do my prepared statements look? i updated code above – mb9393 Jul 12 '19 at 01:28

Did I do PDO right? First timer at prepared statements

2 Answers2