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));
}

}```

Does the `customer_id` field exist in the table's defined fields? — SteveK, Apr 14 '20 at 20:28
in your query you have to replace all variables that start with a **$** with a **:**, so that binding can work, because you must have the same amount of placeholder as you bind to the query. You also must **replace all** the $ variables in your string — nbk, Apr 14 '20 at 20:36
@nbk I am copying what was already there and working such as **':username' => $username, ** and **timestamp = $time,**. I can't see why I would need to change the format that was already working ? — , Apr 14 '20 at 20:40

nbk · Answer 1 · 2020-04-14T22:28:42.770

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));
}

edited Apr 14 '20 at 22:28

answered Apr 14 '20 at 20:44

nbk

45,398
8
30
47

Thanks NBK, Getting somewhere ... I copy pasted verbatim and now I am getting this new error. Still working on it ```Parse error: syntax error, unexpected ':', expecting ')' in /home/public_html/admin/includes/Registration.php on line 202 – Apr 14 '20 at 20:55
There where 3 single quotes missing. try it now – nbk Apr 14 '20 at 20:58
Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in home/public_html/admin/includes/Registration.php:202 – Apr 14 '20 at 21:03
Stack trace: #0 /home/public_html/admin/includes/Registration.php(202): PDOStatement->execute(Array) #1 /home/public_html/admin/includes/Registration.php(92): Registration->addNewUser('sammyD', 'Sammy', 'Davis', '1234qwer', 'anyone@anyplace...') #2 /home/signlet2/public_html/admin/includes/process.php(164): Registration->register('sammyD', 'Sammy', 'Davis', '1234qwer', '1234qwer', 'anyone@anyplace...', 'anyone@anyplace...', 0) #3 /home/signlet2/public_html/admin/includes/process.php(20): register(Object(Database), Object(Session), Object(Configs), – Apr 14 '20 at 21:06
What is variable $time . Please echo it, because ***0 is not a valid time** and it seems it hasn't a valid time code. – nbk Apr 14 '20 at 21:10
I will paste the function (before I edited it) which works. Please look at the edit in my post – Apr 14 '20 at 21:14
I still don't see where you assign $time, and your code doesn't work as it was intended to do i refer you to the manual https://www.php.net/manual/de/pdo.prepare.php **all** Please also the new code, with all variables showm assigned – nbk Apr 14 '20 at 21:27
first line after function declaration function addNewUser($username, $firstname, $lastname, $password, $email) { $time = time(); – Apr 14 '20 at 21:35
As I said this is not my script. It works very well but I need to add a field to the user table so I can tie the users into my customers table – Apr 14 '20 at 21:36
@ nbk I am told not to use extended comments and my reputation is not high enough to move it to chat so I will try to solve it on my own. Thanks for the help – Apr 14 '20 at 21:44
So added my code to your function and there appears no error i add the code also to phofiddle, to see of there was a syntax error, but it also runs smoothly. Your error is some where else. – nbk Apr 14 '20 at 22:24
I solved the issue and posted it but was beaten up yet again. Thank you for the help – Apr 14 '20 at 22:35

add a field to a PDO query in mysql

1 Answers1