Pass parameters to ExecuteSql()

I am trying to pass a table name as a parameter to the ExecuteSql() method.

This is what I have tried:

        var viewName = "search_view";

        using (var db = dbFactory.Open())
        {
            db.ExecuteSql("REFRESH MATERIALIZED VIEW @viewName;", new { viewName });
        }

It doesn't work, it creates exception with the message of:

Npgsql.PostgresException

42601: syntax error at or near "$1"

I enabled logging to try to see what SQL is generated but I think because of the exception the query isn't logged.

The query runs fine when it is all text, what am I doing wrong passing in the table name as a parameter?

Solution

You can only use DB parameters as a replacement for parameters, i.e. you can't use them as a free-text substitution for SQL template generation like you're trying to do.

You would need to use include the viewName in your SQL, e.g:

db.ExecuteSql($"REFRESH MATERIALIZED VIEW {viewName};");

Although if viewName was provided by the user you would need to guard it against possible SQL injection. My recommendation is checking against a white-list of allowed viewNames, e.g:

if (!AllowedViewNames.Contains(viewName))
    throw new Exception("Invalid View");

In OrmLite you can escape a string with GetQuotedValue() API, e.g:

var quotedViewName = db.GetDialectProvider().GetQuotedValue(viewName);

To detect invalid names for symbols like view names you can use a RegEx to only allow valid characters, e.g:

if (!new Regex(@"[^A-Za-z0-9_]").IsMatch(viewName))
    throw new Exception("Invalid View");

Whilst OrmLite's SqlVerifyFragment() extension method lets you detect potentially illegal SQL injection if you wanted to accept an SQL fragment, e.g:

db.ExecuteSql($"SELECT * FROM User WHERE {userSql.SqlVerifyFragment()}");

Where OrmLite will throw an ArgumentException if it detects a potential illegal SQL violation.