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
)
User contributions licensed under CC BY-SA 3.0