EF Core/Npgsql Parameterize an entire where clause

Question

I'm attempting to use Entity Framework Core with Npgsql to perform a raw SQL query where the WHERE clause part is pulled in from the database, with the actual 'value' supplied by a user. As a concrete example:

This part of the SQL is static

SELECT TITLE as TitleField,
       GEOM as GeomField,
       GEOM_X as XField,
       GEOM_Y as YField
FROM fdw_public."MY_TABLE"

Then an administrator can set a WHERE clause up in the database, for example:

COLUMNNAME LIKE '%{search}%'

or

OTHER_COLUMNNAME = '{search}' AND COLUMNNAME = 'Something Static';

This isn't known at compile time and could be any valid WHERE clause.

The {search} part is a placeholder. I trust the clause coming from the database, this is only controlled by the application owner and if they want to break their own table, that's fine.

The end result is this SQL

SELECT TITLE as TitleField,
       GEOM as GeomField,
       GEOM_X as XField,
       GEOM_Y as YField
FROM fdw_public."MY_TABLE"
WHERE COLUMNNAME LIKE '%{search}%'

The {search} part however is the user input (from a web application), so this needs parameterizing.

The following works, but is obviously very open to SQL Injection

var sql = $@"SELECT TITLE as TitleField,
                    GEOM as GeomField,
                    XFIELD as XField,
                    YFIELD as YField
             FROM fdw_public."MY_TABLE"
             WHERE {searchDefinition.WhereClause.Replace("{search}", searchTerm)}";
            
var dbResults = _context.DatabaseSearchResults.FromSqlRaw(sql).ToList();

So I attempted to use a parameter for the whole where clause

var sql = $@"SELECT TITLE as TitleField,
                    GEOM as GeomField,
                    XFIELD as XField,
                    YFIELD as YField
             FROM fdw_public."MY_TABLE"
             WHERE @whereClause";

        var whereClauseParam = new Npgsql.NpgsqlParameter("@whereClause", searchDefinition.WhereClause.Replace("{search}", searchTerm));
        var dbResults = _context.DatabaseSearchResults.FromSqlRaw(sql,whereClauseParam).ToList();

But this throws the following exception from Npgsql

42804: argument of WHERE must be type boolean, not type text

This does makes sense to me, as it feels wrong paramterizing ann entire clause, but I can't figure out a better way round it.

Ideally I need to parametrize just the search term, like this

var sql = $@"SELECT TITLE as TitleField,
                    GEOM as GeomField,
                    XFIELD as XField,
                    YFIELD as YField
             FROM fdw_public."MY_TABLE"
             WHERE {searchDefinition.WhereClause.Replace("{search}","@p0")}";

            var searchTermParam = new Npgsql.NpgsqlParameter("p0", searchTerm);
            var dbResults = _context.DatabaseSearchResults.FromSqlRaw(sql, searchTermParam).ToList();

But again, this doesn't work, understandably, as it is being interpreted literally.

I feel this may involve a change in how I'm doing this completely, or at worse, falling back to sanitizing the search string using some well known sanitization, but I don't want to do this, I want to use parameters.

Either way, to sum up, the requirement is this

The WHERE clause must come from the database but it CAN be trusted
The WHERE clause from the db can be any valid WHERE with a {search} placeholder, allowing us to do things like wildcarding (the admins are expected to know the data types and whether the value should be wrapped in quotes or not etc.)
The search term must be sanitized

Why don't you let EF generate the query? There's little point in using an ORM if you write the query yourself. And `DatabaseSearchResults` is a very weird name for a domain entity — Panagiotis Kanavos, Jun 09 '21 at 17:06
In any case you can't "parameterize" the entire clause. A parameter, just like C# parameters, is used to pass *values* — Panagiotis Kanavos, Jun 09 '21 at 17:08
This part of the application requires admins to set up the WHERE clause. The rest of the application makes use of EF to generate all necessary queries, but this is one part of the application logic where the SQL itself is controlled from elsewhere. I wasn't expecting to be able to parametrize the whole clause, but that was the concept I was starting at and trying to tease out what the best was of achieving what I need is — Rob Quincey, Jun 10 '21 at 08:00
I also take your point about the name, but in this particular context you'll have to trust me that it does make sense! — Rob Quincey, Jun 10 '21 at 08:45

Guru Stron · Answer 1 · 2021-06-09T17:08:37.050

1

Try replacing "{search}" with "{0}" and pass searchTerm as parameters to FromSqlRaw :


var sql = $@"SELECT TITLE as TitleField,
                    GEOM as GeomField,
                    XFIELD as XField,
                    YFIELD as YField
             FROM fdw_public.""MY_TABLE""
WHERE {searchDefinition.WhereClause.Replace("{search}","{0}")}";

var dbResults = _context.DatabaseSearchResults.FromSqlRaw(sql, searchTerm).ToList();

edited Jun 09 '21 at 17:08

answered Jun 09 '21 at 16:48

Guru Stron

102,774
10
95
132

This *almost* works perfectly. My only issue is when the where clause contains things like wildcarding e.g. `COLUMNNAME LIKE '%{search}%'`. Replacing just the {search} term doesn't work as its wrapped in single quotes and the wildcard operator. By removing the quotes and adding the '%' into the parameter, it works as expected. I'm sure I can make some adjustments to handle this or force admins to not include quotes/put the wildcard characters within the placeholder, unless you know of a better way? – Rob Quincey Jun 10 '21 at 08:42
@RobQuincey TBH I don't have better solution. Maybe something can be done with [`eval`](https://stackoverflow.com/a/36909941/2501279) but not sure. – Guru Stron Jun 10 '21 at 09:23

EF Core/Npgsql Parameterize an entire where clause

1 Answers1