Update with Dapper using dynamic column name

Question

I need to do an update but the column name is dynamic.

Code Snippet:

using (var cn = Connection)
{
    var sql = @"UPDATE Teste
        SET @columnName = @parameter,
        DT_PROCESSAMENTO = @vDtProcessamento                                        
        WHERE ID = @ID";

    var resultado = cn.Execute(sql, new
    {
        columnName,
        parameter,
        ID
    });
}

Is it possible to pass the column name as a parameter? This code is what I did, but it does not work. No exceptions but the update does not work.

Why are you trying to do this? Usually there is a better way to approach any problem where dynamic sql is the proposed answer — Milney, Feb 22 '17 at 13:47
There is a routine in my code that inserts in the database the column name, the type and the value. I need to update another column with this values. — Nathália Neves, Feb 22 '17 at 14:16
Okay but what I am trying to say is that this is usually a bad idea... if you are designing a system and not working with an already created one - you should seriously consider re-thinking your design as this (as you have noticed) can be very hard to work with... For example, are users entering any of this data? If so you will be very vulnerable to SQL Injection attacks — Milney, Feb 22 '17 at 14:42
I agree with you. But this system already exists, Im not the one who designed, so I need to make it work. But I put this issue on the table. — Nathália Neves, Feb 23 '17 at 19:42

score 4 · Answer 1 · answered Feb 22 '17 at 13:36

4

You should use String.Format:

var columnName = "Name";
var sql = String.Format(@"UPDATE Teste
                 SET {0} = @parameter                                       
                 WHERE ID = @ID", columnName);

But here you can get SQL injection.

So it's better to check that column name is really column name in your table.

answered Feb 22 '17 at 13:36

teo van kot

12,350
10
38
70

1

For the sake of SQL injection any string parameter can be hijacked, the query in this case don't even needs to be dynamic. While SQL inject is a real concern I hope no real developer will get a column name by user input – jean Feb 22 '17 at 13:56
1

@jean No real developer would use this pattern in the first place :S – Milney Feb 22 '17 at 14:42

score 3 · Accepted Answer · edited Feb 22 '17 at 13:44

3

No you cannot do that that way because the column name cannot be a variable. To do it you do need dynamic SQL like this:

using (var cn = Connection)
{
       var sql = $@"UPDATE Teste
                    SET {columnName} = @parameter,
                    DT_PROCESSAMENTO = @vDtProcessamento                                        
                    WHERE ID = @ID";

        var resultado = cn.Execute(sql, new
        {
                     parameter,
                     ID
        });
}

In the above snippet code you can combine @ to use linebreaks inside the string and $ to insert variables in the string - it's a bit clearer and shorter than using String.Format.

I already use something like this with dapper for a few specific scenarios.

edited Feb 22 '17 at 13:44

Bridge

29,818
9
60
82

answered Feb 22 '17 at 13:37

jean

4,159
4
31
52

@NatháliaNeves *disponha* =) – jean Feb 22 '17 at 14:00
1

using the $ at the start wouldn't work for me. I'm stuck using an older version of c# so had to use string format instead! – Alex Rapso Jul 11 '17 at 08:13
1

Is this vulnerable to SQL injection? What if the value of columnName ends in `; DROP TABLE etc` – TomDane Feb 08 '20 at 01:57
@TomDane **YES!** it's that's why I rarely use it and when I do `columnName` is fed internally by my own business layer, just to be sure invalid/bad names are not allowed, I can never use it by a direct user input – jean Feb 10 '20 at 11:22
Depending on the data, this could be s aimple filter method to strip any special characters: https://stackoverflow.com/a/1120248/3276634 – Lion Mar 05 '21 at 14:53

score 0 · Answer 3 · answered Feb 22 '17 at 13:54

As noted, to do this you need to dynamically construct your SQL, either in SQL then use EXEC(), or in your C# with string.Format() or StringBuilder. Just be aware that by doing this, you will identify yourself as a savage in the eyes of some people! You have no guarantee that users of your method will provide a valid column name. You have to run your program to see what SQL it generates, and if it runs. You have no type safety around the input parameter. There must be a better way. disclaimer: I wrote QueryFirst

Update with Dapper using dynamic column name

3 Answers3