{"error":{"text":SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens}} <

Question

pls am trying to create a login and registration system using pdo and mysql and this error keeps popping up, i read thru all the answers on this kind of error but cant seem to rectify mine... heres the code..

<?php

class userClass
{
  
/* User Login */
     public function userLogin($email,$password)
     {

          $db = getDB();
          $hash_password= hash('sha256', $password);
          $stmt = $db->prepare("SELECT uid FROM users WHERE email=:email AND password=:hash_password");  
          $stmt->bindParam("email", $email,PDO::PARAM_STR) ;
          $stmt->bindParam("hash_password", $hash_password,PDO::PARAM_STR) ;
          $stmt->execute();
          $count=$stmt->rowCount();
          $data=$stmt->fetch(PDO::FETCH_OBJ);
          $db = null;
          if($count)
          {
                $_SESSION['uid']=$data->uid;
                return true;
          }
          else
          {
               return false;
          }    
     }

     /* User Registration */
     public function userRegistration($email,$password,$fname,$lname,$dob,$sex,$country,$state_resd,$phone_no,$profile_pic)
     {
          try{
          $db = getDB();
          $st = $db->prepare("SELECT uid FROM users WHERE email=:email, password=:hash_password, fname=:fname, lname=:lname, dob=:dob, sex=:sex, country=:country, state_resd=:state_resd, phone_no=:phone_no, profile_pic=:profile_pic");  
          /**$st->bindParam("username", $username,PDO::PARAM_STR);**/
          $st->bindParam("email", $email,PDO::PARAM_STR);
          $st->execute();
          $count=$st->rowCount();
          if($count<1)
          {
          $stmt = $db->prepare("INSERT INTO users(email,password,fname,lname,dob,sex,country,state_resd,phone_no,profile_pic) VALUES (:email,:hash_password:fname,:lname,:dob,:sex,:country,:state_resd,:phone_no)");  
          /**$stmt->bindParam("username", $username,PDO::PARAM_STR) ;**/
          
          $stmt->bindParam("email", $email,PDO::PARAM_STR) ;
   $hash_password= hash('sha256', $password);
          $stmt->bindParam("hash_password", $hash_password,PDO::PARAM_STR) ;
          $stmt->bindParam("fname", $fname,PDO::PARAM_STR) ;
   $stmt->bindParam("lname", $lname,PDO::PARAM_STR) ;
   $stmt->bindParam("dob", $dob,PDO::PARAM_STR) ;
   $stmt->bindParam("sex", $sex,PDO::PARAM_STR) ;
   $stmt->bindParam("country", $country,PDO::PARAM_STR) ;
   $stmt->bindParam("state_resd", $state_resd,PDO::PARAM_STR) ;
   $stmt->bindParam("phone_no", $phone_no,PDO::PARAM_STR) ;
   $stmt->bindParam("profile_pic", $profile_pic,PDO::PARAM_STR) ;
   
          $stmt->execute();
          $uid=$db->lastInsertId();
          $db = null;
          $_SESSION['uid']=$uid;
          return true;

          }
          else
          {
          $db = null;
          return false;
          }
          
         
          } 
          catch(PDOException $e) {
          echo '{"error":{"text":'. $e->getMessage() .'}}'; 
          }
     }
     
     /* User Details */
     public function userDetails($uid)
     {
        try{
          $db = getDB();
          $stmt = $db->prepare("SELECT email FROM users WHERE uid=:uid");  
          $stmt->bindParam("uid", $uid,PDO::PARAM_INT);
          $stmt->execute();
          $data = $stmt->fetch(PDO::FETCH_OBJ);
          return $data;
         }
         catch(PDOException $e) {
          echo '{"error":{"text":'. $e->getMessage() .'}}'; 
          }

     }


}
?>

and for the registration page

<?php 
include("config.php");
include('class/userClass.php');
$userClass = new userClass();

$errorMsgReg='';
$errorMsgLogin='';
if (!empty($_POST['loginSubmit'])) 
{
$email=$_POST['email'];
$password=$_POST['password'];

 if(strlen(trim($email))>1 && strlen(trim($password))>1 )
   {
    $uid=$userClass->userLogin($email,$password);
    if($uid)
    {
        $url=BASE_URL.'home.php';
        header("Location: $url");
    }
    else
    {
        $errorMsgLogin="Please check login details.";
    }
   }
}

if (!empty($_POST['signupSubmit'])) 
{

 /**$username=$_POST['usernameReg'];**/
 
 $email=$_POST['emailReg'];
 $password=$_POST['passwordReg'];
    $fname=$_POST['fnameReg'];
    $lname=$_POST['lnameReg'];
    $dob=$_POST['dobReg'];
    $sex=$_POST['sexReg'];
    $country=$_POST['countryReg'];
    $state_resd=$_POST['state_resdReg'];
    $phone_no=$_POST['phone_noReg'];
    $profile_pic=$_POST['profile_picReg'];
 /**$username_check = preg_match('~^[A-Za-z0-9_]{3,20}$~i', $username);**/
 $email_check = preg_match('~^[a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.([a-zA-Z]{2,4})$~i', $email);
 $password_check = preg_match('~^[A-Za-z0-9!@#$%^&*()_]{6,20}$~i', $password);

 if($email_check && $password_check && strlen(trim($fname))>0) 
 {
    $uid=$userClass->userRegistration($email,$password,$fname,$lname,$dob,$sex,$country,$state_resd,$phone_no,$profile_pic);
    if($uid)
    {
     $url=BASE_URL.'home.php';
     header("Location: $url");
    }
    else
    {
      $errorMsgReg="Email already exits.";
    }
    
 }


}

?>
<!DOCTYPE html>
<html>
<head>
<style>
#container{width: 700px}
#login,#signup{width: 300px; border: 1px solid #d6d7da; padding: 0px 15px 15px 15px; border-radius: 5px;font-family: arial; line-height: 16px;color: #333333; font-size: 14px; background: #ffffff;rgba(200,200,200,0.7) 0 4px 10px -1px}
#login{float:left;}
#signup{float:right;}
h3{color:#365D98}
form label{font-weight: bold;}
form label, form input{display: block;margin-bottom: 5px;width: 90%}
form input{ border: solid 1px #666666;padding: 10px;border: solid 1px #BDC7D8; margin-bottom: 20px}
.button {
    background-color: #5fcf80 !important;
    border-color: #3ac162 !important;
    font-weight: bold;
    padding: 12px 15px;
    max-width: 300px;
    color: #ffffff;
}
.errorMsg{color: #cc0000;margin-bottom: 10px}
</style>
<body>
<div id="container">
<<div id="signup">
<h3>Registration</h3>
<form method="post" action="" name="signup">
<label>Email</label>
<input type="text" name="emailReg" autocomplete="off" />
<label>Password</label>
<input type="password" name="passwordReg" autocomplete="off"/>

<label>First Name</label>
<input type="text" name="fnameReg" autocomplete="off" />
<label>Last Name</label>
<input type="text" name="lnameReg" autocomplete="off" />

<label>Sex</label>
<input type="text" name="sexReg" autocomplete="off" />


<label>Date of Birth</label>
<input type="text" name="dobReg" autocomplete="off" />
<label>Country</label>
<input type="text" name="countryReg" autocomplete="off" />

<label>State of Residence</label>
<input type="text" name="state_resdReg" autocomplete="off" />

<label>Phone Number</label>
<input type="text" name="phone_noReg" autocomplete="off" />
<label>Profile photo</label>
<input type="text" name="profile_picReg" autocomplete="off" />
<!--<label>Username</label>
<input type="text" name="usernameReg" autocomplete="off" />-->


<div class="errorMsg"><?php echo $errorMsgReg; ?></div>
<input type="submit" class="button" name="signupSubmit" value="Create Counter">
<a href="index.php" class="to_register"> Login </a>
</form>
</div>




</div>

</body>
</html>

pls help me take a look at my codes to see what am doing wrong as am close to a novice

There's a lot that you doing wrong... on ur register function you are only binding one parameter but you have a lot of placeholders — Masivuye Cokile, Dec 12 '16 at 11:32
Read about the correct use of PDO here : https://phpdelusions.net/pdo — Masivuye Cokile, Dec 12 '16 at 11:35
Please, it takes 10 seconds to spot the error in question. Just count column names then parameters then `bind_params()` statements. They shoudl all be the same number and if not there is your Woops. — RiggsFolly, Dec 12 '16 at 11:41
Also check that there are Commas between all column names and parameters — RiggsFolly, Dec 12 '16 at 11:41

score 0 · Answer 1 · edited May 23 '17 at 12:33

The error you are getting is self explanatory, it means the number of parameters you are binding does not equal the number of column names you use in the first part of the Query Statement .

Your register function should look like this

/* User Registration */
     public function userRegistration($email,$password,$fname,$lname,$dob,$sex,$country,$state_resd,$phone_no,$profile_pic)
     {
          try{
          $db = getDB();
          $st = $db->prepare("SELECT uid FROM users WHERE email=:email LIMIT 1");  
          $st->bindParam("email", $email,PDO::PARAM_STR);
          $st->execute();
          $count=$st->rowCount();
          if($count<1)
          {
          $stmt = $db->prepare("INSERT INTO users(email,password,fname,lname,dob,sex,country,state_resd,phone_no,profile_pic) VALUES (:email,:hash_password,:fname,:lname,:dob,:sex,:country,:state_resd,:phone_no,:profile_pic)");

          $stmt->bindParam("email", $email,PDO::PARAM_STR) ;
         $hash_password= password_hash($password,PASSWORD_DEFAULT);
          $stmt->bindParam(":hash_password", $hash_password,PDO::PARAM_STR) ;
          $stmt->bindParam(":fname", $fname,PDO::PARAM_STR) ;
         $stmt->bindParam(":lname", $lname,PDO::PARAM_STR) ;
         $stmt->bindParam(":dob", $dob,PDO::PARAM_STR) ;
         $stmt->bindParam(":sex", $sex,PDO::PARAM_STR) ;
         $stmt->bindParam(":country", $country,PDO::PARAM_STR) ;
         $stmt->bindParam(":state_resd", $state_resd,PDO::PARAM_STR) ;
         $stmt->bindParam(":phone_no", $phone_no,PDO::PARAM_STR) ;
         $stmt->bindParam(":profile_pic", $profile_pic,PDO::PARAM_STR) ;

          $stmt->execute();
          $uid=$db->lastInsertId();
          $db = null;
          $_SESSION['uid']=$uid;
          return true;

          }
          else
          {
          $db = null;
          return false;
          }


          } 
          catch(PDOException $e) {
          echo '{"error":{"text":'. $e->getMessage() .'}}'; 
          }
     }

NB: I have changed from the sha256 you were using when you thought that you are hashing your password. I used password_hash(); and password_verify(); This SO question will give you more details as why I used that. Secure hash and salt for PHP passwords.

You might as well need to visit the site. https://phpdelusions.net/pdo to learn more about PDO proper use.

Alternative /usually easy way to insert using PDO:

 $stmt = $db->prepare("INSERT INTO users(email,password,fname,lname,dob,sex,country,state_resd,phone_no,profile_pic) VALUES (?,?,?,?,?,?,?,?,?,?)");
 $stmt->execute(array($email,$hash_password,$fname,$lname,$dob,$sex,$country,$state_resd,$phone_no,$profile_pic));

Your login function using password_verify(); should look like :

/* User Login */
     public function userLogin($email,$password)
     {

          $db = getDB();
          // $hash_password= hash('sha256', $password);
          $stmt = $db->prepare("SELECT uid,email,password FROM users WHERE email=:email");  
          $stmt->bindParam(":email", $email,PDO::PARAM_STR) ;
          $stmt->execute();

          $results= $stmt->fetchall(PDO::FETCH_ASSOC);

          if(count($results) > 0){

               foreach($results as $row){

                    if(password_verify($password,$row['password'])){

                       $_SESSION['uid']=$data->uid;
                         return true; 

                         //$password is the password from the user
                    }else{

                         //provided password does not match stored hash

                         return false;
                    }
               }

          }else{
               //No results

               return false;
          }


     }

Hope I didn't miss any curly bracket there :)

no you didnt miss any curly bracket. and i really appreciate your answer.. i used the corrected codes you sent but am getting this error message. Fatal error: Call to undefined function password_hash() in C:\wamp\www\appyam\class\userClass.php on line 57. pls help me look at it. just downloaded the password_compat as am using php5.3 — JerryCole, Dec 12 '16 at 17:14
Oh, the problem is with your php version you need to have 5.6 or above — Masivuye Cokile, Dec 13 '16 at 08:46
yes thank you very much... i figured so i upgraded to 5.7. thanks — JerryCole, Dec 14 '16 at 22:31
hello. pls could you help me take a look at a registration form.. its not submitting.. here is the link www.abc.allwie.com. and heres little of the .js file.. $(document).ready(function(){ $("#register-form").validate({ submitHandler : function(form) { //$('#submit_btn').attr('disabled','disabled'); //$('#submit_btn').attr('disabled','true'); //$('#submit_btn').button('loading'); form.submit(); }, pls help me check what am doing wrong.. and if you need more codes to show.. pls lemme know. space not enough for the rest code. but would send them in bits. thank you — JerryCole, Jan 28 '17 at 12:48
Please start a new question and paste your updated code @JerryCole — Masivuye Cokile, Jan 30 '17 at 08:56
Yes I already pasted a new question.. I would appreciate if you help take a look at it on my wall.. Its title login registration button not submitting.. Pls help! — JerryCole, Jan 31 '17 at 12:27

{"error":{"text":SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens}} <

1 Answers1