Thread: Parameter in SQL query being misinterpreted
Good day! I've run into a quirk executing an SQL statement and am not sure if it's a fundamental issue with my query or a bug in psycopg3. In short, I'm inserting/updating a row using both NOW() and 'NOW() plus a varying value'. The NOW() value is fine, but what ends up in the database for the second one is just "NOW() + 4 seconds". I finally realized this is because of how the command is being processed, the variable becomes "$4" and apparently the dollar sign is lost. I'm guessing this is an example of why one of the bullet points at https://www.psycopg.org/psycopg3/docs/basic/params.html says "The placeholder must not be quoted". :-/ I would appreciate any advice on how to work around this while still passing in the value safely. Here's the command from Python. The value we're looking at is for "nextupdate". "Stats" is a dictionary which contains "NextDBLog" as an integer whose value is something between 120 and 800. =-=-=-=-=-=-=-=-=- dbRWcur.execute(''' INSERT INTO backendstatus (nodeid, debug, started, lastupdated, nextupdate, version, platform, python, cpucores, state, uptime, taskstats) VALUES (%(nodeid)s, %(debug)s, %(laststarteddb)s, NOW(), NOW() + INTERVAL '%(NextDBLog)s SECOND', %(version)s, %(platform)s, %(python)s, %(cpucores)s, %(state)s, %(uptime)s, %(statsstring)s) ON CONFLICT (nodeid) DO UPDATE SET debug = excluded.debug, started = excluded.started, lastupdated = excluded.lastupdated, nextupdate = excluded.nextupdate, version = excluded.version, platform = excluded.platform, python = excluded.python, cpucores = excluded.cpucores, state = excluded.state, uptime = excluded.uptime, taskstats = excluded.taskstats; ''', Stats) =-=-=-=-=-=-=-=-=- After the command is processed this is the contents of dbRWcur._query.query.decode('utf-8'). =-=-=-=-=-=-=-=-=- INSERT INTO backendstatus (nodeid, debug, started, lastupdated, nextupdate, version, platform, python, cpucores, state, uptime, taskstats) VALUES ($1, $2, $3, NOW(), NOW() + INTERVAL '$4 SECOND', $5, $6, $7, $8, $9, $10, $11) ON CONFLICT (nodeid) DO UPDATE SET debug = excluded.debug, started = excluded.started, lastupdated = excluded.lastupdated, nextupdate = excluded.nextupdate, version = excluded.version, platform = excluded.platform, python = excluded.python, cpucores = excluded.cpucores, state = excluded.state, uptime = excluded.uptime, taskstats = excluded.taskstats; =-=-=-=-=-=-=-=-=- This is being run on Rocky Linux v9.5 (essentially Red Hat / RHEL). The relevant installed packages are: python3-3.9.19-8.el9_5.1.x86_64 python3-psycopg3-3.1.18-4.el9.noarch (from EPEL) postgresql-server-15.8-2.module+el9.5.0+28955+a22540b0.x86_64 Daniel Johnson djohnson@progman.us
On 12/5/24 17:27, Daniel Johnson wrote: > Good day! I've run into a quirk executing an SQL statement and am not > sure if it's a fundamental issue with my query or a bug in psycopg3. > > In short, I'm inserting/updating a row using both NOW() and 'NOW() plus > a varying value'. The NOW() value is fine, but what ends up in the > database for the second one is just "NOW() + 4 seconds". I finally > realized this is because of how the command is being processed, the > variable becomes "$4" and apparently the dollar sign is lost. > > I'm guessing this is an example of why one of the bullet points at > https://www.psycopg.org/psycopg3/docs/basic/params.html says "The > placeholder must not be quoted". :-/ I would appreciate any advice on > how to work around this while still passing in the value safely Pretty sure the below does not work because the placeholder is becoming part of the literal string: NOW() + INTERVAL '%(NextDBLog)s SECOND' You might try something like: NOW() + INTERVAL || ' ' || %(NextDBLog)s || ' SECOND' If that does work then you will need to use the psycopg.sql module to build that part of the query > > > Here's the command from Python. The value we're looking at is for > "nextupdate". "Stats" is a dictionary which contains "NextDBLog" as an > integer whose value is something between 120 and 800. > =-=-=-=-=-=-=-=-=- > dbRWcur.execute(''' > INSERT INTO backendstatus > (nodeid, debug, started, lastupdated, nextupdate, version, > platform, python, cpucores, state, uptime, taskstats) > VALUES > (%(nodeid)s, %(debug)s, %(laststarteddb)s, NOW(), NOW() + > INTERVAL '%(NextDBLog)s SECOND', %(version)s, %(platform)s, %(python)s, > %(cpucores)s, %(state)s, %(uptime)s, %(statsstring)s) > ON CONFLICT > (nodeid) > DO UPDATE SET > debug = excluded.debug, > started = excluded.started, > lastupdated = excluded.lastupdated, > nextupdate = excluded.nextupdate, > version = excluded.version, > platform = excluded.platform, > python = excluded.python, > cpucores = excluded.cpucores, > state = excluded.state, > uptime = excluded.uptime, > taskstats = excluded.taskstats; > ''', Stats) > =-=-=-=-=-=-=-=-=- > > > After the command is processed this is the contents of > dbRWcur._query.query.decode('utf-8'). > =-=-=-=-=-=-=-=-=- > INSERT INTO backendstatus > (nodeid, debug, started, lastupdated, nextupdate, version, > platform, python, cpucores, state, uptime, taskstats) > VALUES > ($1, $2, $3, NOW(), NOW() + INTERVAL '$4 SECOND', $5, $6, $7, $8, > $9, $10, $11) > ON CONFLICT > (nodeid) > DO UPDATE SET > debug = excluded.debug, > started = excluded.started, > lastupdated = excluded.lastupdated, > nextupdate = excluded.nextupdate, > version = excluded.version, > platform = excluded.platform, > python = excluded.python, > cpucores = excluded.cpucores, > state = excluded.state, > uptime = excluded.uptime, > taskstats = excluded.taskstats; > =-=-=-=-=-=-=-=-=- > > This is being run on Rocky Linux v9.5 (essentially Red Hat / RHEL). The > relevant installed packages are: > python3-3.9.19-8.el9_5.1.x86_64 > python3-psycopg3-3.1.18-4.el9.noarch (from EPEL) > postgresql-server-15.8-2.module+el9.5.0+28955+a22540b0.x86_64 > > > > Daniel Johnson > djohnson@progman.us > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/5/24 17:37, Adrian Klaver wrote: > On 12/5/24 17:27, Daniel Johnson wrote: >> Good day! I've run into a quirk executing an SQL statement and am not >> sure if it's a fundamental issue with my query or a bug in psycopg3. >> >> In short, I'm inserting/updating a row using both NOW() and 'NOW() >> plus a varying value'. The NOW() value is fine, but what ends up in >> the database for the second one is just "NOW() + 4 seconds". I >> finally realized this is because of how the command is being >> processed, the variable becomes "$4" and apparently the dollar sign is >> lost. >> >> I'm guessing this is an example of why one of the bullet points at >> https://www.psycopg.org/psycopg3/docs/basic/params.html says "The >> placeholder must not be quoted". :-/ I would appreciate any advice >> on how to work around this while still passing in the value safely > > Pretty sure the below does not work because the placeholder is becoming > part of the literal string: > > NOW() + INTERVAL '%(NextDBLog)s SECOND' > > You might try something like: > > NOW() + INTERVAL || ' ' || %(NextDBLog)s || ' SECOND' > > If that does work then you will need to use the psycopg.sql module to > build that part of the query >> What I ended getting to work: from psycopg import sql cur.execute(sql.SQL("select now(), now() + ({} || ' seconds')::interval ").format(sql.Literal(4))) cur.fetchone() (datetime.datetime(2024, 12, 5, 17, 57, 55, 670218, tzinfo=zoneinfo.ZoneInfo(key='US/Pacific')), datetime.datetime(2024, 12, 5, 17, 57, 59, 670218, tzinfo=zoneinfo.ZoneInfo(key='US/Pacific'))) -- Adrian Klaver adrian.klaver@aklaver.com
On 12/5/24 18:00, Adrian Klaver wrote:
> On 12/5/24 17:37, Adrian Klaver wrote:
>> On 12/5/24 17:27, Daniel Johnson wrote:
>> You might try something like:
>>
>> NOW() + INTERVAL || ' ' || %(NextDBLog)s || ' SECOND'
>>
>> If that does work then you will need to use the psycopg.sql module to
>> build that part of the query
>>>
>
>
> What I ended getting to work:
>
> from psycopg import sql
>
> cur.execute(sql.SQL("select now(), now() + ({} || ' seconds')::interval
> ").format(sql.Literal(4)))
>
> cur.fetchone()
> (datetime.datetime(2024, 12, 5, 17, 57, 55, 670218,
> tzinfo=zoneinfo.ZoneInfo(key='US/Pacific')),
> datetime.datetime(2024, 12, 5, 17, 57, 59, 670218,
> tzinfo=zoneinfo.ZoneInfo(key='US/Pacific')))
>
>
Then there is the alternative I keep forgetting about, the function
make_interval():
https://www.postgresql.org/docs/current/functions-datetime.html
make_interval ( [ years int [, months int [, weeks int [, days int [,
hours int [, mins int [, secs double precision ]]]]]]] ) → interval
Then you could pass parameters to the function.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, 6 Dec 2024 at 02:27, Daniel Johnson <djohnson@progman.us> wrote:
>
> Good day! I've run into a quirk executing an SQL statement and am not
> sure if it's a fundamental issue with my query or a bug in psycopg3.
It's a mix of an issue with your query, postgres being too forgiving
in parsing the interval, and psycopg 3 behaving very differently from
psycopg 2.
Your query assumes a literal replacement of the placeholder in the
query, which was true in psycopg 2, but in psycopg 3 uses real
placeholders. However it doesn't parse the query to check if a
placeholder is in a string or not (arguably it shouldn't touch the
ones in the strings, but no, we don't parse queries). So the %s as you
have seen becomes a $4, but postgres will not replace that parameter
with a value, because it will surely parse the query and will not
touch the string.
Arguably, Postgres should complain about a malformed interval literal,
but this behaviour was probably designed in times in which the
principle "be conservative in what you do, be liberal in what you
accept from others" was still considered a good idea and fixing it
would kill too many dinosaurs.
Adrian has already provided a few workarounds for the problem you
report - thank you very much!. The one I prefer is however the
following, because it does without string operations: it makes use of
the possibility to multiply an interval by a scalar:
>>> cur.execute("select now(), now() + %s * '1 second'::interval",
(10,)).fetchone()
(datetime.datetime(2024, 12, 6, 2, 33, 32, 117134,
tzinfo=zoneinfo.ZoneInfo(key='Europe/London')),
datetime.datetime(2024, 12, 6, 2, 33, 42, 117134,
tzinfo=zoneinfo.ZoneInfo(key='Europe/London')))
cheers
-- Daniele
On 12/5/24 20:39, Daniele Varrazzo wrote:
<snip>
> Adrian has already provided a few workarounds for the problem you
> report - thank you very much!. The one I prefer is however the
> following, because it does without string operations: it makes use of
> the possibility to multiply an interval by a scalar:
>
> >>> cur.execute("select now(), now() + %s * '1 second'::interval",
> (10,)).fetchone()
> (datetime.datetime(2024, 12, 6, 2, 33, 32, 117134,
> tzinfo=zoneinfo.ZoneInfo(key='Europe/London')),
> datetime.datetime(2024, 12, 6, 2, 33, 42, 117134,
> tzinfo=zoneinfo.ZoneInfo(key='Europe/London')))
Oh I hadn't even thought about multiplying an interval, that's a very
clean approach. Prior to e-mailing the list I'd tried something similar
to (but not as thorough as) Adrian's suggestion and it had failed, but
if it works I like the look of this.
I vaguely recall having a similar interval manipulation headache in a
PHP portion of this project, I shall try the same thing there.
Thank you so much!
Daniel Johnson
djohnson@progman.us