C#•9mo ago

✅ How to use Dapper with Query Parameters

I work a lot with SQL databases such as MySQL, SQLite and PSQL and usually I write my Queries like this

// Example written in System.Data.SQLite

using(SQLiteConnection connection = new SQLiteConnection("connect string")) 
{
  connection.Open();
  using(SQLiteCommand command = new SQLiteCommand(connection))
  {
    command.CommandText = "DELETE FROM SomeSensitiveTable WHERE SomeSensitiveData=@myParam";
    command.Parameters.AddWithValue("@myParam", "myText");
    command.ExecuteNonQuery();
  }
  connection.Close();
}

// Example written in System.Data.SQLite

using(SQLiteConnection connection = new SQLiteConnection("connect string")) 
{
  connection.Open();
  using(SQLiteCommand command = new SQLiteCommand(connection))
  {
    command.CommandText = "DELETE FROM SomeSensitiveTable WHERE SomeSensitiveData=@myParam";
    command.Parameters.AddWithValue("@myParam", "myText");
    command.ExecuteNonQuery();
  }
  connection.Close();
}

But using Dapper if i want to create an Object from a SELECT query I need to run connection.Query<ObjectModel>("MyQuery");, is there any way I can use Dapper while keeping my queries secure?

19 Replies

leowest•9mo ago

    var dictionary = new Dictionary<string, object>
    {
        { "@ProductId", 1 }
    };
    var parameters = new DynamicParameters(dictionary);
    var sql = "SELECT * FROM products WHERE ProductId = @ProductId";
    using (var connection = new SqlConnection(connectionString))
    {
        var product = connection.QuerySingle<Product>(sql, parameters);
    }

    var dictionary = new Dictionary<string, object>
    {
        { "@ProductId", 1 }
    };
    var parameters = new DynamicParameters(dictionary);
    var sql = "SELECT * FROM products WHERE ProductId = @ProductId";
    using (var connection = new SqlConnection(connectionString))
    {
        var product = connection.QuerySingle<Product>(sql, parameters);
    }

there is also string parameters https://www.learndapper.com/parameters#dapper-dynamic-parameters and other ways

The Fog from Human ResourcesOP•9mo ago

oh i do have a followup question about Parameters in general

leowest•9mo ago

    var parameters = new { Id = 10 };
    var sql = "DELETE FROM SomeSensitiveTable WHERE SomeSensitiveData=@Id";
    var result = connection.Query(sql, parameters);

    var parameters = new { Id = 10 };
    var sql = "DELETE FROM SomeSensitiveTable WHERE SomeSensitiveData=@Id";
    var result = connection.Query(sql, parameters);

The Fog from Human ResourcesOP•9mo ago

1. Do they prevent SQL Injection things 2. Do they also prevent errors that can happen if my WHERE clause has a target with spaces and such, cause i had massive trouble searching for GUID / UUID format

Jimmacle•9mo ago

they prevent any issues related to formatting and sanitization of the query

The Fog from Human ResourcesOP•9mo ago

alright! Thanks

leowest•9mo ago

1) yes its parametized 2) yes they also have parametized where

Jimmacle•9mo ago

the query and parameters are sent to the server separately so they can't possibly affect how the query executes

The Fog from Human ResourcesOP•9mo ago

I love dapper it wouldve been a shame if it didnt have that :SCgetoutofmyhead:

Jimmacle•9mo ago

not a dapper thing just a SQL thing