Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MERGE statement loses parameters in WHEN (NOT) MATCHED clause that will never be matched, crashes server in some situations [CORE6280] #6522

Closed
firebird-automations opened this issue Apr 9, 2020 · 26 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @mrotteveel

Is duplicated by JDBC621

In a MERGE statement with a WHEN (NOT) MATCHED clause that will never be matched, the parameters defined in that clause are lost. Depending on the exact statement, preparing the statement can crash the server.

Using table:

create table t (i int not null primary key, j int);

For example:

EXECUTE block
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

will result in error "Input parameters mismatch". Preparing this MERGE statement (using Jaybird), will result in a statement described with a single parameter (see also JDBC621).

Replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 2 parameters).

Adding another clause (eg "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)") and executing with 2 parameters yields an error "SQLDA error; Wrong number of parameters (expected 3, got 2)", but using three parameters will again yield "Input parameters mismatch". Attempts to prepare that MERGE statement with Jaybird will result in a crash of Firebird.

Here again, replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 3 parameters).

It looks like Firebird detects that the second WHEN MATCHED clause will never be used, and doesn't allocate a parameter for it (or at least not correctly).

Example (Java 11) program to let Jaybird 4 crash the Firebird 3.0.5 server:

public class MergeCrash {
public static void main(String[] args) throws SQLException {
try (var connection = DriverManager.getConnection("jdbc:firebird://localhost/C:/Data/Db/FB3TESTDATABASE.FDB",
"sysdba", "masterkey");
var pstmt = connection.prepareStatement("merge into t using (select 1 x from rdb$database) on 1 = 1 \n" +
"when matched then update set j = ? \n" +
"when matched and i = ? then delete \n" +
"WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)")) {
var pmd = pstmt.getParameterMetaData();
System.out.println(pmd.getParameterCount());
}
}
}

Commits: bba40d5 4114bf3

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: In a MERGE statement with WHEN (NOT) MATCHED clause that will be unused, the parameters defined in that clause are lost. Depending on the exact statement, preparing the statement can crash the server.

For example:

EXECUTE block
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

will result in error "Input parameters mismatch". Preparing this MERGE statement (using Jaybird), will result in a statement described with a single parameter (see also JDBC621).

Replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 2 parameters).

Adding another clause (eg "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)") and executing with 2 parameters yields an error "SQLDA error; Wrong number of parameters (expected 3, got 2)", but using three parameters will again yield "Input parameters mismatch". Attempts to prepare that MERGE statement with Jaybird will result in a crash of Firebird.

Here again, replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 3 parameters).

It looks like Firebird detects that the second WHEN MATCHED clause will never be used, and doesn't allocate a parameter for it (or at least not correctly).

Example (Java 11) program to let Jaybird crash the Firebird 3.0.5 server:

public class MergeCrash {
public static void main(String[] args) throws SQLException {
try (var connection = DriverManager.getConnection("jdbc:firebird://localhost/C:/Data/Db/FB3TESTDATABASE.FDB",
"sysdba", "masterkey");
var pstmt = connection.prepareStatement("merge into t using (select 1 x from rdb$database) on 1 = 1 \n" +
"when matched then update set j = ? \n" +
"when matched and i = ? then delete \n" +
"WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)")) {
var pmd = pstmt.getParameterMetaData();
System.out.println(pmd.getParameterCount());
}
}
}

=>

In a MERGE statement with WHEN (NOT) MATCHED clause that will be unused, the parameters defined in that clause are lost. Depending on the exact statement, preparing the statement can crash the server.

Using table:

create table t (i int not null primary key, j int);

For example:

EXECUTE block
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

will result in error "Input parameters mismatch". Preparing this MERGE statement (using Jaybird), will result in a statement described with a single parameter (see also JDBC621).

Replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 2 parameters).

Adding another clause (eg "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)") and executing with 2 parameters yields an error "SQLDA error; Wrong number of parameters (expected 3, got 2)", but using three parameters will again yield "Input parameters mismatch". Attempts to prepare that MERGE statement with Jaybird will result in a crash of Firebird.

Here again, replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 3 parameters).

It looks like Firebird detects that the second WHEN MATCHED clause will never be used, and doesn't allocate a parameter for it (or at least not correctly).

Example (Java 11) program to let Jaybird crash the Firebird 3.0.5 server:

public class MergeCrash {
public static void main(String[] args) throws SQLException {
try (var connection = DriverManager.getConnection("jdbc:firebird://localhost/C:/Data/Db/FB3TESTDATABASE.FDB",
"sysdba", "masterkey");
var pstmt = connection.prepareStatement("merge into t using (select 1 x from rdb$database) on 1 = 1 \n" +
"when matched then update set j = ? \n" +
"when matched and i = ? then delete \n" +
"WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)")) {
var pmd = pstmt.getParameterMetaData();
System.out.println(pmd.getParameterCount());
}
}
}

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Link: This issue is duplicated by JDBC621 [ JDBC621 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: In a MERGE statement with WHEN (NOT) MATCHED clause that will be unused, the parameters defined in that clause are lost. Depending on the exact statement, preparing the statement can crash the server.

Using table:

create table t (i int not null primary key, j int);

For example:

EXECUTE block
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

will result in error "Input parameters mismatch". Preparing this MERGE statement (using Jaybird), will result in a statement described with a single parameter (see also JDBC621).

Replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 2 parameters).

Adding another clause (eg "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)") and executing with 2 parameters yields an error "SQLDA error; Wrong number of parameters (expected 3, got 2)", but using three parameters will again yield "Input parameters mismatch". Attempts to prepare that MERGE statement with Jaybird will result in a crash of Firebird.

Here again, replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 3 parameters).

It looks like Firebird detects that the second WHEN MATCHED clause will never be used, and doesn't allocate a parameter for it (or at least not correctly).

Example (Java 11) program to let Jaybird crash the Firebird 3.0.5 server:

public class MergeCrash {
public static void main(String[] args) throws SQLException {
try (var connection = DriverManager.getConnection("jdbc:firebird://localhost/C:/Data/Db/FB3TESTDATABASE.FDB",
"sysdba", "masterkey");
var pstmt = connection.prepareStatement("merge into t using (select 1 x from rdb$database) on 1 = 1 \n" +
"when matched then update set j = ? \n" +
"when matched and i = ? then delete \n" +
"WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)")) {
var pmd = pstmt.getParameterMetaData();
System.out.println(pmd.getParameterCount());
}
}
}

=>

In a MERGE statement with a WHEN (NOT) MATCHED clause that will never be matched, the parameters defined in that clause are lost. Depending on the exact statement, preparing the statement can crash the server.

Using table:

create table t (i int not null primary key, j int);

For example:

EXECUTE block
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

will result in error "Input parameters mismatch". Preparing this MERGE statement (using Jaybird), will result in a statement described with a single parameter (see also JDBC621).

Replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 2 parameters).

Adding another clause (eg "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)") and executing with 2 parameters yields an error "SQLDA error; Wrong number of parameters (expected 3, got 2)", but using three parameters will again yield "Input parameters mismatch". Attempts to prepare that MERGE statement with Jaybird will result in a crash of Firebird.

Here again, replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 3 parameters).

It looks like Firebird detects that the second WHEN MATCHED clause will never be used, and doesn't allocate a parameter for it (or at least not correctly).

Example (Java 11) program to let Jaybird crash the Firebird 3.0.5 server:

public class MergeCrash {
public static void main(String[] args) throws SQLException {
try (var connection = DriverManager.getConnection("jdbc:firebird://localhost/C:/Data/Db/FB3TESTDATABASE.FDB",
"sysdba", "masterkey");
var pstmt = connection.prepareStatement("merge into t using (select 1 x from rdb$database) on 1 = 1 \n" +
"when matched then update set j = ? \n" +
"when matched and i = ? then delete \n" +
"WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)")) {
var pmd = pstmt.getParameterMetaData();
System.out.println(pmd.getParameterCount());
}
}
}

summary: MERGE statement loses parameters in unused WHEN (NOT) MATCHED clauses, crashes server in some situations => MERGE statement loses parameters in WHEN (NOT) MATCHED clause that will never be matched, crashes server in some situations

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: In a MERGE statement with a WHEN (NOT) MATCHED clause that will never be matched, the parameters defined in that clause are lost. Depending on the exact statement, preparing the statement can crash the server.

Using table:

create table t (i int not null primary key, j int);

For example:

EXECUTE block
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

will result in error "Input parameters mismatch". Preparing this MERGE statement (using Jaybird), will result in a statement described with a single parameter (see also JDBC621).

Replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 2 parameters).

Adding another clause (eg "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)") and executing with 2 parameters yields an error "SQLDA error; Wrong number of parameters (expected 3, got 2)", but using three parameters will again yield "Input parameters mismatch". Attempts to prepare that MERGE statement with Jaybird will result in a crash of Firebird.

Here again, replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 3 parameters).

It looks like Firebird detects that the second WHEN MATCHED clause will never be used, and doesn't allocate a parameter for it (or at least not correctly).

Example (Java 11) program to let Jaybird crash the Firebird 3.0.5 server:

public class MergeCrash {
public static void main(String[] args) throws SQLException {
try (var connection = DriverManager.getConnection("jdbc:firebird://localhost/C:/Data/Db/FB3TESTDATABASE.FDB",
"sysdba", "masterkey");
var pstmt = connection.prepareStatement("merge into t using (select 1 x from rdb$database) on 1 = 1 \n" +
"when matched then update set j = ? \n" +
"when matched and i = ? then delete \n" +
"WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)")) {
var pmd = pstmt.getParameterMetaData();
System.out.println(pmd.getParameterCount());
}
}
}

=>

In a MERGE statement with a WHEN (NOT) MATCHED clause that will never be matched, the parameters defined in that clause are lost. Depending on the exact statement, preparing the statement can crash the server.

Using table:

create table t (i int not null primary key, j int);

For example:

EXECUTE block
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

will result in error "Input parameters mismatch". Preparing this MERGE statement (using Jaybird), will result in a statement described with a single parameter (see also JDBC621).

Replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 2 parameters).

Adding another clause (eg "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)") and executing with 2 parameters yields an error "SQLDA error; Wrong number of parameters (expected 3, got 2)", but using three parameters will again yield "Input parameters mismatch". Attempts to prepare that MERGE statement with Jaybird will result in a crash of Firebird.

Here again, replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 3 parameters).

It looks like Firebird detects that the second WHEN MATCHED clause will never be used, and doesn't allocate a parameter for it (or at least not correctly).

Example (Java 11) program to let Jaybird 4 crash the Firebird 3.0.5 server:

public class MergeCrash {
public static void main(String[] args) throws SQLException {
try (var connection = DriverManager.getConnection("jdbc:firebird://localhost/C:/Data/Db/FB3TESTDATABASE.FDB",
"sysdba", "masterkey");
var pstmt = connection.prepareStatement("merge into t using (select 1 x from rdb$database) on 1 = 1 \n" +
"when matched then update set j = ? \n" +
"when matched and i = ? then delete \n" +
"WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)")) {
var pmd = pstmt.getParameterMetaData();
System.out.println(pmd.getParameterCount());
}
}
}

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

When you say about potential to crash, do you have any evidence or it's just speculation?

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

I was able to crash the server repeatedly by executing above Java program against a database with the example table t. I don't have a dump at this time, I will see if I can produce a dump this weekend.

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Version: 4.0 Beta 1 [ 10750 ]

environment: Firebird-3.0.5.33220-0_x64 on Windows 10 => Firebird-3.0.5.33220-0_x64 on Windows 10
Firebird-4.0.0.1876-0_x64 on Windows 10

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Full dump with Firebird-3.0.5.33220-0_x64_pdb debug build: https://www.dropbox.com/s/aijoghosu1m958u/firebird.exe.11728.7z?dl=0

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Mark, please test fix done in master.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done with caveats

Test Details: Confirmed crash on WI-V3.0.5.33220, WI-T4.0.0.1871 - but only when run MERGE statements with parameters from Python. NO crash when run it from ISQL.

No crash on 4.0.0.1881, but message "No SQLDA for input values provided" will raise for any number of input parameters: 2 or 3.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Pavel, you can have a crash in isql if you previously run "set sqlda_display on;".

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> you can have a crash in isql if you previously run "set sqlda_display on;".

I've tried - no success.

recreate table t (i int not null primary key, j int);
commit;

set sqlda_display on;
set term ^;
execute block as
declare s varchar(4000);
begin
s = '
merge into t
using (select 1 x from rdb$database) on 1 = 1
when matched then
update set j = ?
when matched and i = ? then
delete
when not matched then
insert (i, j) values (1, ?)
';
execute statement ( s ) (2, 1);
end
^

INPUT message field count: 0

OUTPUT message field count: 0
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-Wrong number of parameters (expected 3, got 2)
-At block line: 14, col: 5
After line 7 in file c6280.sql
set term ^;

show version;
ISQL Version: WI-T4.0.0.1871 Firebird 4.0 Beta 1
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-T4.0.0.1871 Firebird 4.0 Beta 1"
Firebird/Windows/AMD/Intel/x64 (remote server), version "WI-T4.0.0.1871 Firebird 4.0 Beta 1/tcp (Image-PC1)/P16:C"
Firebird/Windows/AMD/Intel/x64 (remote interface), version "WI-T4.0.0.1871 Firebird 4.0 Beta 1/tcp (Image-PC1)/P16:C"
on disk structure version 13.0

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

I'm reopening this issue because it will also need to be backported to 3.0.6.

Tested it on the 4.0.0.1881 x64 build from Appveyor and verified it works, and I can execute the merge statement, and it behaves as expected.

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

@pavel you can reproduce it in ISQL using:

set sqlda_display on;
merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete
when not matched then insert (i, j) values (1, ?);

C:\DevSoft\Firebird\Firebird-3.0.5.33220-0_x64>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect 'localhost:c:/data/db/fb3testdatabase.fdb' user sysdba password 'masterkey';
Database: 'localhost:c:/data/db/fb3testdatabase.fdb', User: SYSDBA
SQL> set sqlda_display on;
SQL> merge into t using (select 1 x from rdb$database) on 1 = 1
CON> when matched then update set j = ?
CON> when matched and i = ? then delete
CON> when not matched then insert (i, j) values (1, ?);
Statement failed, SQLSTATE = 08006
Error reading data from the connection.
SQL>

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Yes, this way really produces crash. Update state will be corrected now.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Reopened [ 4 ] => Reopened [ 4 ]

QA Status: Done with caveats => Done successfully

Test Details: Confirmed crash on WI-V3.0.5.33220, WI-T4.0.0.1871 - but only when run MERGE statements with parameters from Python. NO crash when run it from ISQL.

No crash on 4.0.0.1881, but message "No SQLDA for input values provided" will raise for any number of input parameters: 2 or 3.

=>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Reopened [ 4 ] => Reopened [ 4 ]

Test Details: ::: NB ::: Ticket must be closed by Mark (he reopened it).

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.6 [ 10889 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Tested with 3.0.6.33288 works ok.

@pavel: I can't close as I don't have those rights.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

Test Details: ::: NB ::: Ticket must be closed by Mark (he reopened it). =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

security: Developers [ 10012 ] =>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment