How to execute custom delimiter query with mysqli multiquery

Question

I have the following sql script and I want to execute it with multi-query

DELIMITER $$
DROP FUNCTION IF EXISTS `getAttendanceHistoryDates`$$

CREATE FUNCTION getAttendanceHistoryDates(processDate date)
  RETURNS TEXT
  DETERMINISTIC
  LANGUAGE SQL
BEGIN

  DECLARE minDate date;
  DECLARE startYear int;
  DECLARE endYear int;
  DECLARE dateString TEXT;

  SET minDate = (SELECT MIN(date) FROM `ohrm_attendance_report`);
  SET startYear = YEAR(minDate);
  SET endYear = YEAR(processDate);

  SET  dateString = processDate;
  WHILE startYear  < endYear DO
     SET  dateString = CONCAT(dateString,'|',CONCAT(startYear, '-12-31'));
     SET  startYear = startYear + 1; 
  END WHILE;

  RETURN dateString;
END;
$$
DELIMITER ;

Is there a way to do this? Will it work if I just remove DELIMITER $$ and DELIMITER ; from the script and replace $$ by ; and execute with multi-query?

score 1 · Accepted Answer · answered Apr 11 '14 at 06:23

1

No, it is not possible through the MySQL API. The semicolon separator is not configurable. It's really determined on the MySQL server side. For more details, see http://dev.mysql.com/doc/refman/5.6/en/c-api-multiple-queries.html

The mysql command-line interface supports DELIMITER by pre-parsing the input and separating statements by the delimiter. Then it executes each statement individually.

There is no reason you need to use multi-query. You should run the DROP FUNCTION and the CREATE FUNCTION as individual queries.

Using multi-query is a bad idea in general, because it creates an opportunity for bad SQL injection problems.

answered Apr 11 '14 at 06:23

Bill Karwin

538,548
86
673
828

Worst thing to tell is that mysqli library can itself cause a way for mysql-injections. SQL Injection should be avoidable without prepared statements and mysqli_multi should not be blamed for injections. – Fakhar Anwar Oct 04 '19 at 17:06
@FakharAnwar, I don't like to disappoint you, but query parameters are the best solution for avoiding SQL injection, and query parameters require the use of prepare() & execute(). As for mysqli_multi(), I stand by my statement in this answer that there is no benefit to using it, and only a small risk of enabling a type of SQL injection that is not possible if you use the normal API. – Bill Karwin Oct 04 '19 at 20:19

How to execute custom delimiter query with mysqli multiquery

1 Answers1

Related