Re: PHP: query with parameter for SET

Hi

út 27. 7. 2021 v 19:10 odesílatel Ray O'Donnell <ray(at)rodonnell(dot)ie> napsal:

> Hi everyone,
>
> Using PHP (or indeed maybe more generally), is it possible to issue a
> SET TIME ZONE statement with a parameter for the new runtime setting?
>
> In a PHP application I'm issuing a SET TIME ZONE command just after the
> database connection is created, and for security I'd like to pass the
> time zone value in a parameter, as it's coming from a configuration file
> - something like this:
>
> $pdo = new PDO(/* DSN goes here */);
>
> $statement = $pdo->prepare('set time zone :tz');
> $statement->execute([':tz' => 'Europe/Dublin']);
>
> However, in the logs I see:
>
> ERROR: syntax error at or near "to" at character 15
> STATEMENT: set time zone $1
>
> Is this possible? I've also tried the "set timezone ..." variant with
> similar results. I'm wondering too if the limitation (if such it is) is
> in PHP or whether it's a Postgres thing.
>

When you use client side prepared statements, then it can be possible. But
I don't know how it works in PHP.

Server side prepared statements are not possible in this case. "set"
statement has not execution plan. But you You can use Adrian's proposal,
and you can prepare the query 'select set_config($1, false)'

Regards

Pavel

> Thanks,
>
> Ray.
>
> --
> Raymond O'Donnell // Galway // Ireland
> ray(at)rodonnell(dot)ie
>
>
>