Insert NULL into always encrypted column is not working

[steff-lorenz]

Environment

• Python: 3.9.16
• pyodbc: 4.0.39
• OS: Win 11
• DB: SQL Server 16, Always Encrypted
• driver: ODBC Driver 18 for SQL Server

Issue

I am using pyodbc to write into an always encrypted database.

If I want to write a NULL value into an encrypted column it is not working:

conn = pyodbc.connect("DSN=SQLXXXX;"
                      "database=DBNAME;"
                      "Column Encryption Setting=Enabled;"
                      )
cursor = conn.cursor()

query = """INSERT INTO test_table (designation, label) VALUES (?, ?)"""

cursor.execute(query, ('Test', None))

Both columns are encrypted.
The error message I get for the column I am trying to insert NULL/None is:

pyodbc.DataError: ('22018', "[22018] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Operand type clash: varchar(1) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'XX', column_encryption_key_database_name = 'DBNAME') collation_name = 'Latin1_General_BIN2' is incompatible with nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'XX', column_encryption_key_database_name = 'DBNAME') (206) (SQLExecDirectW); [22018] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

It is working fine if I execute a similar parameterized statement in the SQL Management Studio and insert a NULL.

Is this a bug or am I doing something wrong? It seems like it is encrypting the None to a varchar(1) string. How can i prevent this?

[gordthompson]

Try using

cursor.setinputsizes([None, (pyodbc.SQL_WVARCHAR, 50, 0)])

before you call .execute()

[steff-lorenz]

Thank you very much. This solved my problem.

However, I observed another odd behaviour. This works for the given example query. But if my query looks like this, I get the same error message again:

query = """IF NOT EXISTS
(SELECT designation FROM test_table WHERE designation = ?)
BEGIN 
INSERT INTO test_table (designation, label) VALUES (?, ?)
END"""

Do you have any idea why that might be?

[v-chojas]

You need to set the type for the appropriate parameter(s). See the documentation for setinputsizes.

[gordthompson]

In that case you would need to use an extra None to modify the third parameter instead of the second

cursor.setinputsizes([None, None, (pyodbc.SQL_WVARCHAR, 50, 0)])