Skip to content

Attempting to insert an empty string into an encrypted column throws a SQLServerException #624

@jaymajors

Description

@jaymajors

Driver version or jar name

Tried both mssql-jdbc-6.2.2.jre8.jar & mssql-jdbc-6.3.6.jre8-preview.jar

SQL Server version

SQL Server 2016

Client operating system

Test on Windows 10 & Windows Server 2016

Java/JVM version

Oracle jdk 1.8.0

Table schema

CREATE TABLE [dbo].[Person](
[first_name] varchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[last_name] varchar NULL,
[middle_name] varchar NULL
) ON [PRIMARY]
GO

Problem description

Trying to insert an empty string '' into an encrypted column causes the following exception to be thrown.

com.microsoft.sqlserver.jdbc.SQLServerException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): Data type 0xA7 has an invalid data length or metadata length.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1566)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:554)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:488)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7352)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2724)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:435)
	at com.sample.alwaysencrypted.SQLDatabaseConnection.main(SQLDatabaseConnection.java:25)

Expected behavior and actual behavior

The row is inserted without an exception thrown.

Repro code

import java.sql.*;

public class SQLDatabaseConnection {

	// Connect to your database.
	// Replace server name, username, and password with your credentials
	public static void main(String[] args) {
		String connectionString = "jdbc:sqlserver://localhost:2016;" + "database=db;"
				+ "user=user;" + "password=pass;" + "sendStringParametersAsUnicode=true;columnEncryptionSetting=Enabled;";

		// Declare the JDBC objects.
		Connection connection = null;

		try {
			connection = DriverManager.getConnection(connectionString);			
			
			String insert = "insert into Person values (?, ?, ?)";
			PreparedStatement insertps = connection.prepareStatement(insert);
			insertps.setString(1, "");
			insertps.setString(2, "Smith");
			insertps.setString(3, "George");
		
			insertps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (Exception e) {
				}
		}
	}
}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions