Schema, table and column name parametrization in SqlQueryRaw

[aleksvujic]

We are using .NET 7, EF Core 7 and Npgsql 7 with PostgreSQL database. We use the database first approach with data annotations - we make the changes in the database and then scaffold it to get C# database models.

We have the following database model for fit.issue_reference table:

[Table("issue_reference", Schema = "fit")]
public partial class IssueReference
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("original_issue_id")]
    public int OriginalIssueId { get; set; }

    [Column("referenced_issue_id")]
    public int ReferencedIssueId { get; set; }

    [Column("reference_type")]
    public int ReferenceType { get; set; }

    [ForeignKey("OriginalIssueId")]
    [InverseProperty("IssueReferenceOriginalIssue")]
    public virtual Issue OriginalIssue { get; set; }

    [ForeignKey("ReferencedIssueId")]
    [InverseProperty("IssueReferenceReferencedIssue")]
    public virtual Issue ReferencedIssue { get; set; }
}

We would like to query the database from our service layer using WITH RECURSIVE SQL CTE statement, which is currently not (yet) supported in EF Core (#26486).

The code that we have currently, uses our own methods which use reflection to read data from the C# database model:

1. Reflector.GetTableColumnName<IssueReference>(x => x.OriginalIssueId) method gets [Column] attribute of the property and return its Name.
2. Reflector.GetSchemaAndTableNameFromDBModel<IssueReference>() method retrieves [Table] attribute and returns schema.table string.

private bool ExistsPathBetween(int currentIssueId, int otherIssueId)
{
  return _fitDbContext.Database
    .SqlQueryRaw<bool>(@"
      WITH RECURSIVE cte AS (
        (SELECT
          @originalIssueIdColumn, @referencedIssueIdColumn
        FROM
          @schemaAndTable
        WHERE
          @referencedIssueIdColumn = @currentIssueId)
    
        UNION ALL
    
        (SELECT
          ir.@originalIssueIdColumn, ir.@referencedIssueIdColumn
        FROM
          @schemaAndTable AS ir
        INNER JOIN cte ON cte.@originalIssueIdColumn = ir.@referencedIssueIdColumn)
      )
      SELECT EXISTS (
        SELECT 1
        FROM cte
        WHERE @originalIssueIdColumn = @otherIssueId
      ) AS ""Value""",

      new NpgsqlParameter("@schemaAndTable", Reflector.GetSchemaAndTableNameFromDBModel<IssueReference>()),
      new NpgsqlParameter("@originalIssueIdColumn", Reflector.GetTableColumnName<IssueReference>(x => x.OriginalIssueId)),
      new NpgsqlParameter("@referencedIssueIdColumn", Reflector.GetTableColumnName<IssueReference>(x => x.ReferencedIssueId)),
      new NpgsqlParameter("@currentIssueId", currentIssueId),
      new NpgsqlParameter("@otherIssueId", otherIssueId)
    )
    .SingleOrDefault();
}

However, when we execute the method, we get the following exception:

Exception thrown: 'Npgsql.PostgresException' in Npgsql.dll
Exception thrown: 'Npgsql.PostgresException' in Npgsql.dll
Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll
Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll
Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll
Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll
Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll
Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (436ms) [Parameters=[@schemaAndTable='fit.issue_reference' (Nullable = false), @originalIssueIdColumn='original_issue_id' (Nullable = false), @referencedIssueIdColumn='referenced_issue_id' (Nullable = false), @currentIssueId='5193', @otherIssueId='2802'], CommandType='Text', CommandTimeout='30']
SELECT t."Value"
FROM (

                        WITH RECURSIVE cte AS (
                            (SELECT
                                @originalIssueIdColumn, @referencedIssueIdColumn
                            FROM
                                @schemaAndTable
                            WHERE
                                @referencedIssueIdColumn = @currentIssueId)
                    
                            UNION ALL
                    
                            (SELECT
                                ir.@originalIssueIdColumn, ir.@referencedIssueIdColumn
                            FROM
                                @schemaAndTable AS ir
                            INNER JOIN cte ON cte.@originalIssueIdColumn = ir.@referencedIssueIdColumn)
                        )
                        SELECT EXISTS (
                            SELECT 1
                            FROM cte
                            WHERE @originalIssueIdColumn = @otherIssueId
                        ) AS "Value"
) AS t
LIMIT 2
Exception thrown: 'Npgsql.PostgresException' in Microsoft.EntityFrameworkCore.Relational.dll
'iisexpress.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\7.0.5\System.Diagnostics.StackTrace.dll'. 
Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'Application.Data.Models.Fit.FitDbContext'.
Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "$3"

POSITION: 221
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|234_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "$3"
    Position: 221
    File: scan.l
    Line: 1123
    Routine: scanner_yyerror

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "$3"

POSITION: 221
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|234_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "$3"
    Position: 221
    File: scan.l
    Line: 1123
    Routine: scanner_yyerror
Exception thrown: 'Npgsql.PostgresException' in Microsoft.EntityFrameworkCore.Relational.dll

I assume that the exception occurs because databases do not allow parameterizing column names (source). We would like to avoid hard-coding schema, table and column names as much as possible and use reflection to retrieve these values from scaffolded C# database models instead. Therefore, if we ever rename table or any of its columns, our code won't break because it would read updated attribute values from the newly-scaffolded C# model. What are our options for allowing parametrization of schema, table and column names?

[roji]

Databases indeed don't support parameterizing column names. But you can still interpolate them directly into the SQL, i.e. just do {columnName} instead of a parameter placeholder such as @originalIssueIdColumn. Of course, be mindful of SQL injection/interference, e.g. when your column name contains odd characters; you can use the provider's ISqlGenerationHelper.DelimitIdentifier to apply the same quoting/escaping that EF itself uses internally (you can get the service via context.GetService<ISqlGenerationHelper>()).

[aleksvujic]

Thank you. Does EF Core offer any helper functions for sanitizing user input to prevent SQL injection attacks or is this done by the database alone after it receives the SQL query? For example, I would like to sanitize {columnName} value before I send it to the database as part of the raw SQL statement (SqlQueryRaw).

[roji]

@aleksvujic sanitizing definitely isn't (and cannot be) done by the database - the developer is the one interpolating an arbitrary SQL fragment into their larger SQL string, so it's their responsibility to make sure that this fragment is safe.

EF does provider a helper function - that's the ISqlGenerationHelper.DelimitIdentifier function I referred to above.