Error when using Always Encrypted and Update record

[WillyBilly90]

I got this error when updating a record when updating a record with columns which are encrypted.

Operand type clash: varchar(14) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'WCCASHDESK') collation_name = 'Latin1_General_CI_AS' is incompatible with nvarchar(50) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'WCCASHDESK')

The used sql command is this:

string sql = "UPDATE dbo.Klant SET Voornaam=@Voornaam, Tussenvoegsel=@Tussenvoegsel, Achternaam=@Achternaam, Bedrijfsnaam=@Bedrijfsnaam ,Straat=@Straat ,Huisnummer=@Huisnummer, Postcode=@Postcode, Plaats=@Plaats, Telefoon1=@Telefoon1, Telefoon2=@Telefoon2, Email=@Email WHERE KlantId=@KlantId";

                AceQLCommand command = new AceQLCommand(sql, conn);
                command.Parameters.AddWithValue("@KlantId", klant.KlantId);
                command.Parameters.AddWithValue("@Voornaam", klant.Voornaam);
                command.Parameters.AddWithValue("@Tussenvoegsel", klant.Tussenvoegsel);
                command.Parameters.AddWithValue("@Achternaam", klant.Achternaam);
                command.Parameters.AddWithValue("@Bedrijfsnaam", klant.Bedrijfsnaam);
                command.Parameters.AddWithValue("@Straat", klant.Straat);
                command.Parameters.AddWithValue("@Huisnummer", klant.Huisnummer);
                command.Parameters.AddWithValue("@Postcode", klant.Postcode);
                command.Parameters.AddWithValue("@Plaats", klant.Plaats);
                command.Parameters.AddWithValue("@Telefoon1", klant.Telefoon1);
                command.Parameters.AddWithValue("@Telefoon2", klant.Telefoon2);
                command.Parameters.AddWithValue("@Email", klant.Email);

                rows = await command.ExecuteNonQueryAsync();

[ndepomereu]

Hi,
Remember all SQL calls are executed in Java with a JDBC Driver on the server side.
Are you sure the JDBC Driver is correctly configured for encryption (JDBC Driver model, version, and parameters) ?

The best would be to do a little Java program on the server that encrypts/decryts a column and make sure all is OK.
Then run same SQL calls from C# using AceQL and check if it's still OK, or report the error.

I will try some tests, but I have no experience using Always Encrypted with Java and a JDBC Driver, it may take some time...

[WillyBilly90]

Hi,

When using SQL instead of AceQL I do this:

public void CreateSqlParameter()
{
  SqlParameter myParameter = new SqlParameter();
  myParameter.ParameterName = "@Voornaam";
  myParameter.DbType = DbType.String;
  myParameter.Direction = ParameterDirection.Output;
  myParameter.Size = 15;
}

When using DbType and Size, then it works.
But these parameters seems not implemented in AceQl.

Always Encrypted columns can now be read with the SQL select statement and are decrypted by Java / Tomcat.
When updating or inserting database rows, which need to be encrypted, you sometimes need to add the dbtype and length.

[ndepomereu]

When using SQL instead of AceQL: all tests must be done in Java, there is no C# involved on server side.

[ndepomereu]

I have found this on the error you reported:
https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/connect/jdbc/using-always-encrypted-with-the-jdbc-driver.md#errors-due-to-passing-plaintext-instead-of-encrypted-values

The whole guide is very complete:
https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/connect/jdbc/using-always-encrypted-with-the-jdbc-driver.md

[WillyBilly90]

It works with Java when I use named parameters, when writing the complete query without parameters the same error as before pops up.

Does AceQL make query's with or without named parameters ?

[ndepomereu]

This is is normal that it works with Java when using named parameters ( aka "?" substitutions).
This is normal that it does not work when writing the complete query without parameters.

It is explained here:
https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/connect/jdbc/using-always-encrypted-with-the-jdbc-driver.md#errors-due-to-passing-plaintext-instead-of-encrypted-values

Yes, AceQL on the server makes all Java/JDBC queries with named parameters if the C# query has at least one parameter.

[WillyBilly90]

Where are the parameters created?

I can't find them. It should be like setstring() what can be changed to setNstring() in java.

[ndepomereu]

Could you please more specific ? Give a lot more details? I don't understand the exact meaning of the question.

Parameters on Java server side are created in ServerStatement by ServerPreparedStatementParametersUtil

Also, can you please send the Java code you mentioned that succeeds with named parameters?
It will help me to understand if something is missing on the server-side, then I can fix it.

[WillyBilly90]

Sorry for the delay.

What should be created is that you can update a record and tell java what kind of field should be used, example: update culumn "Name" with content "Billy" as NVARCHAR(50).

The program now only can update culumn "Name" with content "Billy", but it can not specify the type of column that should be updated.

With Always Encrypted you should specify the columntype for the field else the JDBC tries to update a record with the wrong column type and that will not work .

Is it possible to fix that ?

[ndepomereu]

Hi,
I understand but I please need more specific info.
Please send some code sample using Always Encrypted with JDBC so that I can reproduce the required behavior.

[WillyBilly90]

Hi,

This is an example:

import java.sql.Connection;
import java.sql.PreparedStatement;
import com.w3spoint.util.JDBCUtil;
 
/**
 * This class is used to update a record in DB table
 * using PreparedStatement.
 * @author w3spoint
 */
public class JDBCTest {
	public static void main(String args[]){
		Connection conn = null;
		PreparedStatement preparedStatement = null;
 
		String query = "update USER set " +
		"NAME = ? " + 
		"where ID = ? ";
 
		try{			
			//get connection
			conn = JDBCUtil.getConnection();
 
			//create preparedStatement
			preparedStatement = conn.prepareStatement(query);
 
			//set values
			preparedStatement.setNString(2, "John");     //when you force to update a column with Nvarchar
                        preparedStatement.setString(2, "John");     //when you force to update a column with varchar

			preparedStatement.setInt(1, 65000);
 
			//execute query
			preparedStatement.executeUpdate();
 
			//close connection
			preparedStatement.close();
			conn.close();
 
		      System.out.println("Record updated successfully.");
		}catch(Exception e){
			e.printStackTrace();
		}
	}	
}