Sanitize user given column name in SQL Server via C#

ykejflvf 于 2023-03-07 发布在 SQL Server

关注(0)|答案(2)|浏览(131)

In C# I have the following function:

public bool Set(int id, string columnName, object value)
{
    /// <summary>
    /// This function sets the given column name to the given value for the given id.
    /// Returns true if the operation was successful, false otherwise.
    /// </summary>

    string query = "UPDATE " + TableName + " SET " + columnName + " = @value WHERE id = @id";

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            connection.Open();

            command.Parameters.AddWithValue("@id", id);

    ....

I cannot set columnName as an parameter, that does not work. However the column name should be a user input. How can I make this sanitize?

I tried making columnName an variable like this @columnName and setting it later, however, this does not work because I think SQL Server interprets this as a string later, not a column object.

sql-server

来源：https://stackoverflow.com/questions/75626122/sanitize-user-given-column-name-in-sql-server-via-c-sharp

2条答案

按热度按时间

dpiehjr41#

I felt like writing something for this anyway, because I don't write a lot of C#. I do, however, want to repeat that everything in the comments is still true; just don't do this.

That being said, what you can do if pass parameters for the object names, the id and the value, and then create a dynamic SQL statement, checking against the system objects. This results in some (awful abomination) like this:

String sql = @"DECLARE @SQL nvarchar(MAX);
               SELECT @SQL = N'UPDATE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' SET ' + QUOTENAME(c.name) + N' = @Value WHERE id = @Id;'
               FROM sys.schemas s
                    JOIN sys.tables t ON s.schema_id = t.schema_id
                    JOIN sys.columns c ON t.object_id = c.object_id
               WHERE s.name = @Schema
                 AND t.name = @Table
                 AND c.name = @Column;
               IF @SQL IS NOT NULL
                   EXEC sys.sp_executesql @SQL, N'@Id int, @Value nvarchar(4000)', @Id, @Value;
               ELSE
                   THROW 56725, N'Object not found for update.',16;";
try
{   
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        
        command.Parameters.Add("@Schema",SqlDbType.NVarChar,128).Value = schemaName;
        command.Parameters.Add("@Table",SqlDbType.NVarChar,128).Value = tableName;
        command.Parameters.Add("@Column",SqlDbType.NVarChar,128).Value = columnName;
        command.Parameters.Add("@Id",SqlDbType.Int).Value = idValue;
        command.Parameters.Add("@Value",SqlDbType.NVarChar,128).Value = updateValue;
        command.ExecuteNonQuery();
        Console.WriteLine("Update Complete.");

    }   
}
catch (SqlException e)
{
    //Raise appropriate exception, I just dump errors to the console
    Console.WriteLine(e.ToString());
    Console.WriteLine("Update Failed.");
}

赞(0）回复(0）举报 2023-03-07

jyztefdp2#

A simple way to "sanitize" your input is to try to find the table name and column names in the system tables and do it using a parametrized query.

SELECT *
FROM SYS.SYSOBJECTS SO
JOIN SYS.SYSCOLUMNS SC ON SC.id = SO.id
WHERE SO.XTYPE = N'U'
AND SO.NAME = N'TABLE NAME'
AND SC.NAME = N'COLUMN NAME'

disclaimer: I'm not a big fan of doing things the way you are doing nor doing stuff that needs sanitized user inputs. You need to question those things first.

赞(0）回复(0）举报 2023-03-07

我来回答

Sanitize user given column name in SQL Server via C#

2条答案

相关问题

热门标签

最新问答