Prepared Statement get wrong result in MYSQL-CodePudding

I have a table with design

CREATE TABLE IF NOT EXISTS InsuranceContract (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`enquiryCode` VARCHAR(20) DEFAULT NULL,
`contractCode` VARCHAR(20) DEFAULT NULL,
`createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP (),
`updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP () ON UPDATE CURRENT_TIMESTAMP (),
UNIQUE KEY (`enquiryCode`)) ENGINE=INNODB DEFAULT CHARSET=UTF8 COLLATE = UTF8_BIN;

Then I was created a procedure like this

DROP procedure IF EXISTS `sp_insurance_contract_get`;
DELIMITER $$ 
CREATE PROCEDURE `sp_insurance_contract_get` (enquiryCode VARCHAR(20), contractCode VARCHAR(20)) 
BEGIN
    SET @t1 = "SELECT * FROM InsuranceContract 
               WHERE InsuranceContract.enquiryCode = enquiryCode 
                     AND InsuranceContract.contractCode = contractCode;";
    PREPARE param_stmt FROM @t1;
  EXECUTE param_stmt;
  DEALLOCATE PREPARE param_stmt;
END$$ 
DELIMITER ;

And I was executed this procedure in MySQL Workbench by this command:

CALL sp_insurance_contract_get('EQ000000000014', '3001002');

I expected I will receive 1 row result but it selected all records in this table. If I copy and create exactly this @t1 into plain SQL not using statement, it's correct.

Please help me to fix this error. I'm using MySQL 8.0.19

CodePudding user response：

You can use placehoders on prepare statements, this is why we use them to prevent sql injection

One other thing never use column names as variables names, databases can not differentiate

DROP procedure IF EXISTS `sp_insurance_contract_get`;
DELIMITER $$ 
CREATE PROCEDURE `sp_insurance_contract_get` (enquiryCode_ VARCHAR(20), contractCode_ VARCHAR(20)) 
BEGIN
    SET @t1 = "SELECT * FROM InsuranceContract 
               WHERE enquiryCode = ? 
                     AND contractCode = ?;";
  PREPARE param_stmt FROM @t1;
  SET @a = enquiryCode_;
 SET @b = contractCode_;
 

  EXECUTE param_stmt USING @a, @b;
  DEALLOCATE PREPARE param_stmt;
END$$ 
DELIMITER ;

CodePudding user response：

When you say

WHERE enquiryCode = enquiryCode

you compare that named column to itself. The result is true always (unless the column value is NULL).

Change the names of your SP's parameters, so you can say something like

WHERE enquiryCode_param = enquiryCode

and things should work.

Notice that you have no need of a MySql "prepared statement" here. In the MySql / MariaDb world prepared statements are used for dynamic SQL. That's for constructing statements within the server from text strings. You don't need to do that here.