ASP.NET Core Identity - illegal variable name/number when updating with UserManager

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Danny Nieruch
Posts: 4
Joined: Thu 12 Jun 2014 10:30

ASP.NET Core Identity - illegal variable name/number when updating with UserManager

Post by Danny Nieruch » Mon 14 Jan 2019 14:49

  • .NET & EF Core 2.2
  • Devart.Data.Oracle.EFCore: 9.6.675


Hi,

I'm using ASP.NET Core Identity with devart dotConnect for Oracle. I'm getting the Oracle exception "ORA-01036: illegal variable name/number" exception when I try to update a user with Core Identity UserManager and I'm not sure how to track down the problem.

I have a custom application user:

Code: Select all

public class ApplicationUser : IdentityUser<long>
{
    [Column("NAME")]
    [StringLength(50)]
    public string FullName { get; set; }
}
And customized the DbContexts OnModelCreating:

Code: Select all

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    [...]
    builder.Entity<ApplicationUser>(userConfig =>
    {
        userConfig.ToTable("USERS");

        userConfig.Ignore(u => u.LockoutEnabled);
        userConfig.Ignore(u => u.PhoneNumber);
        userConfig.Ignore(u => u.PhoneNumberConfirmed);
        userConfig.Ignore(u => u.AccessFailedCount);
        userConfig.Ignore(u => u.LockoutEnd);
        userConfig.Ignore(u => u.TwoFactorEnabled);
        userConfig.Property(u => u.Id).HasColumnName("ID");
        userConfig.Property(u => u.Email).HasColumnName("EMAIL").HasMaxLength(50);
        [...]
Whenever I use the UserManager from ASP.NET Core Identity to update a user (for example .UpdateAsync() or .AddPasswordAsync()), I get the "illegal variable name/number" ORA exception. For example:

Code: Select all

private readonly UserManager<ApplicationUser> _userManager;
[...]
public async void Test()
{
    [...]
    ApplicationUser user = await _userManager.FindByIdAsync(123);
    user.EmailConfirmed = true;
    user.SecurityStamp = Guid.NewGuid().ToString("D");
    [...]
    await _userManager.UpdateAsync(user);
    //await _userManager.AddPasswordAsync() does not work too
}
I've checked Visual Studios output and see the problem:

Code: Select all

Microsoft.EntityFrameworkCore.Database.Command:Error: Failed executing DbCommand (69ms)
[Parameters=[p9='?' (DbType = Decimal), p0='?' (Size = 36), p10='?', p1='?' (Size = 25), p2='?' (DbType = Decimal), p3='?' (Size = 12), p4='?' (Size = 25), p5='?' (Size = 2), p6='?' (Size = 84), p7='?' (Size = 36), p8='?' (Size = 2)], CommandType='Text', CommandTimeout='0']
UPDATE USERS SET CONCURRENCYSTAMP = :p0, EMAIL = :p1, EXTERN = :p2, NAME = :p3, EMAIL_NORMALIZED = :p4, NAME_NORMALIZED = :p5, PASSWORDHASH = :p6, SECURITYSTAMP = :p7, DBNAME = :p8
WHERE ID = :p9 AND CONCURRENCYSTAMP IS NULL
There are 11 parameters generated (p10 seems to be null) but the update query only uses 10 parameters. Of course this causes an ORA "illegal variable name/number" exception. But why?

I've also checked the DB query with Devarts dbMonitor:
Image
Same result... I can see the 11 parameters but I don't know why p10 gets generated (and is NULL).

I can not find the root cause. Is something wrong with my code? Could this be a bug in the Devart .NET Core Package?
How can I find the cause for the not used parameter in the generated query?

Danny Nieruch
Posts: 4
Joined: Thu 12 Jun 2014 10:30

Re: ASP.NET Core Identity - illegal variable name/number when updating with UserManager

Post by Danny Nieruch » Thu 17 Jan 2019 11:45

Okay I found the problem.

The root cause of the problem is the IdentityUsers ConcurrencyStamp property.
The exception "ORA-01036: illegal variable name/number" gets thrown when you try to update a user where the ConcurrencyStamp is NULL.

Updating a user creates a DB query likes this:

Code: Select all

UPDATE USERS SET [...] WHERE ID = :p9 AND CONCURRENCYSTAMP = :p10
The last parameter gets replaced with the current ConcurrenyStamp. When ConcurrenyStamp is null, you get the following query:

Code: Select all

UPDATE USERS SET [...] WHERE ID = :p9 AND CONCURRENCYSTAMP IS NULL
So the CONCURRENCYSTAMP p10 parameter gets generated in the background with a NULL value and is not used by the query, what causes the parameter mismatch.

TL;DR:
CONCURRENCYSTAMP in your IdentityUser table must have a value when users/rows are not created with the Identity UserManager.

Shalex
Site Admin
Posts: 8750
Joined: Thu 14 Aug 2008 12:44

Re: ASP.NET Core Identity - illegal variable name/number when updating with UserManager

Post by Shalex » Thu 07 Feb 2019 12:53

Thank you for your report and described workaround. We have reproduced the issue and will notify you about the results of our investigation.

Shalex
Site Admin
Posts: 8750
Joined: Thu 14 Aug 2008 12:44

Re: ASP.NET Core Identity - illegal variable name/number when updating with UserManager

Post by Shalex » Mon 18 Feb 2019 12:17

The bug with updating a null value of the concurrency check column with some non-null value in EF Core is fixed: viewtopic.php?f=1&t=38378.

Danny Nieruch
Posts: 4
Joined: Thu 12 Jun 2014 10:30

Re: ASP.NET Core Identity - illegal variable name/number when updating with UserManager

Post by Danny Nieruch » Mon 18 Feb 2019 12:42

Thanks for your support/bugfix and the new build!

Post Reply