Thread: 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
Hi
út 27. 7. 2021 v 19:10 odesílatel Ray O'Donnell <ray@rodonnell.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@rodonnell.ie
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