DevHeads.net

Bound parameters in escaped functions

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;
}

Comments

Re: Bound parameters in escaped functions

By Inoue, Hiroshi at 05/16/2019 - 20:48

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:

Re: Bound parameters in escaped functions

By Inoue, Hiroshi at 05/19/2019 - 00:06

Hi Grant,

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

regards,
Hiroshi Inoue

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

RE: Bound parameters in escaped functions

By Grant Shirreffs at 05/19/2019 - 17:21

Thank you, Hiroshi.

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

Kind regards
Grant

Cc: <a href="mailto:pgsql- ... at lists dot postgresql.org">pgsql- ... at lists dot postgresql.org</a>

Hi Grant,

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

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>

Re: Bound parameters in escaped functions

By Inoue, Hiroshi at 05/20/2019 - 07:58

Hi,

We would make a new release this weekend.

regards,
Hiroshi Inoue

On 2019/05/20 6:21, Grant Shirreffs wrote: