Invalid input syntax for type uuid- parameter not bound

0

I have an application in .NET Core 3.1. It is based on PostgreSQL database. I use EF Core 3.1.4 for virtually every DB operation. My provider is Npgsql.EntityFrameworkCore.PostgreSQL 3.1.4. Unfortunately, I have a rather advanced query, which would be really complicated to execute in EF, so I decided to use DbSet.FromRawSql.

Query is supposed to fetch entity type and all parent types hierarchy. Unfortunately, each time I get an PostgresException:

Npgsql.PostgresException (0x80004005): 22P02: invalid input syntax for type uuid: "@myEntityTypeId"
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   ...

It seems as if the parameter was not bound in any way. Query itself is correct - it works if I use simple string concatenation without any binding. However, I want to secure it against SQL Injection, so I need some way to sanitize this parameter. I've read thoroughly EF Core documentation on Raw Sql: Raw SQL Queries and tried every method which is proposed there - Formatted string (i.e. passing value with {0}), bind with '@' key, passing SqlParameter (NpgsqlParameter). Unfortunately, only simple concatenation works.

Is there any simple mistake? How can I pass this parameter to get it bound in query?

At this moment I have the following code

public class ReadOnlyRepository<TEntity> : IReadOnlyRepository<TEntity> where TEntity : Entity
{
    private readonly DbContext _context;

    public ReadOnlyRepository(DbContext context)
    {
        _context = context;
    }

    public IQueryable<TEntity> Set()
    {
        return _context.Set<TEntity>().AsNoTracking();
    }

    public IQueryable<TEntity> FromRawSql(string sql, params object[] sqlParameters)
    {
        return _context.Set<TEntity>()
            .FromSqlRaw(sql, sqlParameters)
            .AsNoTracking();
    }
}
public class GetMyEntityListQueryHandler : IQueryHandler<GetMyEntityListQuery, GetMyEntityListResponse>
{
    private readonly IReadOnlyRepository<MyEntityType> _myEntityTypeRepository;

    public GetMyEntityListQueryHandler(IReadOnlyRepository<MyEntityType> myEntityTypeRepository)
    {
        _myEntityTypeRepository = myEntityTypeRepository;
    }

    // omitted for brevity

    private ICollection<GetMyEntityListQuery.MyEntityTypeItem> GetMyEntityTypes(Guid myEntityTypeId)
    {
        return _myEntityTypeRepository.FromRawSql(
            @$"WITH RECURSIVE type_hierarchy AS (
                SELECT id, name, parent_type_id FROM my_entity_types
                WHERE id = '@myEntityTypeId'

                UNION ALL

                SELECT mt.id, mt.name, mt.parent_type_id FROM my_entity_types mt
                INNER JOIN type_hierarchy h
                ON mt.id = h.parent_type_id
            )
            SELECT * FROM type_hierarchy", myEntityTypeId)
            .Select(x => new GetMyEntityListQuery.MyEntityTypeItem(x.Id, x.Name))
            .ToList();
    }
}

My SQL Table have the following structure

TABLE my_entity_types
(
    id uuid NOT NULL,
    name text COLLATE pg_catalog."default",
    parent_type_id uuid
)
c#
postgresql
.net-core
entity-framework-core
npgsql
asked on Stack Overflow Oct 12, 2020 by Vagrant326

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0