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
Comments
Modified by: @mrotteveeldescription: 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 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 { => 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 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 { |
Modified by: @mrotteveel |
Modified by: @mrotteveeldescription: 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 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 { => 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 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 { 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 |
Modified by: @mrotteveeldescription: 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 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 { => 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 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 { |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: @asfernandes When you say about potential to crash, do you have any evidence or it's just speculation? |
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. |
Modified by: @mrotteveelVersion: 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 |
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 |
Commented by: @asfernandes Mark, please test fix done in master. |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 Beta 2 [ 10888 ] |
Modified by: @pavel-zotovstatus: 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. |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: @asfernandes Pavel, you can have a crash in isql if you previously run "set sqlda_display on;". |
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); set sqlda_display on; INPUT message field count: 0 OUTPUT message field count: 0 show version;
|
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. |
Commented by: @mrotteveel @pavel you can reproduce it in ISQL using: set sqlda_display on; C:\DevSoft\Firebird\Firebird-3.0.5.33220-0_x64>isql |
Commented by: @pavel-zotov Yes, this way really produces crash. Update state will be corrected now. |
Modified by: @pavel-zotovstatus: 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. => |
Modified by: @pavel-zotovstatus: Reopened [ 4 ] => Reopened [ 4 ] Test Details: ::: NB ::: Ticket must be closed by Mark (he reopened it). |
Modified by: @asfernandesstatus: Reopened [ 4 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0.6 [ 10889 ] |
Commented by: @mrotteveel Tested with 3.0.6.33288 works ok. @pavel: I can't close as I don't have those rights. |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] Test Details: ::: NB ::: Ticket must be closed by Mark (he reopened it). => |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @dyemanovsecurity: Developers [ 10012 ] => |
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
The text was updated successfully, but these errors were encountered: