Re: Array data type in mysql

[Gary Malcolm]

Don't just pass string into stored procedures and then just enter them as concatenation to a query... for the sake of security.

I wrote this procedure that returns a list of integers from a string using a temp table as a liason. You will avoid a lot of sql injection this way :-)

DELIMITER $$

DROP PROCEDURE IF EXISTS `YourDB`.`sp_parseIntList` $$
CREATE DEFINER=`hotstuff`@`%` PROCEDURE `sp_parseIntList`(
_intlist TEXT )
BEGIN

DECLARE comma INT DEFAULT 0;
DECLARE mylist TEXT DEFAULT _intlist;
DECLARE temp TEXT DEFAULT '';
DECLARE strlen int DEFAULT LENGTH(_intlist);


CREATE TEMPORARY TABLE TempTable (num int) TYPE=INNODB;


SET comma = LOCATE(',',mylist);

WHILE strlen > 0 DO
IF
comma = 0
THEN
SET temp = TRIM(mylist);
SET mylist = '';
SET strlen = 0;
ELSE
SET temp = TRIM(SUBSTRING(mylist,1,comma));
SET mylist = TRIM(SUBSTRING(mylist FROM comma+1));
SET strlen = LENGTH(mylist);
END IF;

IF CAST(temp as UNSIGNED) != 0
THEN
INSERT INTO TempTable VALUES(CAST(temp as UNSIGNED));
END IF;
SET comma = LOCATE(',',mylist);
END WHILE;

SELECT * FROM TempTable;

DROP TEMPORARY TABLE IF EXISTS TempTable;



END $$

DELIMITER ;