add a field to a PDO query in mysql

Question

I am trying to alter a login registration script that uses PDO queries for mysql.

I added the new field customer_id (int 11) to the users table and I am now trying to populate that field from the php registration script. The script that inserts the new user is below and I added

customer_id = $custId, to the query string and ':customer_id' => $custId, to the execute array statement.

However, when I run the code I get the console error of mismatched tokens yet I only added 1 extra parameter to each so I don't understand how it is mismatched. Maybe the syntax is wrong? I am following what was already working and it looks right.

    $userip = $_SERVER['REMOTE_ADDR'];

    $custId = '45';

    $query = "INSERT INTO users SET customer_id = $custId, username = 
    :username, firstname = :firstname, lastname = :lastname, password = 
    :password, userlevel = $ulevel, email = :email, timestamp = $time, ip = 
    '$userip', regdate = $time";

    $stmt = $this->db->prepare($query);

    return $stmt->execute(array(':customer_id' => $custId, ':username' => 
    $username, ':firstname' => $firstname, ':lastname' => $lastname, 
    ':password' => $password_hash, ':email' => $email));

UPDATE: This is the entire function that works before I edited it

```function addNewUser($username, $firstname, $lastname, $password, $email) {
    $time = time();
    /* If admin sign up, give admin user level */
    if (($this->functions->totalUsers() == '0') AND (strcasecmp($username, 
ADMIN_NAME) == 0)) {
        $ulevel = SUPER_ADMIN_LEVEL;

   /* Which validation is on? */
    } else if ($this->configs->getConfig('ACCOUNT_ACTIVATION') == 1) {
        $ulevel = REGUSER_LEVEL; /* No activation required */
    } else if ($this->configs->getConfig('ACCOUNT_ACTIVATION') == 2) {
        $ulevel = ACT_EMAIL; /* Activation e-mail will be sent */
    } else if ($this->configs->getConfig('ACCOUNT_ACTIVATION') == 3) {
        $ulevel = ADMIN_ACT; /* Admin will activate account */
    } else if (($this->configs->getConfig('ACCOUNT_ACTIVATION') == 4) && 
!$this->session->isAdmin()) {
        header("Location: " . $this->configs->homePage()); /* Registration 
Disabled so go back to Home Page */
    } else {
        $ulevel = REGUSER_LEVEL;
    }

    /* Hash password using PHP's inbuilt password_hash function - 
currently using BCRYPT - as of 2.5 */ 
    $password_hash = password_hash($password, PASSWORD_DEFAULT);

    $userip = $_SERVER['REMOTE_ADDR'];

    $query = "INSERT INTO users SET username = :username, firstname = 
:firstname, lastname = :lastname, password = :password, userlevel = $ulevel, email = :email, timestamp = $time, ip = '$userip', regdate = $time";
    $stmt = $this->db->prepare($query);
    return $stmt->execute(array(':username' => $username, ':firstname' => 
$firstname, ':lastname' => $lastname, ':password' => $password_hash, ':email' => $email));
}

}```

Answer 1

in your query you have to replace all variables that start with a $ with a :, so that binding can work.

So do this instead of your query

$userip = $_SERVER['REMOTE_ADDR'];

$custId = '45';

$query = "INSERT INTO users SET customer_id = :custId, username = :username, firstname = :firstname, lastname = :lastname, password = :password, userlevel = :ulevel, email = :email, timestamp = :time1, ip = :userip, regdate = :time";

$stmt = $this->db->prepare($query);

return $stmt->execute(array(':customer_id' => $custId, ':username' => $username, ':firstname' => $firstname, ':lastname' => $lastname, ':password' => $password_hash, ':ulevel'=>$ulevel, ':email' => $email,':time1'=>$time,':userip'=>$userip,':time'=>$time));

As you notice, i exchanged all '$ variables' with withg ':variables' and added them to the binding. I added both times as different placeholders, but it isn't necessary.

Your function as code without sql injection

function addNewUser($username, $firstname, $lastname, $password, $email) {
    $time = time();
    /* If admin sign up, give admin user level */
    if (($this->functions->totalUsers() == '0') AND (strcasecmp($username, ADMIN_NAME) == 0)) {
        $ulevel = SUPER_ADMIN_LEVEL;

   /* Which validation is on? */
    } else if ($this->configs->getConfig('ACCOUNT_ACTIVATION') == 1) {
        $ulevel = REGUSER_LEVEL; /* No activation required */
    } else if ($this->configs->getConfig('ACCOUNT_ACTIVATION') == 2) {
        $ulevel = ACT_EMAIL; /* Activation e-mail will be sent */
    } else if ($this->configs->getConfig('ACCOUNT_ACTIVATION') == 3) {
        $ulevel = ADMIN_ACT; /* Admin will activate account */
    } else if (($this->configs->getConfig('ACCOUNT_ACTIVATION') == 4) && 
!$this->session->isAdmin()) {
        header("Location: " . $this->configs->homePage()); /* Registration 
Disabled so go back to Home Page */
    } else {
        $ulevel = REGUSER_LEVEL;
    }

    /* Hash password using PHP's inbuilt password_hash function - 
currently using BCRYPT - as of 2.5 */ 
    $password_hash = password_hash($password, PASSWORD_DEFAULT);

    $userip = $_SERVER['REMOTE_ADDR'];

    $custId = '45';

    $query = "INSERT INTO users SET customer_id = :custId, username = :username, firstname = :firstname, lastname = :lastname, password = :password, userlevel = :ulevel, email = :email, timestamp = :time1, ip = :userip, regdate = :time";

    $stmt = $this->db->prepare($query);

    return $stmt->execute(array(':customer_id' => $custId, ':username' => $username, ':firstname' => $firstname, ':lastname' => $lastname, ':password' => $password_hash, ':ulevel'=>$ulevel, ':email' => $email,':time1'=>$time,':userip'=>$userip,':time'=>$time));
}