如何在pg_prepare（）pg_execute（）中使用参数化ORDER BY？

Question

I want to order this query by something decided by the user so I've made it parameterized. I really can't understand why the second parameter is being ignored! (It doesn't order the results)

function getY($id, $order){
    ....
    ....
    $db = connection_pgsql() or die ('connection failed');
    $sql = "SELECT id, y FROM test WHERE id = $1 ORDER BY $2";
    $resource = pg_prepare($db, "get_y", $sql);

    $value = array($id, $order);
    $resource = pg_execute($db, "get_y", $value);
    ....
    ....
}

If I pass it like this:

$sql = "SELECT id, y FROM test WHERE id = $1 ORDER BY {$order}";

it works but I think it's not safe (isn't it?).

I've only found this pgsql 42601 error with PDO::execute which really doesn't solve my problem.

Answer 1

ORDER BY $2 is sorting a fix value, something like this:

SELECT * FROM t1 ORDER BY 'some string';

As all records will be sorted by the same string, there is no sorting....

ORDER BY is also something that can't be prepared because you don't tell the database during preparation what column you want to use for the sorting. It's like planning a road trip but without knowing where to go.

To fix this, you need dynamic SQL and some other security measures:

function getY($id, $order){
    ....
    ....
    $db = connection_pgsql() or die ('connection failed');

    $sql = "SELECT quote_ident($1);"; // give me a secure object name

    $resource = pg_query_params($db, $sql, array($order)); // error handling is missing

    $order = pg_fetch_result($resource, 0, 0);

    $sql = "SELECT id, y FROM test WHERE id = $1 
        ORDER BY ".$order.";"; // <===== it's now safe to use

    $resource = pg_prepare($db, "get_y", $sql); // error handling is missing

    $value = array($id);
    $resource = pg_execute($db, "get_y", $value); // error handling is missing
    ....
    ....
}

You now create a complete string of SQL that can be prepared and is save because of quote_ident(). Whatever content there will be in $order, it will be treated as an identifier in PostgreSQL. Like a column in this case. If that column is missing, the prepare will fail. That's why you need proper error handling, you know that one day this query will fail because of bad input.

If you're using this statement just once, you could also use pg_query_params() instead of pg_prepare() + pg_execute().