search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.53k stars 3.13k forks source link

Migrations don't use parameters for data operations #20734

Open mindlink opened 4 years ago

mindlink commented 4 years ago

When inserting data during a migration (using the migration builder API) into a table that has an SQL Always Encrypted column, the operation throws an SqlException (see below).

When using a DbContext to insert data after the migration it works as expected, but the migration API doesn't seem to follow the same mechanism and fails.

Steps to reproduce

See https://github.com/mindlink/bug-efcore-encrypted-migration for a minimal sample project that demonstrates this.

Stack trace:

Microsoft.Data.SqlClient.SqlException HResult=0x80131904 Message=Operand type clash: nvarchar is incompatible with nvarchar(4000) 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 = 'EFCoreEncryption') Source=Core Microsoft SqlClient Data Provider StackTrace: at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod) at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult) at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult) at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization) at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.d13.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.d13.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.d13.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.d1.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.d1.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.d1.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.d1.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.d14.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.GetResult() at EncryptionMigrationBug.Program.d3.MoveNext() in C:\Users\terryl\Luke\src\github\bug-efcore-encrypted-migration\EncryptionMigrationBug\Program.cs:line 117 at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at EncryptionMigrationBug.Program.d3.MoveNext() in C:\Users\terryl\Luke\src\github\bug-efcore-encrypted-migration\EncryptionMigrationBug\Program.cs:line 117 at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.GetResult() at EncryptionMigrationBug.Program.

d0.MoveNext() in C:\Users\terryl\Luke\src\github\bug-efcore-encrypted-migration\EncryptionMigrationBug\Program.cs:line 50 at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at EncryptionMigrationBug.Program.
d0.MoveNext() in C:\Users\terryl\Luke\src\github\bug-efcore-encrypted-migration\EncryptionMigrationBug\Program.cs:line 41 at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.GetResult() at EncryptionMigrationBug.Program.
(String[] args)

This exception was originally thrown at this call stack: Microsoft.Data.SqlClient.SqlConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action) Microsoft.Data.SqlClient.SqlInternalConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action) Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Microsoft.Data.SqlClient.TdsParserStateObject, bool, bool) Microsoft.Data.SqlClient.TdsParser.TryRun(Microsoft.Data.SqlClient.RunBehavior, Microsoft.Data.SqlClient.SqlCommand, Microsoft.Data.SqlClient.SqlDataReader, Microsoft.Data.SqlClient.BulkCopySimpleResultSet, Microsoft.Data.SqlClient.TdsParserStateObject, out bool) Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(System.IAsyncResult, bool, string) Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(System.IAsyncResult) Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(System.IAsyncResult) System.Threading.Tasks.TaskFactory.FromAsyncCoreLogic(System.IAsyncResult, System.Func<System.IAsyncResult, TResult>, System.Action, System.Threading.Tasks.Task, bool) System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(System.Threading.Tasks.Task) ... [Call Stack Truncated]

Further technical details

EF Core version: 3.1.3 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: NET Core 3.1 Operating system: Windows 10 Enterprise IDE: Visual Studio 2019 16.3

AndriySvyryd commented 3 years ago

This is the SQL generated by migrations:

INSERT INTO [Messages] ([MessageId], [GroupId], [SenderId], [Content], [Timestamp], [Context])
VALUES (N'dde5f8b1-980c-45c1-9921-ff087bf4e52e', N'390fca16-47bb-46f8-9719-e42e843c7f11', N'3aad58d1-5cfb-4007-99af-8edae40e1e0a', N'This is content inserted as part of a migration', '2020-08-27T00:25:37.0870009+00:00', N'Some context');

It also throws the same exception when executed from SSMS because the values are hardcoded instead of being supplied in parameters.

When the migration is executed (as opposed to generating a script) parameters should be used instead of hardcoding the values.

@mindlink This is currently a limitation in how migrations were designed. We recommend populating tables with encrypted columns using SaveChanges. This way you can also avoid having the unencrypted data in the source code.