CODEWITHSUNDEEP
mysqlparametersprepared-statementdynamic-sql
Erin Schoonover
Erin Schoonover

Reputation: 539

Prepared statement execution with variable number of parameters to be bound

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:

However, I was wondering if anyone else has ran into this desire to handle dynamic construction of an EXECUTE statement purely with SQL.

Upvotes: 1

Views: 2406

Answers (2)

bloodyKnuckles
bloodyKnuckles

Reputation: 12089

However, I was wondering if anyone else has ran into this desire to handle dynamic construction of an EXECUTE statement purely with SQL.

Yes, me too.

Here's a PHP solution to generate the list of question marks for a prepared statement based on an array of unknown length:

/* My target query is this:
   SELECT fun FROM fun_stuff WHERE fun_key IN ( ...unknown number of values... )
*/

/* For this example let's set our array to this: */
$val_arr = array(1,2,3,4,5,6,7,8,9);
$val_arr_cnt = count($val_arr); /* and count it */

/* Now make prepared statement q-mark string from values array */
$sql_prep = str_pad('?', ($val_arr_cnt * 2) - 1, ',?', STR_PAD_RIGHT);

/* add it to query */
$sql = "SELECT fun FROM fun_stuff WHERE fun_key IN ($sql_prep)";

/* And the result:
   SELECT fun FROM fun_stuff WHERE fun_key IN (?,?,?,?,?,?,?,?,?)
 */

I've no idea how efficient this is. But I too every now and then want to implement the security and efficiency of MySQL prepared statements but have variable length input arrays

Upvotes: 1

Bradley D
Bradley D

Reputation: 2835

Not sure if it's possible to dynamically build the parameter list (changing the number of parameters on the fly, etc...). But since you can dynamically build your where clause, one very simple workaround is do something like this. Assuming your validations permit it, the else clause basically has the same effect as ignoring the parameter you may or may not be filtering on.

if p_cust_id is not null && p_cust_id > 0 then
    set v_where_clause = concat(v_where_clause, ' c.cust_id = ? ');
    set @v_cust_id := p_cust_id;
else
    set v_where_clause = concat(v_where_clause, ' c.cust_id > ? ');
    set @v_cust_id := 0;
end if;

then plug all the user variables above into your execute statement

execute str1 using @v_cust_id, @v_etc....;*

Upvotes: 0

Related Questions