C#•17mo ago

Need help with dapper to prevent sql injection on database parameter!

Hello, this is my current endpoint:

c++
        [HttpGet]
        public async Task<ActionResult<List<Workflow>>> GetAllWorkflows(string databaseName)
        {
            using var connection = new SqlConnection(_config.GetConnectionString("Default"));

            var sql = "SELECT [Key],[Code] " +
                $"FROM [{databaseName}].[dbo].[workflowTable] " +
                "ORDER BY [Key] ASC;";

            var workflows = await connection.QueryAsync<Workflow>(sql);

            return Ok(workflows);
        }

c++
        [HttpGet]
        public async Task<ActionResult<List<Workflow>>> GetAllWorkflows(string databaseName)
        {
            using var connection = new SqlConnection(_config.GetConnectionString("Default"));

            var sql = "SELECT [Key],[Code] " +
                $"FROM [{databaseName}].[dbo].[workflowTable] " +
                "ORDER BY [Key] ASC;";

            var workflows = await connection.QueryAsync<Workflow>(sql);

            return Ok(workflows);
        }

Now reading the documentation of dapper they implement a endpoint like this:

c++
var parameters = new { UserName = username, Password = password };
var sql = "SELECT * from users where username = @UserName and password = @Password";
var result = connection.Query(sql, parameters);

c++
var parameters = new { UserName = username, Password = password };
var sql = "SELECT * from users where username = @UserName and password = @Password";
var result = connection.Query(sql, parameters);

How can I implement this on the database parameter from my code? Thanks in advance!

16 Replies

TheRanger•17mo ago

by database variable do you mean databaseName ?

ZilOP•17mo ago

Yes Parameter*

TheRanger•17mo ago

for what i know you select the database from the connection string

ZilOP•17mo ago

Yea i know what you mean but in my case the api supports multiple databases. So in the actual front end a user can choose what database he wants to do certain CRUD operations in

TheRanger•17mo ago

then build up a connection string and set the database's variable in the connection string based on the user's input

ZilOP•17mo ago

So you are saying instead of doing

c++
using var connection = new SqlConnection(_config.GetConnectionString("Default"));

c++
using var connection = new SqlConnection(_config.GetConnectionString("Default"));

Something like

c++
using var connection = new SqlConnection("Server=localhost;Database={databaseName};User Id=...;Password=...;TrustServerCertificate=True");

c++
using var connection = new SqlConnection("Server=localhost;Database={databaseName};User Id=...;Password=...;TrustServerCertificate=True");

TheRanger•17mo ago

the {variable} wont get converted, you need to put the dollar sign before the first quote of the string

ZilOP•17mo ago

yea i know, it was just to get an indication of if this is what u meant

TheRanger•17mo ago

and.. yes

ZilOP•17mo ago

is this safer? does this prevent sql injection ? because we removed the problem while trying to use dapper to prevent sql injection but now it feels like we just moved the parameter to somewhere else without fixing the sql injection ?

TheRanger•17mo ago

well you cant put sql in the sql connection

ZilOP•17mo ago

true Okay that worked but I feel like declaring the connection string for every end point like this bad practice?

c++
[HttpGet]
        public async Task<ActionResult<List<Workflow>>> GetAllWorkflows(string databaseName)
        {
            var connectionString = $"Server=localhost;Database={databaseName};User Id=*;Password=*;TrustServerCertificate=True";
            using var connection = new SqlConnection(connectionString);

            var sql = "SELECT [Key],[Code] " +
                $"FROM [workflowTable] " +
                "ORDER BY [Key] ASC;";

            var workflows = await connection.QueryAsync<Workflow>(sql);

            return Ok(workflows);
        }

c++
[HttpGet]
        public async Task<ActionResult<List<Workflow>>> GetAllWorkflows(string databaseName)
        {
            var connectionString = $"Server=localhost;Database={databaseName};User Id=*;Password=*;TrustServerCertificate=True";
            using var connection = new SqlConnection(connectionString);

            var sql = "SELECT [Key],[Code] " +
                $"FROM [workflowTable] " +
                "ORDER BY [Key] ASC;";

            var workflows = await connection.QueryAsync<Workflow>(sql);

            return Ok(workflows);
        }

Is there a way I can maybe put a parameter inside the appsettings.json somehow so i can do something like

c++
using var connection = new SqlConnection(_config.GetConnectionString("Default", databaseName));

c++
using var connection = new SqlConnection(_config.GetConnectionString("Default", databaseName));

Anu6is•17mo ago

you could make multiple connection string configs and set the key as the db name

  "ConnectionStrings": {
    "db_A": "...",
    "db_B": "..."
  }

  "ConnectionStrings": {
    "db_A": "...",
    "db_B": "..."
  }

using var connection = new SqlConnection(_config.GetConnectionString(databaseName));

using var connection = new SqlConnection(_config.GetConnectionString(databaseName));

ZilOP•17mo ago

Right, is that something that is a good solution for a problem like this ?

Anu6is•17mo ago

never really had to do multiple databases from a single endpoint where the user specifies the db but.. at the very least it's not a bad solution... you are retrieving the connection string the same way you did before, but instead of hard coding default, the user passes the value they could of course pass a value that does not exist as a db and you should account for that

ZilOP•17mo ago

true ill keep that in mind, thanks! also thank you @ℝ ill close the post

Gaming

Programming

Need help with dapper to prevent sql injection on database parameter!