public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Achilleas Mantzios <[email protected]>
To: [email protected]
Subject: Re: Getting error 42P02, despite query parameter being sent
Date: Sun, 17 Nov 2024 14:02:19 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

On 11/17/24 11:44, Achilleas Mantzios wrote:
> 
> Στις 16/11/24 18:09, ο/η Adrian Klaver έγραψε:
>> On 11/16/24 03:15, Achilleas Mantzios wrote:
>>>
>>> Στις 16/11/24 12:55, ο/η Max Ulidtko έγραψε:
>>>> Greetings, group!
>>>>
>>>> I'm trying to understand a low-level issue. Am evaluating a new 
>>>> client library for Postgres; it's not particularly popular / 
>>>> mainstream, and as I've understood so far, sports an independent 
>>>> implementation of PG binary protocol.
>>>>
>>>> The issue I'm hitting with it is exemplified by server logs like this:
>>>>
>>>> 2024-11-16 10:28:19.927 UTC [46] LOG: statement: SET client_encoding 
>>>> = 'UTF8';SET client_min_messages TO WARNING;
>>>> 2024-11-16 10:28:19.928 UTC [46] LOG: execute <unnamed>: CREATE VIEW 
>>>> public.foobar (alg, hash) AS VALUES ('md5', $1);
>>>
>>> At least for SQL level prepared statements the statement has to be 
>>> one of :
>>>
>>> |SELECT|, |INSERT|, |UPDATE|, |DELETE|, |MERGE|, or |VALUES|
>>>
>>> |so CREATE is not valid, and I guess the extended protocol prepared 
>>> statements aint no different in this regard.
>>
>> It would seem so. Using psycopg:
>>
>> import psycopg
>> from psycopg import sql
>>
>> con = 
>> psycopg.connect("postgresql://postgres:[email protected]:5432/test")
>> cur = con.cursor()
>> cur.execute("CREATE VIEW public.foobar (alg, hash) AS VALUES ('md5', 
>> %s)", ['test'])
>>
>> IndeterminateDatatype: could not determine data type of parameter $1
>>
>> cur.execute(sql.SQL("CREATE VIEW public.foobar (alg, hash) AS VALUES 
>> ('md5', {})").format(sql.Literal('test')))
>>
>> con.commit()
>>
>> cur.execute("select * from foobar")
>> cur.fetchone()
>>
>> ('md5', 'test')
> 
> I dont know python but this does not look like a solid prepared statement.
> 
> https://www.psycopg.org/psycopg3/docs/advanced/prepare.html
> 
> Does not seem to have used the prepared statement circuitry.
> 

The second example is not and was not meant to be. It was meant to show 
how you could dynamically create an SQL statement when it will not 
accept parameters.

FYI, this was run using psycopg(3) which does things differently, by 
default, then psycopg2. For full explanation see:

https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html

Server-side binding





-- 
Adrian Klaver
[email protected]

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Getting error 42P02, despite query parameter being sent
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox