Pass parameters to ExecuteSql()

Question

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?

mythz · Accepted Answer · 2018-09-01 06:33:58Z

2

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.

edited Sep 1, 2018 at 6:33

answered Sep 1, 2018 at 5:42

mythz

144k31 gold badges253 silver badges400 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guerrilla Over a year ago

Thanks. Is there any public method available to strip sql injection attacks from strings? In this case I am not taking user input for it but I am just curious to know for the future.

mythz Over a year ago

@Guerrilla See my updated answer for different ways to guard against SQL injection from user input.

Collectives™ on Stack Overflow

Pass parameters to ExecuteSql()

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related