SQLServerPreparedStatement uses a wrong handle after server restart - may cause wrong/dangerous statement execution

[sergiuo]

Driver version

10.2.1

SQL Server version

Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Sep 12 2022 15:07:06

Client Operating System

Windows 10

JAVA/JVM version

11.0.10

Table schema

none

Problem description

SQLServerPreparedStatement.retryBasedOnFailedReuseOfCachedHandle is not properly detecting if the handle is still valid.
After DB server restart server handle IDs are reset and the numbering starts from 1. So the handle ID from cache may refer to a completely different statement after server restart. The result is unpredictable and may lead to data loss/damaging.

----------sample code-----------------------
public class ZopaSqlTest {

public static void main(String[] args) throws SQLException, IOException {
    String statement1 = "select SYSTEM_USER where SYSTEM_USER not in (?)";
    String statement2 = "select SYSTEM_USER where SYSTEM_USER not in (?, ?)";
    try(Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=master;sendStringParametersAsUnicode=false;disableStatementPooling=false;statementPoolingCacheSize=999999;encrypt=false", "username", "password")){

        runPrepared2(connection, statement1, "1");
        runPrepared2(connection, statement2, "1", "2");
        runPrepared2(connection, statement1, "1");
        runPrepared2(connection, statement2, "1", "2");
        System.out.println("Restart DB Server and press enter...");
        System.in.read();
        runPrepared2(connection, statement2, "1", "2");
        runPrepared2(connection, statement1, "1");//here the statement2 is executed instead of statement1
    }
}

private static void runPrepared2(Connection connection, String sql, String... params) throws SQLException {
    try(PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
        for (int i = 0; i < params.length; i++) {
            preparedStatement.setString(i + 1, params[i]);
        }
        preparedStatement.execute();
    }
}

}

Expected behavior

After restart the server handle ID should not be used.

Actual behavior

The the server handle ID should is used, a wrong statement is executed and a "missing parameter" exception is thrown.

Error message/stack trace

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The parameterized query '(@p0 varchar(8000),@p1 varchar(8000))select SYSTEM_USER where SY' expects the parameter '@p1', which was not supplied.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7730)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3786)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:515)
at com.edifecs.archival.etl.ZopaSqlTest.runPrepared2(ZopaSqlTest.java:32)
at com.edifecs.archival.etl.ZopaSqlTest.main(ZopaSqlTest.java:23)

Any other details that can be helpful

[Jeffery-Wasty]

Hi @sergiuo,

We'll take a look into this, see if we can reproduce it, and get back to you with our findings.

[lilgreenbird]

hi @sergiuo

I am unable to repro the issue, when the server is restarted I get a "connection reset by peer" when executing the prepared statement. Can you please tell me what you did to repro this?

Also, there's been quite a bit of changes in that area could you please give the latest 11.2.1 release a try and see if you're getting the same results?

[sergiuo]

After sql server restart wait a 10-20 seconds before resuming code execution. I use "java version "11.0.10" 2021-01-19 LTS " but I don't think that matters.

With "11.2.1.jre11" the connection is broken after restart so the issue is not valid (unless there are some options to restore the connection and the statements pools are not reset):
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3876)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:459)

Thanks for looking into it.

[lilgreenbird]

@sergiuo

I can see the issue in 10.2.1, will need to do more investigation to determine what had changed.

[David-Engel]

As a workaround, you can either disable connection resiliency via connectRetryCount=0 or prepared statement caching via statementPoolingCacheSize=0.

[lilgreenbird]

hi @sergiuo

Thank you for reporting the issue. We are currently working on hotfix releases 10.2.2 and 11.2.2 that will include the fix for this issue.

[lilgreenbird]

re-opened for 11.2.2