PDO uncaught error posting form data

Question

struggling to see what the error is here, trying to submit some fields into the db with a password salt also its to add new users to my site (I will be improving the security) but struggling a bit as I am getting the correct results with var_dump please see below as I cannot spot the issue?

Here is the form:

<form action="actions/add_emp.php" method="post">

  <input type="text" name="user" placeholder="Username" required="required"  /><br/>
  <input type="text" name="pass" type="password" placeholder="Password"/></label><br/>

    <input type="text" name="firstname" id="name" required="required" placeholder="Firstname"/><br />
    <input type="text" name="lastname" id="email" required="required" placeholder="Lastname"/><br/>
    <input type="email" name="emailAddress" id="city" required="required" placeholder="Email Address"/><br/>
    <input type="text" name="extension" id="extension" required="required" placeholder="Extension Number"/><br/>
    <select name="title">
        <option  selected disabled>Please Select a Job Title...</option>

        <option disabled></option>
        <option disabled>Helpesk:</option>
        <option value="Technical Support Advisor">Technical Support Advisor</option>
        <option value="Deputy Team Leade">Deputy Team Leader</option>
        <option value="Team Leader">Team Leader</option>
        <option value="Incident Resolution Advisor">Incident Resolution Advisor</option>

        <option disabled></option>
        <option disabled>Call Centre:</option>
        <option value="Technical Support Advisor">Technical Support Advisor</option>
        <option value="">Deputy Team Leader</option>
        <option value="">Team Leader</option>

        <option disabled></option>
    </select>
    <br><br> 
    <input type="submit" value="Add User" name="submit"/><br />
</form>

Here is the action script:

 <?
      error_reporting(E_ALL);
     ini_set('display_errors', 1);
  if(isset($_POST['submit'])){

    include_once'../../config.php';

    $dbh = new PDO("mysql:host=$hostname;dbname=dashboardr",$username,$password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

    if(isset($_POST['user']) && isset($_POST['pass'])){
    $password=$_POST['pass'];
    $sql=$dbh->prepare("SELECT COUNT(*) FROM `user_login` WHERE `username`=?");
    $sql->execute(array($_POST['user']));

    if($sql->fetchColumn()!=0){

    die("User Exists");

    }else{
     function rand_string($length) {
      $str="";
      $chars = "MY SALT LIVES HERE - NOT THE MOST SECURE I KNOW!";
      $size = strlen($chars);
      for($i = 0;$i < $length;$i++) {
       $str .= $chars[rand(0,$size-1)];
      }

      return $str; 
     }
     $p_salt = rand_string(20); 
     $site_salt="MYCUSTOMSALT"; 
     $salted_hash = hash('sha256', $password.$site_salt.$p_salt);

     $sql=$dbh->prepare("INSERT INTO `user_login` (`id`, `username`, `password`, `psalt`, `firstname`, `lastname`, `emailAddress`, `extension`) 
                            VALUES (NULL, ?, ?, ?, '".$_POST["firstname"]."','".$_POST["lastname"]."','".$_POST["emailAddress"]."','".$_POST["extension"]."';");

var_dump($sql); 
     $sql->execute(array($_POST['user'], $salted_hash, $p_salt));

     header ('Location: ../list_emp.php');


    }
   }
  }
  ?>

I am fairly new to PDO so I am still learning.

Oh and here is the result from the var_dump:

object(PDOStatement)#3 (1) { ["queryString"]=> string(203) "INSERT INTO `user_login` (`id`, `username`, `password`, `psalt`, `firstname`, `lastname`, `emailAddress`, `extension`) VALUES (NULL, ?, ?, ?, 'firstname','lastname','email@email.com','1234';" } 

Here is the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2' in /Applications/MAMP/htdocs/dashboardr v3.2.3/admin/actions/add_emp.php:72 Stack trace: #0 /Applications/MAMP/htdocs/dashboardr v3.2.3/admin/actions/add_emp.php(72): PDOStatement->execute(Array) #1 {main} thrown in /Applications/MAMP/htdocs/dashboardr v3.2.3/admin/actions/add_emp.php on line 72

Answer 1

The problem is you don't have a closing parenthesis on the query.

INSERT INTO `user_login` (`id`, `username`, `password`, `psalt`, `firstname`, `lastname`, `emailAddress`, `extension`)
VALUES (NULL, ?, ?, ?, 'firstname','lastname','email@email.com','1234';

Needs to be

INSERT INTO `user_login` (`id`, `username`, `password`, `psalt`, `firstname`, `lastname`, `emailAddress`, `extension`)
VALUES (NULL, ?, ?, ?, 'firstname','lastname','email@email.com','1234');

But your code has more serious concerns. You are using a prepared statement wrong. More to follow....

You never put user input directly into the query. This is a reason for prepared statements and parameters. You use the placeholders ? and then bind the parameters later in the execute statement like so:

 $sql=$dbh->prepare("INSERT INTO `user_login` (`id`, `username`, `password`, `psalt`, `firstname`, `lastname`, `emailAddress`, `extension`) 
                        VALUES (NULL, ?, ?, ?, ?, ?, ?, ?)");
 $sql->execute(
   array($_POST['user'], $salted_hash, $p_salt, $_POST["firstname"], $_POST["lastname"], $_POST["emailAddress"], $_POST["extension"])
 );