mysql query help (like statement)

Question

I am using mysql which i have a stored procedure which has an input variable.

I want to use this variable in a select statement (with like clause).

Eg:

DELIMITER $$

DROP PROCEDURE IF EXISTS `DeleteDataByTransactionID` $$
CREATE DEFINER=`root`@`%` PROCEDURE `DeleteDataByTransactionID`(in **$TransactionID** varchar(50))

BEGIN

delete from sqlstatements where tempsql like '%'+ **$TransactionID** + '%';

END $$


DELIMITER ;

Thanks

Answer 1

DELIMITER $$
DROP PROCEDURE IF EXISTS `DeleteDataByTransactionID`
$$
CREATE DEFINER=`root`@`%` PROCEDURE `DeleteDataByTransactionID`(TransactionID VARCHAR(50))
BEGIN
        DELETE
        FROM    sqlstatements
        WHERE   tempsql LIKE CONCAT('%', TransactionID, '%');
END
$$
DELIMITER ;

Answer 2

Actually, the accepted answer's version is open to SQL injection if the caller does not properly parameterize the call to the stored procedure. I would recommend utilizing a prepared statement in the stored procedure as follows to be safe instead of relying on the caller...

DELIMITER $$ CREATE PROCEDURE DeleteDataByTransactionID ( TransactionID VARCHAR(50) ) BEGIN

SET @sql = "DELETE FROM sqlstatements WHERE
tempsql LIKE CONCAT('%', ?, '%')";

SET @transid = TransactionID;

PREPARE stmt FROM @sql; EXECUTE stmt USING @transid; DEALLOCATE PREPARE stmt;

END $$