php error: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Question

I'm making an API for my project with php and mssql (sql server). I wrote this code but its giving me erros. I have checked the parameters and the values but I can't see the problem. any help would be appreciated. The error is this: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\wamp\www\sarfeh\api\objects\user.php on line 40 I have marked the line 40 in this code bellow. Ans this is what $hash has: $2y$10$BTog4ZdjkrDsH9Hw/FjuD.myHiz61o6SOUDy4KuvoB2dGDQV9Vl4u

EDIT: If I pass the password without hashing (the 11111), this works, but with hashing I get the error.

function create(){

    /
    // insert query
    $query = "INSERT INTO " . $this->table_name . "
    (first_name,last_name,phone,password_hash)
    values (:first_name,:last_name,:phone,':password')";

    /$this->first_name="arassssh2";
    $this->last_name="arasssh2";
    $this->phone="326981";
    $this->password="111111";
    // prepare the query
    $stmt = $this->conn->prepare($query);
    // bind the values
    $stmt->bindParam(':first_name', $this->first_name, PDO::PARAM_STR);
    $stmt->bindParam(':last_name', $this->last_name, PDO::PARAM_STR);
    $stmt->bindParam(':phone', $this->phone, PDO::PARAM_STR);
    $hash = password_hash($this->password, PASSWORD_BCRYPT);
    $stmt->bindParam(':password', $hash); //line 40


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

    return false;

}

Remove ' before and after :password. Change ` (:first_name,:last_name,:phone,':password')` to ` (:first_name,:last_name,:phone,:password)`. — Zhorov
– Zhorov, Commented Feb 11, 2019 at 7:27
Why you are giving ':password' in your query? Refer this for more info w3schools.com/php/php_mysql_prepared_statements.asp — Omi
– Omi, Commented Feb 11, 2019 at 7:37

Zhorov · Accepted Answer · 2019-02-11 07:37:34Z

3

You don't need to surround your parameter name with '. Remove ' before and after :password. Change

$query = "INSERT INTO " . $this->table_name . "
    (first_name,last_name,phone,password_hash)
    values (:first_name,:last_name,:phone,':password')
";

to

 $query = "INSERT INTO " . $this->table_name . "
    (first_name,last_name,phone,password_hash)
    values (:first_name,:last_name,:phone,:password)
";

answered Feb 11, 2019 at 7:37

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arash Mohammadi Over a year ago

I removed the ' , the error changed to this: Uncaught PDOException: SQLSTATE[22001]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]String or binary data would be truncated.

Zhorov Over a year ago

@ArashMohammadi Your $hash variable length is more than column width in SQL Server.

Arash Mohammadi Over a year ago

Thanks! That was the problem! in Sql Server the password_hash field was 50, changed it to 250 and it worked! you saved me hours thanks!

saibbyweb · Accepted Answer · 2019-02-11 07:32:29Z

1

I believe line 40 should be :

 $stmt->bindParam(':password', $hash, PDO::PARAM_STR);

edited Feb 11, 2019 at 7:32

answered Feb 11, 2019 at 7:23

saibbyweb

3,3042 gold badges37 silver badges55 bronze badges

2 Comments

Arash Mohammadi Over a year ago

The @Zhorov answer solved it for me, but Thanks for reminding me the param type.

saibbyweb Over a year ago

You might consider upvoting the answer if it helped in any way :)

Collectives™ on Stack Overflow

php error: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related