Interpolation with Venflow

If you never heard about string interpolation you should check the official docs.

Writing SQL can be a real pain especially while writing in by hand and keeping it injection safe. However Venflow tries to help you with all of that, especially by providing a simple way to write injection safe SQL. All API's which allow for SQL with parameters also have a counterpart called something along the lines of *Interpolation*. They accept a FormattableString which allow for interpolated strings. Lets take a look at how this would like with a query, that queries all blogs with a similar one to the name provided by the user.

var name = Console.ReadLine(); // The name of the blogs to find with a similar name

var blogs = await database.Blogs.QueryInterpolatedBatch($@"SELECT * FROM ""Blogs"" WHERE ""Name"" LIKE {name}")
                                .QueryAsync();

Warning

This way of injecting parameters is totally safe, however you need to be very careful to always choose the interpolation methods while doing this.

Extract interpolated SQL into variables

If your SQL statement is a little bit larger than usual you might want to extract your string to a local variable or similar. However you need to be careful while choosing the variable type. Your habits might want to tell you to choose a string or var however you need to make sure that you explicitly set the variable type to FormattableString. Otherwise the string interpolation happens inline and no parameterizing by Venflow will happen. Down below you will see a simple example of how that would look like.

FormattableString sql = $@"SELECT * FROM ""Blogs"" WHERE ""Name"" LIKE {Console.ReadLine()}";

var blogs = await database.Blogs.QueryInterpolatedBatch(sql)
                                .QueryAsync();

Supported interpolation types

At the current state you can use all types Npgsql supports, as well as a few neat features Venflow implemented such as the support for ulong, ulong? and all types that implement IList<T>, this includes types such as arrays and all collections that implement it. Lets look at its behaviour with an example.

var blogIds = new int[3] { 1, 2, 3 };

FormattableString sql = $@"SELECT * FROM ""Blogs"" WHERE ""Id"" IN ({blogIds})";

var blogs = await database.Blogs.QueryInterpolatedBatch(sql)
                                .QueryAsync();

This will query all blogs with the ids 1, 2 and 3. The above written SQL statement will be converted to the following:

-- @p* will contain their repective values e.g. (1, 2, 3)
SELECT * FROM "Blogs" WHERE "Id" IN (@p1, @p2, @p3)

Of course the numbers usually would be parametrized, however for the sake of this example it contains the values directly.

Dynamic SQL

There might be situations in which you need to dynamically generate SQL with parameters, in which case the common StringBuilder isn't sufficient enough. Venflow provides you with the FormattableSqlStringBuilder class which acts like a StringBuilder, however it provides methods, which allow for interpolated SQL. Lets take a look at this with a more practical example.

public Task<List<Blogs>> GetBlogsAsync(string[]? names)
{
    var stringBuilder = new FormattableSqlStringBuilder();
	
    stringBuilder.Append(@"SELECT * FROM ""Blogs""");
    
    if(names is not null &&
	   names.Length > 0)
    {
        stringBuilder.Append(@" WHERE ""Name"" IN (");
        stringBuilder.AppendParameter(names);
        stringBuilder.AppendInterpolated(@$") AND LENGTH(""Name"") > {5}");
    }
    
    return database.Blogs.QueryInterpolatedBatch(stringBuilder).QueryAsync();
}

Obviously the query shown above is not too useful, however if names would be provided, it would only query those and additionally they would need to be longer than 5 characters.