c＃tablename as sql PROCEDURE [英] c# tablename as sql PROCEDURE

问题描述

我在button1点击事件中有这个代码

I have this code in button1 click event

if (cn.State == ConnectionState.Closed) cn.Open();
            cm.Connection = cn;
            foreach (ListViewItem item in listView1.Items)
            {
                item.Selected = true;

            }
            ListViewItem lvi2 = listView1.SelectedItems[0];
            string tableName = lvi2.SubItems[5].Text;
            string sql = "UPDATE " + tableName + " SET [itmcount] = [itmcount] - @itmcount1 WHERE [itmcode] = @itmcode1 AND [itmcount] IS NOT NULL";
            cm.CommandText = sql;
            foreach (ListViewItem item in listView1.Items)
            {
                SqlParameter par_ItmCount = new SqlParameter("@itmcount1", SqlDbType.Int);
                par_ItmCount.Value = int.Parse(item.SubItems[3].Text);
                cm.Parameters.Add(par_ItmCount);

                SqlParameter par_ItmCode = new SqlParameter("@itmcode1", SqlDbType.NVarChar);
                par_ItmCode.Value = item.Text;
                cm.Parameters.Add(par_ItmCode);
                {
                    cm.ExecuteNonQuery();
                    
                }
                
            }

因为你可以看到我的表名不是参数如何将此代码更改为sql存储PROCEDURE以防止sql注入？

as u can see my tablename is not a Parameter so how to change this code to sql stored PROCEDURE to prevent sql injection ?

推荐答案

有办法做到这一点但我不明白为什么你需要多个表具有相同的结构。



您可以使用 sp_executesql [ ^ ]您的存储过程应如下所示 -



查看这些文章并尝试自己编写存储过程，如果你遇到任何问题，请回到这里询问你的尝试。



使用sp_executesql [ ^ ]

Sp_executesql - TSQL教程 [ ^ ]



希望，它帮助：）

There are ways to do this but I don't understand why you need multiple tables with same structure.

You can achieve such requirement by using sp_executesql[^] Your stored procedure should look something like following-

Check these articles and try writing the stored proc yourself and if you face any problem, come back here and ask with what you have tried.

Using sp_executesql[^]
Sp_executesql - TSQL Tutorial[^]

Hope, it helps :)

将ListView数据作为Table参数发送并在SQL上更新它

http://www.c-sharpcorner.com/UploadFile/4d9083/insert-and-update-in-sql-using-user-defined-table-type-and-x/ [<一个HREF =http://www.c-sharpcorner.com/UploadFile/4d9 083 / insert-and-update-in-sql-using-user-defined-table-type-and-x /target =_ blanktitle =New Window> ^ ]

Send your ListView data as a Table parameter and update it on SQL
Please follow the link for how to create User Defined Table in SQL
http://www.c-sharpcorner.com/UploadFile/4d9083/insert-and-update-in-sql-using-user-defined-table-type-and-x/[^]

这看起来像一个糟糕的数据库设计，但如果你必须这样做，你需要正确验证表名，并使用 sp_executesql [ ^ ]执行查询：

This looks like a poor database design, but if you have to do it, you need to properly validate the table name, and use sp_executesql[^] to execute the query:

CREATE PROC dbo.usp_UpdateItemCount
(
    @TableName    sysname,
    @ItmCode1     nvarchar(50), -- TODO: Set the correct size here
    @ItmCount1    int
)
As
BEGIN
DECLARE @RealTableName sysname;
DECLARE @Statement nvarchar(max);
    
    SET NOCOUNT ON;
    
    SELECT
        @RealTableName = name
    FROM
        sys.tables
    WHERE
        name = @TableName
    ;
    
    If @@ROWCOUNT = 0 RAISERROR('Table "%s" was not found.', 16, 1, @TableName);
    
    SET @Statement = N'UPDATE ' + QUOTENAME(@RealTableName) 
    + N' SET [itmcount] = [itmcount] - @itmcount1'
    + N' WHERE [itmcode] = @itmcode1 AND [itmcount] IS NOT NULL';
    
    EXEC sp_executesql @Statement, 
        -- TODO: Set the correct size for this parameter as well
        N'@ItmCode1 nvarchar(50), @ItmCount1 int',
        @ItmCode1 = @ItmCode1,
        @ItmCount1 = @ItmCount1
    ;
END

这篇关于c＃tablename as sql PROCEDURE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！