BUG #19925: ECPG inserts host variables unquoted in EXECUTE name(...)

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: ishjaap(dot)singh07(at)gmail(dot)com
Subject: BUG #19925: ECPG inserts host variables unquoted in EXECUTE name(...)
Date: 2026-11-20 07:37:41
Message-ID: 19925-af7cd6812c5d81d1@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19925
Logged by: Ishjaap Singh
Email address: ishjaap(dot)singh07(at)gmail(dot)com
PostgreSQL version: 19.0
Operating system: Ubuntu 24.04 (WSL2), x86_64
Description:

In ECPG, a prepared statement can be executed with its parameter values
in parentheses:

EXEC SQL PREPARE s(int, text) AS INSERT INTO t VALUES ($1, $2);
EXEC SQL EXECUTE s(:id, :name);

For this form, ecpglib doesn't send the host variables as parameters.
It writes each value into the text of the EXECUTE command without
quoting and sends the command with PQexec() (ecpg_build_params() in
execute.c, the ECPGst_exec_with_exprlist branch). This happens
regardless of how the statement was prepared, also after
PREPARE ... FROM.

So a character host variable is parsed as part of the SQL command. A
complete program:

#include <stdio.h>
#include <string.h>

EXEC SQL BEGIN DECLARE SECTION;
int id;
char name[32];
char rows[200];
EXEC SQL END DECLARE SECTION;

int main(void)
{
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL CONNECT TO postgres;
EXEC SQL SET AUTOCOMMIT TO ON;
EXEC SQL CREATE TEMP TABLE t (id int, name text);
EXEC SQL PREPARE s(int, text) AS INSERT INTO t VALUES ($1, $2);

id = 1; strcpy(name, "hello");
EXEC SQL EXECUTE s(:id, :name);
id = 2; strcpy(name, "O'Brien");
EXEC SQL EXECUTE s(:id, :name);
id = 3; strcpy(name, "O'Brien");
EXEC SQL EXECUTE s USING :id, :name;

EXEC SQL SELECT string_agg(id || ' ' || name, ', ') INTO :rows FROM
t;
printf("rows: %s\n", rows);
EXEC SQL DISCONNECT;
return 0;
}

Output on 19.0 (15 gives the same):

SQL error: column "hello" does not exist on line 19
SQL error: unterminated quoted string at or near "'Brien )" on line 21
rows: 3 O'Brien

I expected all three rows to be stored. Only the one passed with USING
is.

To make it work, the program has to put a complete literal such as
'O''Brien' into the variable, and a value taken from untrusted input can
change the command that runs. Boolean, date, timestamp and interval host
variables don't work in this list at all (a bool true becomes the bare
word t, a date 2026-11-18 becomes integer arithmetic). NaN and Infinity
fail the same way. EXECUTE ... USING works for all of these values.

The same code has two more problems with placing the values:

- After inserting a value, ecpglib looks for the next placeholder
starting one character into the value it just inserted. So a value
containing a quote or $n (or ?, with -r questionmarks) can make the
statement fail with "too many arguments", "too few arguments" or a
server error. A bytea value or a character value the program has
already quoted can fail when it's followed by another value:

EXECUTE s(:id, :name) with name = "'O''Brien'" works
EXECUTE s2(:name, :id) with name = "'O''Brien'" ERROR: too many
arguments

- Every placeholder is taken to be two characters long, so for $10 and
up only "$1" is replaced and the rest stays. With ten int values 1, 2,
..., 9 and then 7, the command sent ends in "9 , 70 )", and 70 is
stored, with no error.

I ran these on 15 and 19 built from source, and the code is the same on
all supported branches and master. As far as I can see, none of it is
documented: the ECPG docs only show USING with prepared statements.

Changing the quoting in the stable branches would break programs that
already quote these values themselves, so I'd like to propose:

1. Fix the placeholder handling: continue the search after the inserted
value, and replace the whole placeholder. This only changes cases
that fail or store the wrong value today, so it can go to REL_15 ..
master.

2. A docs note for REL_15 .. master in "Executing a Statement with Input
Parameters": how values in this list are inserted, that a character
value must be a complete escaped literal (pointing to
PQescapeLiteral()), and that USING is the way to pass values as
parameters. Plus a pointer from the ECPG PREPARE page.

3. For master only: ecpglib writes every value in this list as a quoted
literal with PQescapeLiteral(), and the docs describe the new behavior
in place of the note from 2, with a note for programs written for the
old one. Numbers are quoted too, which makes NaN and Infinity work and
avoids keeping a list of types. Values are then converted to the
parameter type the same way as with USING, which changes some cases.
For an integer parameter, a double that isn't a whole number is now
an error (1.5 used to be rounded to 2), and so is a large one that
ecpglib prints with an exponent (1e16 is sent as 1e+16). An int 1 for
a boolean parameter now works. NULL stays the keyword NULL, and bytea
keeps its own literal.

These go on top of my fix for a crash on NULL values in the same code,
which is on Nikita Losev's thread (CF 7664):
https://postgr.es/m/CA+Y2BmfP8hkBFEK2awoAp0uobYXHmQC-C9iAZ7qs2OPfVtEQ5w@mail.gmail.com

I'll send the patches as a reply to this report.

Browse pgsql-bugs by date

  From Date Subject
Next Message Kirill Reshke 2026-11-20 09:36:15 Re: BUG #19650: to_date()/to_timestamp() silently truncate 4+-digit day-of-year input to the first three digits
Previous Message Andrey Rachitskiy 2026-11-20 06:35:40 Re: BUG #19594: to_char/jsonpath format cache serves a format tree parsed in the wrong strict-mode