RE: Bound parameters in escaped functions

Thank you, Hiroshi.

Do you have any idea when there is likely to be a formal release containing this fix?

Kind regards
Grant

From: Inoue, Hiroshi <h-inoue(at)dream(dot)email(dot)ne(dot)jp>
Sent: Sunday, 19 May 2019 16:06
To: Grant Shirreffs <GShirreffs(at)stayinfront(dot)com>
Cc: pgsql-odbc(at)lists(dot)postgresql(dot)org
Subject: Re: Bound parameters in escaped functions

Hi Grant,

I've pushed changes to git.
Please look at
https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=summary .

regards,
Hiroshi Inoue
On 2019/05/17 9:48, Inoue, Hiroshi wrote:
Hi Grant,

Thanks for the report.
I would take care of the bug fix.

regards,
Hiroshi Inoue
On 2019/05/14 12:58, Grant Shirreffs wrote:
Hello,

I have found a bug in the translation of ‘?’ parameters in escaped ODBC functions.

Where there is an escaped ODBC function within another, for example:

CASE WHEN {fn LENGTH({fn LTRIM(Name || ?))=? […]

During convert_escape (convert.c:5471), at line 5626 the QueryBuild object is copied to a local temporary (‘ncb’), which is then passed to ‘processParameters’, which then calls recursively back into convert_escape for the inner function.

Then, at line 5771, the parameter number from ncb is passed back into the original QueryBuild, so that query processing carries on successfully. However, crucially, the dollar_number value from ncb, which has been incremented while processing the parameters inside the function, is not passed back. So although the parameters themselves are correctly handled, the incorrect PostgreSQL placeholder symbol is generated.

In the example above, the resulting SQL is:

CASE WHEN LENGTH(LTRIM(Name || $1))=$1::int4 […]

Where it should of course be:

CASE WHEN LENGTH(LTRIM(Name) || $1))=$2:int4

The fix is to copy the dollar_number back from ncb to the original QueryBuild:

if (SQL_ERROR != retval)
{
qb->param_number = nqb.param_number;
qb->dollar_number = nqb.dollar_number;
qb->flags = nqb.flags;
}

[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-green-avg-v1.png]<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
ウイルス フリー｡ www.avg.com<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>