Build PostgreSQL SELECT dynamically with different number of parameters

Question

I'd like to write my PostgreSQL (9.3) SELECT dynamically based on the user's fields filled out in the query form:

$cond = '';
if ($_REQUEST['sel_when_a'] != '')
    $cond .= ' AND field_lastmod >= $2';
if ($_REQUEST['sel_when_b'] != '')
    $cond .= ' AND field_lastmod <= $3';
$sel = ExecSQLP('SELECT field_value, field_lastmod
                FROM my_values
                WHERE field_value ILIKE \'%\' || $1 || \'%\'
                . $cond .
                ' ORDER BY node_id;'
    , array( $_REQUEST['sel_find'], $_REQUEST['sel_when_a'], $_REQUEST['sel_when_b'] ));

(ExecSQLP is my function to execute an SQL statement with the arguments, based on pg_query_params.)

This is simple and elegant, but I get an error message: Query failed: ERROR: bind message supplies 3 parameters, but prepared statement "" requires 1 if sel_when_a ("after") and sel_when_b ("before") are empty.

I think this shouldn't be an error in PostgreSQL as it doesn't do any bad if an unused parameter is supplied. Can I suppress this error? Othervise how to build the statement with different number of parameters? I have a lot of them, not only the 3 above. It'd be terrible to prepare for the several cases with renumbered parameters if PostgreSQL doesn't allow this.

The documentation: http://www.postgresql.org/docs/9.3/interactive/sql-expressions.html#SQL-EXPRESSIONS-PARAMETERS-POSITIONAL is not very useful for this case.

Thanks for your tips. SQL injection vulnerability is not accepted!

(Perhaps similar to How to handle dynamic number of parameters in querystring when building REST api?, but the relation is not always "equals to a string".)

Tometzky · Accepted Answer · 2014-12-11 15:02:35Z

0

I'd build the parameter array while building query. Like this:

$cond = '';
$params = array( $_REQUEST['sel_find'] );
if ($_REQUEST['sel_when_a'] != '') {
    $params[] = $_REQUEST['sel_when_a'];
    $cond .= ' AND field_lastmod >= $'.count($params);
}
if ($_REQUEST['sel_when_b'] != '') {
    $params[] = $_REQUEST['sel_when_b'];
    $cond .= ' AND field_lastmod <= $'.count($params);
}
$sel = ExecSQLP('SELECT field_value, field_lastmod
                FROM my_values
                WHERE field_value ILIKE \'%\' || $1 || \'%\''
                . $cond .
                ' ORDER BY node_id;'
    , $params ));

It's not very sensible to ignore wrong number or parameters - in 99% of cases it would be a programming error.

If you would've used PDO for database connection you could use named parameters like this:

$cond = '';
$params = array( ':sel_find' => $_REQUEST['sel_find'] );
if ($_REQUEST['sel_when_a'] != '') {
    $cond .= ' AND field_lastmod >= :sel_when_a';
    $params[':sel_when_a'] = $_REQUEST['sel_when_a'];
}
if ($_REQUEST['sel_when_b'] != '') {
    $cond .= ' AND field_lastmod <= :sel_when_b';
    $params[':sel_when_b'] = $_REQUEST['sel_when_b'];
}
$sel = $dbh->prepare('SELECT field_value, field_lastmod
                FROM my_values
                WHERE field_value ILIKE \'%\' || :sel_find || \'%\''
                . $cond .
                ' ORDER BY node_id;')->execute($params);

edited Dec 11, 2014 at 15:02

answered Dec 10, 2014 at 20:34

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lerin Sonberg Over a year ago

Thanks, but this is a bad solution. $3 will not exist if $_REQUEST['sel_when_a'] == '' but $_REQUEST['sel_when_b'] != '' Yes then also the numbers should be generated dynamically... I hoped there was a shorter and more elegant way.

Tometzky Over a year ago

Corrected. And proposed PDO solution with named parameters - clearer and more elegant.

Lerin Sonberg Over a year ago

Nice idea, but are you sure this is allowed in PostgreSQL? I get PostgreSQL error: ERROR: syntax error at or near ":". Finally I did exactly the same solution ($'.count($params);) as you. Thanks.

Tometzky Over a year ago

You probably use classic pg_connect and other pg_* functions, which do not support this. To use it you have to use more modern PDO class ($dbh = new PDO('pgsql:...')).

Collectives™ on Stack Overflow

Build PostgreSQL SELECT dynamically with different number of parameters

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related