PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match

Question

I want to know about this error how can I sort out this error? Check my code, here is the function.

Here is the code that i am using: first is create function and second is error checking, how can I know about this error further please help me out, take me out from this problem.

function create(){

    $this->created=date('Y-m-d H:i:s');

    // insert query
    $query = "INSERT INTO " . $this->table_name . "
            SET
        uname = :uname,
        email = :email,
        contact_number = :contact_number,
        password = :password,
        access_level = :access_level,
        access_code = :access_code,
        status = :status,
        created = :created";


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


    $this->uname=htmlspecialchars(strip_tags($this->uname));
    $this->email=htmlspecialchars(strip_tags($this->email));
    $this->contact_number=htmlspecialchars(strip_tags($this->contact_number));
    $this->password=htmlspecialchars(strip_tags($this->password));
    $this->access_level=htmlspecialchars(strip_tags($this->access_level));
    $this->access_code=htmlspecialchars(strip_tags($this->access_code));
    $this->status=htmlspecialchars(strip_tags($this->status));


    $stmt->bindParam(':uname', $this->uname);
    $stmt->bindParam(':lastname', $this->lastname);
    $stmt->bindParam(':email', $this->email);
    $stmt->bindParam(':contact_number', $this->contact_number);


    $password_hash = password_hash($this->password, PASSWORD_BCRYPT);
    $stmt->bindParam(':password', $password_hash);

    $stmt->bindParam(':access_level', $this->access_level);
    $stmt->bindParam(':access_code', $this->access_code);
    $stmt->bindParam(':status', $this->status);
    $stmt->bindParam(':created', $this->created);

    // execute the query, also check if query was successful
    if($stmt->execute()){
        return true;
    }else{
        $this->showError($stmt);
        return false;
    }
}

You are binding one extra parameter (lastname) to the prepared statement. For better error handling use try-catch blocks. — Sumit Kumar, Feb 03 '20 at 11:57
P.S. running `htmlspecialchars(strip_tags(` on a password field is inappropriate. The password will never be shown on screen (I hope!) so there should be no need to scan it for problem tags or HTML. All you're doing here is making it so that the final password which gets hashed might not be the same as the one the user entered (e.g. if they used some characters like `<` or `>` in it, for example). And that could then mean that the user does not understand why they can't login. — ADyson, Feb 03 '20 at 11:57

score 1 · Answer 1 · answered Feb 03 '20 at 11:45

1

You are binding a parameter that does not exists in the SQL:

$stmt->bindParam(':lastname', $this->lastname);

answered Feb 03 '20 at 11:45

ndr458

144
1
2

Ronak Dhoot · Answer 2 · 2020-02-03T14:00:47.857

1

As your error states clearly "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match".

you are binding a param lastname which is not present in SQL query.

$stmt->bindParam(':lastname', $this->lastname);

remove this line

edited Feb 03 '20 at 14:00

answered Feb 03 '20 at 11:45

Ronak Dhoot

2,322
1
12
19

1

added explanation :) – Ronak Dhoot Feb 03 '20 at 14:01

Arusekk · Answer 3 · 2020-02-03T14:04:59.367

1

As pointed out in the other answers, you are binding a parameter that does not exists in the SQL.

$stmt->bindParam(':lastname', $this->lastname);

This line, if removed, should solve one issue. Another one is that you are using suspicious SQL syntax (a MySQL extension), and as far as I know, the standard syntax for inserting in SQL is

INSERT INTO table (x, y, z) VALUES (0, 1, 2);

and not

INSERT INTO table SET x=0, y=1, z=2;

I hope that it helps.

edited Feb 03 '20 at 14:04

answered Feb 03 '20 at 13:39

Arusekk

827
4
22

Have a look at https://stackoverflow.com/questions/861722/mysql-insert-into-table-values-vs-insert-into-table-set for that "suspicious" syntax. It's standard, and it's much more readable as you would not mix up the number of values that easy – Nico Haase Feb 03 '20 at 14:02

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match

3 Answers3