title: Switching mySQL insert and update statements to PDO prepared statements to prevent SQL injection
body: <p>I'm trying to switch these mySQl INSERT INTO and Update statements to PDO prepared statements (primarily to prevent SQL Injection), but I'm having some difficulty getting the syntax right.</p>
  
  <p>I’m currently using 2 types of INSERT/Update statements:</p>
  
  <p><em>Statement 1 - Names Are Hardcoded</em></p>
  
  <pre><code>$qry = "INSERT INTO customer_info(fname, lname, email, user_name, password)
  VALUES('$_POST[fname]','$_POST[lname]','$_POST[email]','$user_name','".sha1($salt + $_POST['password'])."')"; 
  $result = @mysql_query($qry)
  </code></pre>
  
  <p><em>Statement 2 - Adding Names Dynamically</em></p>
  
  <p>Instead of listing every element's name, most names are added dynamically (names are referenced as either $fieldlist or $setlist, and values are $vallist). The only names/values which are hardcoded are user_id or those which are arrays. I've included the full code for this below.</p>
  
  <pre><code>$result = mysql_query('UPDATE fit_table  SET '.$setlist.' WHERE user_id='.$user_id);
  if (mysql_affected_rows()==0) {
  $result = mysql_query('INSERT INTO fit_table ('.$fieldlist.') VALUES ('.$vallist.')'); };   
  </code></pre>
  
  <p><strong>This is what I've tried:</strong></p>
  
  <p><em>Statement 1 - Based on this post <a href="https://stackoverflow.com/a/60530/1056713">https://stackoverflow.com/a/60530/1056713</a></em></p>
  
  <pre><code>$stmt = $conn-&gt;prepare("INSERT INTO customer_info VALUES(:fname, :lname, :email, :user_name, :password)");
  $stmt-&gt;bindValue(':fname', $fname);
  $stmt-&gt;bindValue(':lname', $lname);
  $stmt-&gt;bindValue(':email', $email);
  $stmt-&gt;bindValue(':user_name', $user_name);
  $stmt-&gt;bindValue(':password ', $password);
  $stmt-&gt;execute();
  </code></pre>
  
  <p><em>Statement 2 - Based on this PDO wrapper <a href="https://github.com/Xeoncross/DByte/blob/master/DB.php" rel="nofollow noreferrer">https://github.com/Xeoncross/DByte/blob/master/DB.php</a> (referenced in this post <a href="https://stackoverflow.com/a/12500462/1056713">https://stackoverflow.com/a/12500462/1056713</a> )</em></p>
  
  <pre><code>static function insert($fit_table, array $fieldlist){
  $query = "INSERT INTO`$fit_table`(`" . implode('`,`', array_keys('.$fieldlist.')). '`) 
  VALUES(' . rtrim(str_repeat('?,', count($fieldlist = array_values('.$vallist.'))), ',') . ')';
  return DB::$p
  ? DB::column($query . 'RETURNING` user_id `', $fieldlist)
  : (DB::query($query, $fieldlist) ? static::$c-&gt;lastInsertId() : NULL);
  }
  </code></pre>
  
  <hr>
  
  <p>The full code for Statement 2 <em>(This is how names are added dynamically at present)</em></p>
  
  <pre><code>// INSERT    
  $fieldlist=$vallist='';
  foreach ($_POST as $key =&gt; $value) {
      if ($key=='pants_waistband'){$value= implode(',',$value);}        
      $fieldlist.=$key.',';
      $vallist.='\''.($value).'\',';
  }
  $fieldlist=substr($fieldlist, 0, -1);
  $vallist=substr($vallist, 0, -1);
  $fieldlist.=', user_id';
  $vallist.=','.$user_id;
  // UPDATE
  $setlist='';
  foreach ($_POST as $key =&gt; $value) {
      if ($key=='pants_waistband'){$value= implode(',',$value);}  
      $setlist.=$key .'=\''.$value.'\',';
  }
  $setlist=substr($setlist, 0, -1); 
  
  $result = mysql_query('UPDATE fit_table SET '.$setlist.' WHERE user_id='.$user_id);
  if (mysql_affected_rows()==0) {
  $result = mysql_query('INSERT INTO fit_table ('.$fieldlist.') VALUES ('.$vallist.')');}  
  </code></pre>