PDO Exception : Tried to bind parameter number 65536. SQL Server supports a maximum of 2100 parameters

Question

I want to read user data. But the result showing like

Tried to bind parameter number 65536. SQL Server supports a maximum of 2100 parameters.

and here is my code of login.php (test with hard code first)

<?php

header("Content-type: application/json");
include_once 'Database.php';
include_once 'master.php';


//$username = $_GET['username'];
//$password = $_GET['password'];


$username = "angela123";
$password = "admin123";

// get database connection
$database = new Database();
$db = $database->getConnection();
$login = new Master($db);
$stmt = $login->Login($username, $password);
?>

and here is function of Login with parameter username and password

public function Login($username,$password)
    {
        // select all query
        try {
            $sqlsrvquery = ("
               EXEC [dbo].[GetAllAdmin2] 
                   @username = ':username', 
                   @password = ':password',
            ");
            // prepare query statement
            $stmt = $this->conn->prepare($sqlsrvquery);
            $stmt->bindParam(':username', $username, PDO::PARAM_STR);
            $stmt->bindParam(':password', $password, PDO::PARAM_STR);
            $stmt->execute();
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $admin_arr = array(
                    "username" => $row['username'],
                    "password" => $row['password'],

                );

            }

                if ($row = 0) {
                $admin_arr = array(
                    "status" => false,
                    "message" => "Invalid Get Data Admin!",
                );
            }


        } catch (Exception $e) {
            print_r($e->getMessage());
        }
        print_r(json_encode($admin_arr));
    }

What's going on in this code? actually the result is working properly on SQL Server with SP Here is the Login SP

ALTER Procedure [dbo].[GetAllAdmin2]
(
@username varchar(55),
@password varchar(55)
)
as
begin
SELECT username, password
    FROM Admin
    WHERE username = @username and password = @password
    and status = 'Active';
END

When execute the SP, the output should be showing username and password

username     password
angela123    admin123

And here is database.php

<?php
class Database
{

    // specify your own database credentials
    private $host = "DESKTOP-N550JK\SQLEXPRESS";
    private $user = "sa";
    private $database = "Library";
    private $password = "sqlserver123";
    public $conn;

    // get the database connection
    public function getConnection(){

        try {

            $this->conn = new PDO("sqlsrv:Server=" .$this->host . ";database=" . $this->database, $this->user, $this->password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        } catch (PDOException $exception) {
            echo "Connection error: " . $exception->getMessage();
            die("Database Connection Error");
        }
        return $this->conn;
    }
}
?>

any solution of this? thanks

Answer 1

You are using parameter binding in a wrong way and you need to remove the quotes around the placeholders (:username and :password). As is explained in the documetation, the statement template can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed.

<?php
...

// Statement
$sqlsrvquery = "
   EXEC [dbo].[GetAllAdmin2] 
      @username = :username, 
      @password = :password
";
$stmt = $this->conn->prepare($sqlsrvquery);
// Parameter bindings
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':password', $password, PDO::PARAM_STR);
// Statement execution
$stmt->execute();

...
?>

An additional example, using the ? parameter marker:

<?php
...

// Statement
$sqlsrvquery = "
   EXEC [dbo].[GetAllAdmin2] 
      @username = ?, 
      @password = ?
";
$stmt = $this->conn->prepare($sqlsrvquery);
// Parameter bindings
$stmt->bindParam(1, $username, PDO::PARAM_STR);
$stmt->bindParam(2, $password, PDO::PARAM_STR);
// Statement execution
$stmt->execute();

...
?>