Using parameter values for schema and table in Postgresql

Question

I'm trying to use a query where the schema and table are passed as parameters into a prepared statement using pg_query_params like this:

$searchSchema = 'mySchema';
$searchTable = 'myTable';
$searchColumn = 'searchColumn';
$searchValue = 'some value';

$selQuery = "SELECT *

FROM $1.$2  --fails here

WHERE someColumn like $3;";

$rs = pg_query_params($db, $selQuery , array($searchSchema, $searchTable, $searchColumn, $searchValue));

The issue is with the schema and table which need to be set dynamically - as in the code above.

@Laurenz Albe please could you remove the 'marked as duplicate' please? — Robert
– Robert, Commented Nov 6, 2017 at 15:39
In essence, you have to compose the query as a string. You cannot use parameters for table or column names. — Laurenz Albe
– Laurenz Albe, Commented Nov 6, 2017 at 16:02
In essence it is the same thing. You can neither use table names nor column names as parameters to a prepared statement, neither with pg_query_params or otherwise. PostgreSQL does not allow it, there is nothing PHP can do about it. — Laurenz Albe
– Laurenz Albe, Commented Nov 7, 2017 at 19:40
Ok, ok. I thought you would be satisfied with being pointed towards the solution, but I'm happy to write up an answer if that is better for you. — Laurenz Albe
– Laurenz Albe, Commented Nov 8, 2017 at 8:31

Laurenz Albe · Accepted Answer · 2017-11-08 08:30:03Z

3

In a parameterized SQL statement (which is a prepared statement in PostgreSQL), parameters can only stand for constant values, not for table or column names.

This limitation is enforced by PostgreSQL, and there is no way around it, no matter what programming language or driver you use. This is also intentional and not a bug.

You will have to compose a string that contains the complete SQL statement with table and column names substituted and execute that. Beware of SQL injection – use functions like pg_escape_identifier to escape names.

answered Nov 8, 2017 at 8:30

Laurenz Albe

256k22 gold badges310 silver badges386 bronze badges

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

Collectives™ on Stack Overflow

Using parameter values for schema and table in Postgresql

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related