public inbox for [email protected]
help / color / mirror / Atom feedRe: Getting error 42P02, despite query parameter being sent
2+ messages / 2 participants
[nested] [flat]
* Re: Getting error 42P02, despite query parameter being sent
@ 2024-11-17 10:09 Max Ulidtko <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Max Ulidtko @ 2024-11-17 10:09 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Achilleas Mantzios <[email protected]>; Adrian Klaver <[email protected]>; +Cc: [email protected]
Thanks for replies! I understand now.
Just to clarify user-side motivation: I'm taught that concatenating
data into SQL query strings is bad practice and should be avoided. I
know how to do it safely in my particular case; but apparently the
author of this client library was taught the same, and so their
query-builder doesn't provide the "raw" quoted-interpolation
substitution (the analogue to sql.Literal from Adrian example). Instead
this query-builder relies on the parameters mechanism.
Hence, this limitation forces me to rewrite my query into raw SQL, with
hand-quoting of parameter and query string concatenation.
> if CREATE VIEW stores the Param as a Param
This makes zero sense to me... I assumed that $1 would get substituted
*at query time*, resulting in effectively VALUES ('md5',
'test-param-value') -- not persisted into the view definition. Which is
yes, the former option, Tom; it is sane because that's what $1 does in
every other query type.
If I stare into the abyss regardless, and consider the latter option,
the one that makes no sense to me... I don't see how could it possibly
ever work.
With substitution at some "later time" (expressly not CREATE VIEW query
time), how could this ever work?
CREATE VIEW foobar_view (alg, hash) AS VALUES ('md5', $1); -- suppose
the Param is persisted into view (?!?)
SELECT * from foobar_view where alg = $1;
— is this a 1- or 2-parameter query?
— what do both $1's refer to exactly?
* there's $1 in select query referring to values in column alg, and
* there's $1 supposedly persisted into VALUES of view definition,
referring to a different column with potentially different type.
This makes no sense to me.
So I'm a bit surprised that the (IMO) straightforward semantics of
substitution-at-query-time is not supported.
Nevertheless, acknowledging the "patches welcome" status quo sentiment.
This is helpful; thanks again.
Max
On сб, лис 16 2024 at 11:51:18 -05:00:00, Tom Lane
<[email protected]> wrote:
> Achilleas Mantzios <[email protected]
> <mailto:[email protected]>> writes:
>> Στις 16/11/24 12:55, ο/η Max Ulidtko έγραψε:
>>> The issue I'm hitting with it is exemplified by server logs like
>>> this:
>>>
>>> 2024-11-16 10:28:19.928 UTC [46] LOG: execute <unnamed>: CREATE
>>> VIEW
>>> public.foobar (alg, hash) AS VALUES ('md5', $1);
>>> 2024-11-16 10:28:19.928 UTC [46] DETAIL: parameters: $1 =
>>> 'test-param-value'
>>> 2024-11-16 10:28:19.928 UTC [46] ERROR: there is no parameter $1 at
>>> character 57
>
>> 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.
>
> Indeed. To some extent this is an implementation limitation: the
> parameter is received (and printed if you have logging enabled),
> but it's not passed down to utility statements such as CREATE VIEW.
> But the reason nobody's been in a hurry to lift that restriction
> is that doing so would open a large can of semantic worms. In a
> case like CREATE VIEW, exactly what is this statement supposed to
> mean? I assume you were hoping that it would result in replacement
> of the Param by a Const representing the CREATE-time value of the
> parameter, but why is that a sane definition? It's certainly not
> what a Param normally does. On the other hand, if CREATE VIEW
> stores the Param as a Param (which is what I think would happen
> if we just extended the parameter-passing plumbing), that's unlikely
> to lead to a good outcome either. There might not be any $1 available
> when the view is used, and if there is one it's not necessarily of
> the right data type.
>
> So, pending some defensible design for what should happen and a patch
> implementing that, we've just left it at the status quo, which is that
> Params are only available to the DML statements Achilleas mentioned.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Getting error 42P02, despite query parameter being sent
@ 2024-11-17 17:05 Adrian Klaver <[email protected]>
parent: Max Ulidtko <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2024-11-17 17:05 UTC (permalink / raw)
To: Max Ulidtko <[email protected]>; Tom Lane <[email protected]>; Achilleas Mantzios <[email protected]>; +Cc: [email protected]
On 11/17/24 02:09, Max Ulidtko wrote:
> Thanks for replies! I understand now.
>
> Just to clarify user-side motivation: I'm taught that concatenating data
> into SQL query strings is bad practice and should be avoided. I know how
> to do it safely in my particular case; but apparently the author of this
> client library was taught the same, and so their query-builder doesn't
Why not name the client?
> provide the "raw" quoted-interpolation substitution (the analogue to
> sql.Literal from Adrian example). Instead this query-builder relies on
> the parameters mechanism.
Per the docs:
https://www.psycopg.org/psycopg3/docs/api/sql.html
"
class psycopg.sql.Literal(obj: Any)
A Composable representing an SQL value to include in a query.
Usually you will want to include placeholders in the query and pass
values as execute() arguments. If however you really really need to
include a literal value in the query you can use this object.
The string returned by as_string() follows the normal adaptation rules
for Python objects.
Example:
s1 = sql.Literal("fo'o")
s2 = sql.Literal(42)
s3 = sql.Literal(date(2000, 1, 1))
print(sql.SQL(', ').join([s1, s2, s3]).as_string(conn))
'fo''o', 42, '2000-01-01'::date
Changed in version 3.1: Add a type cast to the representation if useful
in ambiguous context (e.g. '2000-01-01'::date)
"
It is meant to pass in a value not something else, say an identifier
which is covered by sql.Identifier. The purpose of the sql module is to
build dynamic SQL safely.
>
> Hence, this limitation forces me to rewrite my query into raw SQL, with
> hand-quoting of parameter and query string concatenation.
>
> > if CREATE VIEW stores the Param as a Param
>
> This makes zero sense to me... I assumed that $1 would get substituted
> *at query time*, resulting in effectively VALUES ('md5',
> 'test-param-value') -- not persisted into the view definition. Which is
> yes, the former option, Tom; it is sane because that's what $1 does in
> every other query type.
>
> If I stare into the abyss regardless, and consider the latter option,
> the one that makes no sense to me... I don't see how could it possibly
> ever work.
>
> With substitution at some "later time" (expressly not CREATE VIEW query
> time), how could this ever work?
>
> CREATE VIEW foobar_view (alg, hash) AS VALUES ('md5', $1); -- suppose
> the Param is persisted into view (?!?)
>
> SELECT * from foobar_view where alg = $1;
> — is this a 1- or 2-parameter query?
> — what do both $1's refer to exactly?
> * there's $1 in select query referring to values in column alg, and
> * there's $1 supposedly persisted into VALUES of view definition,
> referring to a different column with potentially different type.
>
> This makes no sense to me.
>
> So I'm a bit surprised that the (IMO) straightforward semantics of
> substitution-at-query-time is not supported.
>
> Nevertheless, acknowledging the "patches welcome" status quo sentiment.
> This is helpful; thanks again.
>
> Max
>
> On сб, лис 16 2024 at 11:51:18 -05:00:00, Tom Lane <[email protected]>
> wrote:
>> Achilleas Mantzios <[email protected]
>> <mailto:[email protected]>> writes:
>>
>> Στις 16/11/24 12:55, ο/η Max Ulidtko έγραψε:
>>
>> The issue I'm hitting with it is exemplified by server logs
>> like this: 2024-11-16 10:28:19.928 UTC [46] LOG: execute
>> <unnamed>: CREATE VIEW public.foobar (alg, hash) AS VALUES
>> ('md5', $1); 2024-11-16 10:28:19.928 UTC [46] DETAIL:
>> parameters: $1 = 'test-param-value' 2024-11-16 10:28:19.928
>> UTC [46] ERROR: there is no parameter $1 at character 57
>>
>> 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.
>>
>> Indeed. To some extent this is an implementation limitation: the
>> parameter is received (and printed if you have logging enabled), but
>> it's not passed down to utility statements such as CREATE VIEW. But
>> the reason nobody's been in a hurry to lift that restriction is that
>> doing so would open a large can of semantic worms. In a case like
>> CREATE VIEW, exactly what is this statement supposed to mean? I assume
>> you were hoping that it would result in replacement of the Param by a
>> Const representing the CREATE-time value of the parameter, but why is
>> that a sane definition? It's certainly not what a Param normally does.
>> On the other hand, if CREATE VIEW stores the Param as a Param (which
>> is what I think would happen if we just extended the parameter-passing
>> plumbing), that's unlikely to lead to a good outcome either. There
>> might not be any $1 available when the view is used, and if there is
>> one it's not necessarily of the right data type. So, pending some
>> defensible design for what should happen and a patch implementing
>> that, we've just left it at the status quo, which is that Params are
>> only available to the DML statements Achilleas mentioned. regards, tom
>> lane
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-11-17 17:05 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-17 10:09 Re: Getting error 42P02, despite query parameter being sent Max Ulidtko <[email protected]>
2024-11-17 17:05 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox