Dapper and SQL injection

Question

In my case, I could not use parameterized queries for some parameters such as filter because the filter is a long string(not a single parameter), so I'm finding a solution for this. I have the following code snippet from my query string. I have tried putting "@filter" but It seems that dapper raw the query string first.

For example, I can not set "....Column = @value", because of the filter of my list may change like contain, starts with.....

queryString = @"SELECT 
                    R.RequestId As requestId, 
                    R.Number AS number, 
                    R.CargoReadyDate AS readyDate, 
                    PL.Name AS portIdOfLoading,  
                    PD.Name AS portIdOfDischarge,  
                    R.Status AS status,  
                    R.AllInFreightAmount_Value AS requestAmount , 
                    R.CreatedDate AS requestDate, 
                    R.AllInFreightAmount_Currency as currency 
                FROM 
                    Requests R  
                JOIN
                    [Ports] PL ON R.PortIdOfLoading = PL.PortId 
                JOIN
                    [Ports] PD ON R.PortIdOfDischarge = PD.PortId  
                WHERE 
                    R.ShipperId = @accountId And " + statusFilter + filter + " " +
              @"ORDER BY 
                    R.CreatedDate DESC 
                    OFFSET @pageSize * (@pageNumber - 1) ROWS 
                    FETCH NEXT @pageSize ROWS ONLY ";

var data = _dapperService.QueryDataSet<RequestDto>(queryString,
            new { accountId = query.AccountId, status = query.RequestStatus, pageSize = query.PageSize, pageNumber = query.PageNumber }, CommandType.Text);

return data;

Answer 1

Ultimately, Dapper can't help with anything that happens outside of the actual parameterization step; if you introduce SQL injection holes before handing it to Dapper: yes, you'll have SQL injection holes.

You can still use Dapper with non-trivial queries, though; and you can still parameterize. For example, you could do:

int? userId = /* something, could be null */
string region = /* something, could be null */

var sql = new StringBuilder(@"select ... /* whatever */ where Open = 1");
if (userId != null) sql.Append(" and UserId = @userId");
if (region != null) sql.Append(" and Region = @region");
// ... etc

var data = conn.Query<SomeType>(sql.ToString(), new { userId, region }).AsList();

Here, Dapper will only add the parameters that it actually sees used in the query; so if @userId doesn't appear, it doesn't add that parameter. But crucially: the query is still fully parameterized and has no SQL injection holes.

For more complex scenarios, Dapper also supports dictionary-like parameters, for where you need to add parameters on-demand rather than statically.