Обсуждение: PHP: query with parameter for SET

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.

Thanks,

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

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.klaver@aklaver.com

On 27/07/2021 18:20, Pavel Stehule wrote:
> 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)'

Thanks Pavel - that makes sense.

Ray.



-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie