Bound parameters in escaped functions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Bound parameters in escaped functions

Grant Shirreffs

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;

            }

 

Reply | Threaded
Open this post in threaded view
|

Re: Bound parameters in escaped functions

Inoue, Hiroshi-2
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;

            }



ウイルス フリー。 www.avg.com
Reply | Threaded
Open this post in threaded view
|

Re: Bound parameters in escaped functions

Inoue, Hiroshi-2
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;

            }


ウイルス フリー。 www.avg.com
Reply | Threaded
Open this post in threaded view
|

RE: Bound parameters in escaped functions

Grant Shirreffs

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 <[hidden email]>
Sent: Sunday, 19 May 2019 16:06
To: Grant Shirreffs <[hidden email]>
Cc: [hidden email]
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;

            }

 

ウイルス フリー。 www.avg.com

 

Reply | Threaded
Open this post in threaded view
|

Re: Bound parameters in escaped functions

Inoue, Hiroshi-2
Hi,

We would make a new release this weekend.

regards,
Hiroshi Inoue

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

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 [hidden email]
Sent: Sunday, 19 May 2019 16:06
To: Grant Shirreffs [hidden email]
Cc: [hidden email]
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;

            }



ウイルス フリー。 www.avg.com