Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter binding for the values that are to be part of the interpolated criteria.
Since the criteria added to the prepared statement and thus the number of parameters to be bound may differ depending on the user input, I devised the method below to determine which variables will be passed to the EXECUTE statement. This works, but it seems inelegant.
CREATE PROCEDURE foo (IN mandatory INT, IN optional INT, IN optional2 VARCHAR(20)) BEGIN SELECT 0, '', '', mandatory, optional, optional2 INTO @params, @sql, @where, @m, @o1, @o2; IF (@o1 > '' AND @o1 IS NOT NULL) THEN SET @where = CONCAT(@where, ' AND field = ?'); SET @params = @params + 1; END IF; IF (@o2 > '' AND @o2 IS NOT NULL) THEN SET @where = CONCAT(@where, ' AND field2 = ?'); SET @params = @params + 3; END IF; SET @sql = CONCAT(' SELECT id, bar FROM table WHERE baz = ? ', @where ); PREPARE STMT FROM @sql; CASE @params WHEN 0 THEN EXECUTE STMT USING @m; WHEN 1 THEN EXECUTE STMT USING @m, @o1; WHEN 3 THEN EXECUTE STMT USING @m, @o2; WHEN 4 THEN EXECUTE STMT USING @m, @o1, @o2; END CASE; DEALLOCATE PREPARE STMT; END$$
I'm aware of alternatives:
- The binaries that would call these stored procedures have a function that attempts to identify potential SQL injection by passing the user supplied strings through a regular expression.
- A user-defined function could be used to dynamically construct the EXECUTE statement given a dynamic number of inputs.
However, I was wondering if anyone else has ran into this desire to handle dynamic construction of an EXECUTE statement purely with SQL.