Use parameters for field and table names in a MySQL Prepare statement to create truly dynamic SQL

The mysql prepare statement sucks heavy time. You can not (!?!!!) use it to template field and table names. So it is mostly useless for any heavy duty stored procedures that want to address different columns.

Here is a small function that will take care of that :)

DELIMITER //
DROP FUNCTION  IF EXISTS substrCount//
CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, '')))/length(delim);//

DROP FUNCTION  IF EXISTS charsplit//
CREATE FUNCTION charsplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');//

DROP FUNCTION  IF EXISTS replacefirst//
CREATE FUNCTION replacefirst(haystack varchar(255), needle varchar(255),replacestr varchar(255)) returns varchar(255)
BEGIN
    IF LOCATE(needle,haystack)>0 THEN
        SET @replaced=concat(left(haystack,LOCATE(needle,haystack)-LENGTH(needle)),replacestr,right(haystack,LENGTH(haystack)-LOCATE(needle,haystack)));
    ELSE
        SET @replaced=haystack;
    END IF;
    RETURN @replaced;
END;//

DROP FUNCTION  IF EXISTS properprepare//
CREATE FUNCTION properprepare(template varchar(255), args varchar(255)) returns varchar(255)
BEGIN
 SET @i=0;
 SET @numargs = substrCount(args,',');
  WHILE @i <= @numargs DO
      SET @i= @i+ 1;
      SET template=replacefirst(template,'?',charsplit(args,',',@i));
  END WHILE;
RETURN template;
END;//

DELIMITER ;

Test with

SELECT properprepare('SELECT ? FROM ? ', '*,user');
SELECT properprepare('SELECT ? FROM ? ??', '*,user');
SELECT properprepare('SELECT ? FROM ?', '*,user,test,test1');
SELECT properprepare('SELECT ? FROM ? WHERE ?=?', '*,user,user_id,25');

-- So now you can in your stored procedures (at last!) do:

PREPARE update_query FROM properprepare('SELECT ? FROM ?', '*,mytable');
EXECUTE update_query;
PREPARE update_query FROM properprepare('SELECT ? FROM ?', 'myfield,mytable');
EXECUTE update_query;

The first argument is the template with the question mark '?' as placeholder, the second is a comma separated string with the variables to replace. If you pass too few variables, the remaining placeholders will not be modified so they can be treated with the mysql PREPARE FROM, EXECUTE USING afterwards…

SET @a=25;
SET @b=properprepare('SELECT ? FROM ? WHERE ?=?', '*,user,id_user');
PREPARE stmnt FROM  @b;
EXECUTE stmnt USING @a;

CAVEAT: Hardly tested, do not use in production without really testing this one