Hi,

I am currently using PDO with MySQL. From what I've read using stored procedures with PDO helps to limit SQL injection since parameters get bound by reference. As an example I use the following function with a PDO wrapper:

Code:
public static function UserLogin($email, $password)
{
      $sql = 'CALL user_login(:email, :password)';

      // Build the parameters array
      $params = array (':email' => $email, ':password' => $password);

      // Execute the query and return the results
      return = DatabaseHandler::GetRow($sql, $params);
}
And the user_login stored procedure which it calls as follows:

Code:
CREATE PROCEDURE user_login(IN inEmail VARCHAR(100), IN inPassword VARCHAR(30))
BEGIN

	SELECT user_id, user_pwd, user_fname, user_lname
	 FROM tbl_users WHERE user_email = inEmail AND user_pwd = inPassword;

END
The above works fine in if you try to inject something ' or 'a'='a in to both the email and password fields the query will fail.

However I have an instances where I need to build a dynamic query and am having to use prepared statments as follows in my stored procedure - note the below is just an example to illustrate the problem, I am not actually using it for login purposes:

Code:
CREATE PROCEDURE user_login(IN inEmail VARCHAR(100), IN inPassword VARCHAR(30))
BEGIN

   SET @statement = 'SELECT user_id, user_pwd, user_fname, user_lname
	 FROM tbl_users WHERE';

   IF (inEmail <> "") THEN						
       SET @statement = CONCAT(@statement, ' (user_email = ''', inEmail,''') ');	
    END IF;

   IF (inPassword <> "") THEN						
       SET @statement = CONCAT(@statement, 'AND (user_pwd = ''', inPassword,''') ');	
    END IF;

    
    PREPARE dynquery FROM @statement;
    
    EXECUTE dynquery;
    
    DEALLOCATE PREPARE dynquery; 		

END$$
If I inject something like ' or 'a'='a in to both the email and password fields the query will return true.

I am unable to escape these parameters using mysqli_real_escape_string as am using PDO.

Any suggestion on how I can sanitize the data to prevent SQL injections whne using prepared statements as above with PDO?

Regards