Description:

• The incident involved unexpected behavior when inserting strings using Always Encrypted with Secure Enclaves in Azure SQL DB.

• Unexpected insert results when using Always Encrypted.

• Subsequent inserts into encrypted columns fail after initial successful inserts.

• Issue in details at code level:
  The buildPreparedStrings method compares the last prepared statement with the current one to be executed. If both statements are identical, it signals that calling sp_prepexec is unnecessary. Otherwise, sp_prepexec will be invoked later in the driver's execution flow. However, multiple calls to buildPreparedStrings overwrite the preparedTypeDefinitions variable, which stores the current prepared statement. As a result, repeated invocations can lead to the loss of the original statement, affecting the logic that determines whether sp_prepexec should be called with updated data types and lengths.

• Resolution details:
  Within the buildPreparedStrings method, the preparedTypeDefinitions variable is cached in preparedTypeDefinitionsPrev to prevent it from being overwritten. If isAEv2() is enabled, renewDefinition is false, and isInternalEncryptionQuery is true, then the driver caches the preparedTypeDefinitions value into preparedTypeDefinitionsPrev. In subsequent calls to buildPreparedStrings, if isAEv2() is still enabled, renewDefinition is true, and isInternalEncryptionQuery is false, the driver restores preparedTypeDefinitions from the cached preparedTypeDefinitionsPrev. This caching mechanism prevents the loss of the original statement and ensures the correct signal is returned regarding whether to invoke sp_prepexec.

Testing

• Configure the SQL database with encryption enabled and create a table with column-level encryption. Below manual test method works well.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
try (Connection conn = DriverManager.getConnection(url)) {
    try (PreparedStatement pstmt = conn.prepareStatement("insert into t1 values(?)")) {
        String data2 ="a"; 
        pstmt.setString(1, data2);
        pstmt.executeUpdate();
        String data3 =  data2 + "aa";
        pstmt.setString(1, data3);
        pstmt.executeUpdate();
        String data4 =  data3 + "aaa";
        pstmt.setString(1, data4);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();  
    }
} 

• Mocked the above use case via Mockito framework in the test case testBuildPreparedStringsForVaryLength()