Re: PHP: query with parameter for SET

On 7/27/21 10:10 AM, Ray O'Donnell wrote:
> 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.

You probably want set_config():

https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-ADMIN-SET

9.26.1. Configuration Settings Functions

>
> Thanks,
>
> Ray.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com