Search SQL Server Always Encrypted DateTime with Java

[Crater Void]

I have a SQL Server database with Always Encrypted columns and I want to do a date search.  The following code works with a jdbc PreparedStatement but not mybatis:

Connection conn = db.getConnectionSQL_SERVER();

String sql = "SELECT purchase_date FROM Customers WHERE purchase_date=?";

try {
   SQLServerPreparedStatement ps = (SQLServerPreparedStatement) conn.prepareStatement(sql);
   SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
   java.util.Date pd= formatter.parse("07/04/2019");
   ps.setDateTime(1, new java.sql.Timestamp(pd.getTime()));
   ResultSet rs = ps.executeQuery();
   while(rs.next()) {
         System.out.println(rs.getDate(1));
 } catch (Exception e) {

        e.printStackTrace();

 }
           

In order for the above to work I had to use a SQLServerPreparedStatement and ps.setDateTime.  The Always Encrypted documentation says that if my column is defined as datetime (vs datetime2), I must use this method and not ps.setTimestamp.

My question is what configuration should I use in my MyBatis xml file for this to work?

I have tried:

        <result property="purchaseDate" javaType="java.util.Date" jdbcType="DATE" typeHandler="DateTypeHandler" column="purchase_date"/>

Changing the javaType and jdbType to timestamp did not help.  The error I get with mybatis is:

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The data types datetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Encryption_Key', column_encryption_key_database_name = customerdb) and datetime2(7) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Encryption_Key', column_encryption_key_database_name = customerdb) are incompatible in the equal to operator.

[Tim]

There is no setDateTime handler in mybatis by default. The jdbc api doesn't define that.

You can create a custom typehandler and use it by replacing typeHandler="DateTypeHandler"

I'm not actually sure about the semantic difference between setTimestamp and setDateTime  

It seems a similar discussion is still open on mssql-jdbc github https://github.com/microsoft/mssql-jdbc/issues/443

Are you able to change the schema from datetime to datetime2 as suggested in the linked mssql-jdbc issue?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/77983ff5-f62b-4043-b92e-ba7d03276cd0%40googlegroups.com.

[Crater Void]

On Monday, November 18, 2019 at 4:43:25 PM UTC-7, h3adache wrote:

There is no setDateTime handler in mybatis by default. The jdbc api doesn't define that.

You can create a custom typehandler and use it by replacing typeHandler="DateTypeHandler"

I'm not actually sure about the semantic difference between setTimestamp and setDateTime  

It seems a similar discussion is still open on mssql-jdbc github https://github.com/microsoft/mssql-jdbc/issues/443

Are you able to change the schema from datetime to datetime2 as suggested in the linked mssql-jdbc issue?

Thanks, unfortunately I don't have access to change the schema.

I tried creating a custom type handler for dates but BaseTypeHandler expects PreparedStatement not SQLServerPreparedStatement, and simply casting a PreparedStatement to SQLServerPreparedStatement did not work. 

On Mon, Nov 18, 2019 at 12:31 PM Crater Void <crater...@gmail.com> wrote:

I have a SQL Server database with Always Encrypted columns and I want to do a date search.  The following code works with a jdbc PreparedStatement but not mybatis:

Connection conn = db.getConnectionSQL_SERVER();

String sql = "SELECT purchase_date FROM Customers WHERE purchase_date=?";

try {
   SQLServerPreparedStatement ps = (SQLServerPreparedStatement) conn.prepareStatement(sql);
   SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
   java.util.Date pd= formatter.parse("07/04/2019");
   ps.setDateTime(1, new java.sql.Timestamp(pd.getTime()));
   ResultSet rs = ps.executeQuery();
   while(rs.next()) {
         System.out.println(rs.getDate(1));
 } catch (Exception e) {

        e.printStackTrace();

 }
           

In order for the above to work I had to use a SQLServerPreparedStatement and ps.setDateTime.  The Always Encrypted documentation says that if my column is defined as datetime (vs datetime2), I must use this method and not ps.setTimestamp.

My question is what configuration should I use in my MyBatis xml file for this to work?

I have tried:

        <result property="purchaseDate" javaType="java.util.Date" jdbcType="DATE" typeHandler="DateTypeHandler" column="purchase_date"/>

Changing the javaType and jdbType to timestamp did not help.  The error I get with mybatis is:

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The data types datetime encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Encryption_Key', column_encryption_key_database_name = customerdb) and datetime2(7) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Encryption_Key', column_encryption_key_database_name = customerdb) are incompatible in the equal to operator.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mybati...@googlegroups.com.

[Tim]

Yea that's unfortunate. It doesn't know to create a SQLServerPreparedStatement for you.

Can you create a sample github project?

It might be something you should raise a bug on mssql-jdbc about.

Past experiences they are very quick to respond and helpful.

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/72fd3f2f-4504-4e84-96cc-2210fae18fb9%40googlegroups.com.

[Iwao AVE!]

Hello,

I haven’t tested it with Always Encrypted, but calling unwrap() should work.

public void setNonNullParameter(PreparedStatement ps, int i,
    Date parameter, JdbcType jdbcType) throws SQLException {
  ps.unwrap(SQLServerPreparedStatement.class)
    .setDateTime(i, new java.sql.Timestamp(parameter.getTime());
}

Regards,
Iwao

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.com.

[Crater Void]

Hey, that worked.  Thanks much Iwao!

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/72fd3f2f-4504-4e84-96cc-2210fae18fb9%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mybati...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAPqCCxNbvm1w513n%2BJGmFRzVjQ4sOf3TDHMvr5tMwS7NjSSGrA%40mail.gmail.com.

[Tim “h3adache” Chen]

Neat Iwao! I’ve never had to use that before but that definitely something handy to remember 

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/e09eef70-54eb-493b-b7c5-acf32b7a771c%40googlegroups.com.