Search code examples
phppdobindparam

PHP PDO: Invalid character value for cast specification

I am trying to execute a stored procedure with an output variable by passing information through from a form, however, I am receiving the following error when submitting the form.

1:integer
2:string
3:string
4:string
5:string
6:string
7:string
8:string
9:string
10:string
11:string
12:string
13:string
14:string
15:string
16:string
17:string
18:string
19:string
20:string
21:string
22:integer
PDOException: SQLSTATE[22018]: [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification in C:\xampp\htdocs\BusinessSolution\classes\signup.classes.php:33 Stack trace: #0 C:\xampp\htdocs\BusinessSolution\classes\signup.classes.php(33): PDOStatement->execute() #1 C:\xampp\htdocs\BusinessSolution\classes\classes.signupContr.php(73): Signup->setUser('WimpieN', 'Wimpie', 'Norman', 'wimpie.norman@g...', '9806225052084', 'asdffawdsd', '1998-06-22', '1', '0737878209', '1', 'asdawdasd', 'awdasdawd', 'asawf', 'asawdas', '197', '12123', '1124123', 'EBsphere', 'IT Department', 1) #2 C:\xampp\htdocs\BusinessSolution\includes\signup.inc.php(37): SignupContr->signupUser() #3 {main}

All I require is a fresh set of eyes for if you can identify this in the below code.

I do apologize for the messy code as I have been changing a lot trying to resolve this but I am just not seeing it right now.

signupinc.php

if(isset($_POST["signup-submit"]))
{
  // Grabbing the data
  $uid = $_POST["signup-username"];
  $firstName = $_POST["signup-firstName"];
  $lastName = $_POST["signup-lastName"];
  $email = $_POST["signup-email"];
  $idNumber = $_POST["signup-id"];
  $passport = $_POST["signup-passport"];
  $DOB = $_POST["signup-dob"];
  $gender = $_POST["signup-gender"];
  $contact = $_POST["signup-contact"];
  $contactType = $_POST["signup-ContactType"];
  $addressline1 = $_POST["signup-address1"];
  $addressline2 = $_POST["signup-address2"];
  $city = $_POST["signup-city"];
  $province = $_POST["signup-province"];
  $country = $_POST["signup-country"];
  $postalCode = $_POST["signup-postalCode"];
  $extension = $_POST["signup-extension"];
  $company = $_POST["signup-company"];
  $division = $_POST["signup-division"];
  $userID = 1;

  // Instantite SignupContr class
  include "../classes/dbh.classes.php";
  include "../classes/signup.classes.php";
  include "../classes/classes.signupContr.php";


  $signup = new SignupContr($uid,$firstName,$lastName,$email,$idNumber,$passport,$DOB,$gender,$contact,$contactType,$addressline1,$addressline2,$city,$province,$country,$postalCode,$extension,$company,$division,$userID);

  // Running error hadnlers and user signupUser
  try {
     $signup->signupUser();
   }
   catch (PDOException $e) {
     $e->getMessage();
     echo $e;
  }

  // Head back to index.php
  header("location: ../index.php?error=none");
}

?>

singup.classes.php

class Signup extends Dbh {
    protected function setUser($uid,$firstName,$lastName,$email,$idNumber,$passport,$DOB,$gender,$contact,$contactType,$addressline1,$addressline2,$city,$province,$country,$postalCode,$extension,$company,$division, $userID) {
      $proc = '{CALL sp_SYS_AddUser (:token, :uid, :fName, :lName, :email, :id, :pass, :dob, :gender, :contact, :contactType, :address1, :address2, :city, :province, :country:, :postalCode, :extension, :company, :division, :userID, :retval) }';
      $stmt = $this->connect()->prepare($proc);

      $token = $this->generateRandomString(8) . " - " . $this->generateRandomString(5) . " - " . $this->generateRandomString(5);
      $stmt->bindParam(':retval',$ReturnValue ,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,4);
      $stmt->bindParam(':token', $token, PDO::PARAM_STR);
      $stmt->bindParam(':uid', $uid, PDO::PARAM_STR);
      $stmt->bindParam(':fName', $firstName, PDO::PARAM_STR);
      $stmt->bindParam(':lName', $lastName, PDO::PARAM_STR);
      $stmt->bindParam(':email', $email, PDO::PARAM_STR);
      $stmt->bindParam(':id', $idNumber, PDO::PARAM_STR);
      $stmt->bindParam(':pass', $passport, PDO::PARAM_STR);
      $stmt->bindParam(':dob', $DOB);
      $stmt->bindParam(':gender', $gender, PDO::PARAM_STR);
      $stmt->bindParam(':contact', $contact, PDO::PARAM_STR);
      $stmt->bindParam(':contactType', $contactType, PDO::PARAM_STR);
      $stmt->bindParam(':address1', $addressline1, PDO::PARAM_STR);
      $stmt->bindParam(':address2', $addressline2, PDO::PARAM_STR);
      $stmt->bindParam(':city', $city, PDO::PARAM_STR);
      $stmt->bindParam(':province', $province, PDO::PARAM_STR);
      $stmt->bindParam(':country', $country, PDO::PARAM_STR);
      $stmt->bindParam(':postalCode', $postalCode, PDO::PARAM_STR);
      $stmt->bindParam(':extension', $extension, PDO::PARAM_STR);
      $stmt->bindParam(':company', $company, PDO::PARAM_STR);
      $stmt->bindParam(':division', $division, PDO::PARAM_STR);
      $stmt->bindParam(':userID', $userID, PDO::PARAM_INT);

      try {
         $stmt->execute();
       }
       catch (PDOException $e) {
         $e->getMessage();
         $location = 'file->signup.classes.php | function->setUser()';
         $line = '33';
         $stmt = null;

         echo "1:".gettype($ReturnValue)."</br>";
         echo "2:".gettype($token)."</br>";
         echo "3:".gettype($uid)."</br>";
         echo "4:".gettype($firstName)."</br>";
         echo "5:".gettype($lastName)."</br>";
         echo "6:".gettype($email)."</br>";
         echo "7:".gettype($idNumber)."</br>";
         echo "8:".gettype($passport)."</br>";
         echo "9:".gettype($DOB)."</br>";
         echo "10:".gettype($gender)."</br>";
         echo "11:".gettype($contact)."</br>";
         echo "12:".gettype($contactType)."</br>";
         echo "13:".gettype($addressline1)."</br>";
         echo "14:".gettype($addressline2)."</br>";
         echo "15:".gettype($city)."</br>";
         echo "16:".gettype($province)."</br>";
         echo "17:".gettype($country)."</br>";
         echo "18:".gettype($postalCode)."</br>";
         echo "19:".gettype($extension)."</br>";
         echo "20:".gettype($company)."</br>";
         echo "21:".gettype($division)."</br>";
         echo "22:".gettype($userID)."</br>";

         $proc = '{CALL sp_SystemErrorGenerator (:token, :message, :location, :line, :userID, :retval) }';
         $stmt = $this->connect()->prepare($proc);

         $stmt->bindParam(':retval',$ReturnValue ,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,4);
         $stmt->bindParam(':token', $token, PDO::PARAM_STR);
         $stmt->bindParam(':message', $e, PDO::PARAM_STR);
         $stmt->bindParam(':location', $location, PDO::PARAM_STR);
         $stmt->bindParam(':line', $line, PDO::PARAM_STR);
         $stmt->bindParam(':userID', $userID, PDO::PARAM_INT);

         try {
            $stmt->execute();
            echo $e;
            exit();
            //header("location: ../admin.user.add.php?error=stmtfailed?id=".$ReturnValue);
          }
          catch (PDOException $e) {
            echo 'Execute #2 failed: ' . $e->getMessage();
            die();
          }
       }

      if(!$stmt->execute()) {
        $stmt = null;
        header("location: ../index.php?error=stmtfailed?id=".$ReturnValue);
        exit();
      }

      $stmt = null;
    }

class.signupContr.php

class SignupContr extends Signup {

  private $uid;
  private $firstName;
  private $lastName;
  private $email;
  private $idNumber;
  private $passport;
  private $DOB;
  private $gender;
  private $contact;
  private $contactType;
  private $addressline1;
  private $addressline2;
  private $city;
  private $province;
  private $country;
  private $postalCode;
  private $extension;
  private $company;
  private $division;
  private $userID;

  public function __construct($uid,$firstName,$lastName,$email,$idNumber,$passport,$DOB,$gender,$contact,$contactType,$addressline1,$addressline2,$city,$province,$country,$postalCode,$extension,$company,$division,$userID) {
    $this->uid = $uid;
    $this->firstName = $firstName;
    $this->lastName = $lastName;
    $this->email = $email;
    $this->idNumber = $idNumber;
    $this->passport = $passport;
    $this->DOB = $DOB;
    $this->gender = $gender;
    $this->contact = $contact;
    $this->contactType = $contactType;
    $this->addressline1 = $addressline1;
    $this->addressline2 = $addressline2;
    $this->city = $city;
    $this->province = $province;
    $this->country = $country;
    $this->postalCode = $postalCode;
    $this->extension = $extension;
    $this->company = $company;
    $this->division = $division;
    $this->userID = $userID;
  }
  public function signupUser() {
    if($this->invalidUid() == false) {
      // echo "Invalid Username!";
      header("location: ../index.php?error=invaliduid");
      exit();
    }

    if($this->invalidEmail() == false) {
      // echo "Invalid Email!";
      header("location: ../index.php?error=invalidemail");
      exit();
    }

    if($this->invalidText() == false) {
      // echo "Invalid Text!";
      header("location: ../index.php?error=invalidtext");
      exit();
    }

    if($this->validateUser() == false) {
      // echo "User Exists";
      header("location: ../index.php?error=userexists");
      exit();
    }

    $this->setUser($this->uid,$this->firstName,$this->lastName,$this->email,$this->idNumber,$this->passport,$this->DOB,$this->gender,$this->contact,$this->contactType,$this->addressline1,$this->addressline2,$this->city,$this->province,$this->country,$this->postalCode,$this->extension,$this->company,$this->division,$this->userID);
  }

SQL SP: sp_SYS_AddUser

CREATE PROCEDURE sp_SYS_AddUser
(
    @token NVARCHAR(MAX),
    @username VARCHAR(255),
    @firstName VARCHAR(255),
    @lastName VARCHAR(255),
    @email VARCHAR(255),
    @idNumber VARCHAR(255),
    @passport VARCHAR(255), 
    @DOB DATE,
    @gender VARCHAR(10),
    @contact VARCHAR(255),
    @contactType VARCHAR(255),
    @addressLine1 VARCHAR(255),
    @addressline2 VARCHAR(255),
    @city VARCHAR(255),
    @province VARCHAR(255),
    @country VARCHAR(255),
    @postalCode VARCHAR(255),
    @extension VARCHAR(255),
    @company VARCHAR(255),
    @division VARCHAR(255),
    @userID INT,
    @status INT OUTPUT
)
Solution

  • I found the issue and you might laugh at this one hey. When putting your place holers I had an extra ":" value for example the one place holder was ":country:"