Thread: libpq: What can and cannot be bound? How to know?
For example with [NOTIFY][1]. The doc states:
> Payload: This must be specified as a simple string literal
Does that mean we cannot bind the payload?
I.e. the pseudo code:
> Payload: This must be specified as a simple string literal
Does that mean we cannot bind the payload?
I.e. the pseudo code:
```
conn.exec(bind(msg), "NOTIFY {} $1", conn.escapeName(channel));
```
is invalid? And I must use instead
```
conn.exec("NOTIFY {} {}", conn.escapeName(channel), conn.escapeLiteral(msg))`?
```
I can try, of course, but could there be a obvious way to know what can and cannot be bound, just from the doc?
That would make it easier to deal with SQL injection to be able to bind for example.
And knowing what can be bound would be useful.
```
I can try, of course, but could there be a obvious way to know what can and cannot be bound, just from the doc?
That would make it easier to deal with SQL injection to be able to bind for example.
And knowing what can be bound would be useful.
On Wed, 2023-06-21 at 11:00 +0200, Dominique Devienne wrote:
> For example with [NOTIFY][1]. The doc states:
>
> > Payload: This must be specified as a simple string literal
>
> Does that mean we cannot bind the payload?
> I.e. the pseudo code:
> ```
> conn.exec(bind(msg), "NOTIFY {} $1", conn.escapeName(channel));
> ```
> is invalid? And I must use instead
> ```
> conn.exec("NOTIFY {} {}", conn.escapeName(channel), conn.escapeLiteral(msg))`?
> ```
> I can try, of course, but could there be a obvious way to know what can and cannot be bound, just from the doc?
>
> That would make it easier to deal with SQL injection to be able to bind for example.
> And knowing what can be bound would be useful.
This is not adequately documented.
The documentation for PREPARE says:
Any SELECT, INSERT, UPDATE, DELETE, MERGE, or VALUES statement.
so NOTIFY is not supported. However, you need some inside knowledge to know
that what you are running is an "unnamed prepared statement" and that the limitation
stated in PREPARE applies.
Yours,
Laurenz Albe
On Wed, Jun 21, 2023 at 1:52 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2023-06-21 at 11:00 +0200, Dominique Devienne wrote:
> [...] obvious way to know what can and cannot be bound, just from the doc?
This is not adequately documented.
Bummer.
The documentation for PREPARE says:
Note that I'm not even preparing in this case, but using PQexecParams() instead.
So I'm unlikely to look at the PREPARE doc, but the one from the COMMAND
I'm actually using, i.e. NOTIFY in my example.
so NOTIFY is not supported.
Bummer again.
So I must PQescapeIdentifier() and PQescapeLiteral() to have an iron-clad NOTIFY,
as the 2nd pseudo-code above showed then. Thanks for confirming Laurenz.
However, you need some inside knowledge to know
that what you are running is an "unnamed prepared statement" and that the limitation
stated in PREPARE applies.
I don't know what shape or form this could be specified in the doc.
Maybe specific and consistent typography for each "argument" kind,
like names, non-bind-able literals, bind-able literals? Just thinking aloud.
With a link in all commands to a "central" page about SQL Injection and binding and format(), etc... maybe?
I do find it strange, from the user perspective, not to be able to bind the NOTIFY's payload text message.
Or be able to bind the password in a CREATE USER. (Yes, I know about PQencryptPasswordConn()).
I'm sure there are good technical reason. But from the outside, it is surprising and a bit inconsistent.
My $0.02. --DD
On Wed, Jun 21, 2023 at 6:09 AM Dominique Devienne <ddevienne@gmail.com> wrote:
I'm sure there are good technical reason. But from the outside, it is surprising and a bit inconsistent.
The planner is the thing that handles binds. The only things that are planned are queries - i.e., SQL commands that are capable of producing result sets from data within tables. I agree this seems like it should be documented in places besides PREPARE.
Reworking that core design choice doesn't seem like a great use of time. Especially when alternatives exist. Specifically, the pg_notify function that can be parameterized and handles the SQL-injection stuff for you.
David J.
On Wed, Jun 21, 2023 at 4:20 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
The planner is the thing that handles binds. [...]
Depends what you mean by "handles", since when I asked about "bind peeking" during planning,
I think the answer was that it was not supported. So I don't see the different link between planning per-se
and binds, which seem more related to the executor once a plan was chosen, from my naive perspective.
But of course, I'm reasoning purely on my little abstract understanding of what that machinery could be...
Reworking that core design choice doesn't seem like a great use of time. Especially when alternatives exist.
Are you saying conn.exec("NOTIFY {}, {}", conn.escapeName(channel), conn.escapeLiteral(payload))
is somehow worse than conn.exec(bind(channel, payload), "SELECT pg_notify($1, $2)")?
I'm not asking in the abstract, as I'll be wrapping these in typesafe and SQL-injection-safe wrappers soon.
And since channel is a name, does the string bound to the pg_notify() call needs to be escaped or not?
I've had "loads of fun" troubleshooting why ::regrole casts failed for names that need escaping,
so it's not a rethorical question either... Whether a function taking a name requires the name to be
escaped on the "outside", or will be escaped "inside", is not really specified, at least that I can see.
Specifically, the pg_notify function that can be parameterized and handles the SQL-injection stuff for you.
Sure, for that one example, there's a function equivalent that allows wrapping.
Is that a native function? Or a SQL or PL/SQL function that just basically does the escape*()
that I showed above? Note that performance matters much between the two, but I'm curious.
Still, not all SQL COMMAND taking names and/or literals are similarly wrapped in functions.
So my point remains. That you think there's low to zero value in it, sure, I get it. --DD