C＃alter table和添加列编程ASP.Net和放大器; SQL服务器 [英] C# Alter Table and Add a column programmatically ASP.Net & SQL Server

问题描述

我一直试图在ASP.NET编程添加一列修改SQL Server中的表。

I have been trying to add a column programmatically in ASP.NET to modify the tables in SQL Server.

请参阅以下code：

 string suppliernotxt = supplieridlist[1].ToString();
 //SqlCommand cmd2 = new SqlCommand("ALTER TABLE [ProductNormalDB] ADD suppliernotxt nvarchar(20) NULL", con);
 SqlCommand cmd2 = new SqlCommand("ALTER TABLE ProductNormalDB ADD @supplierlist nvarchar(20) NULL", con);
 cmd2.Parameters.AddWithValue("@supplierlist", suppliernotxt);
 //cmd2.Parameters.AddWithValue("@supplierlist", suppliernotxt.ToString());
 //cmd2.Parameters["@supplierlist"].Value = supplieridlist[x];
 cmd2.ExecuteNonQuery();

supplieridlist 是取得所有列名添加到SQL Server数据库的数组。出于某种原因，参数化方法不能正常工作，并显示以下错误：

supplieridlist is an array that acquires all the column names to add into the SQL Server database. For some reason the parametrized method is not working and shows the following error:

附近有语法错误@supplierlist。

Incorrect syntax near '@supplierlist'.

的基本思想是有一个用户从一个复选框选择的供应商的名称，根据供应商的阵列将创建为前的供应商名称的选择数目。如果我们选择了3供应商，阵列将节省Supplier1，Supplier2， Supplier3，然后的SqlCommand 应该更改表并添加新列。

The basic idea is to have a user select from a check box the name of the suppliers, based on the selected number of suppliers the array will create the supplier names for ex. if we selected 3 suppliers, the array will save "Supplier1", "Supplier2", "Supplier3" and then the SqlCommand is supposed to alter the table and add the new columns.

推荐答案

您不能使用参数来恩preSS列的名字。结果
参数只能用于前preSS值的WHERE子句或INSERT或UPDATE语句。

You cannot use parameters to express the name of columns.
Parameters could only be used to express values for WHERE clause or for INSERT or UPDATE statements.

您可以使用字符串连接您的​​查询的文本，将字符串值传递给存储过程或使用某种形式的动态SQL。

You could use string concatenation for your query text, passing the string value to a stored procedure or use some form of dynamic sql.

请非常小心使用这些类型的方法，因为如果你不保持传达到$ C $值绝对控制c。你将接触到SQL注入攻击。

Please be very carefull with these kind of approaches because if you don't keep absolute control on the values passed to your code you will be exposed to Sql Injection.

添加如动态SQL执行的例子，但仍容易受到SQL注入

Adding as an example of Dynamic SQL execution, but still vulnerable to SQL Injection

string suppliernotxt = supplieridlist[1].ToString();
string execSQL = "DECLARE @sup nvarchar(15); " + 
                 "SET @sup = '" + suppliernotxt + "'; " +
                 "EXEC ('ALTER TABLE ProductNormalDB  ADD ' + @sup + ' nvarchar(20) NULL')"
SqlCommand cmd2 = new SqlCommand(execSQL, con);
cmd2.ExecuteNonQuery();    

正如你可以看到，即使使用动态SQL没有什么是prevent SQL注入攻击通过 suppliernotxt 变量传递

修改作为从@RBarryYoung下面的评论中解释说，在这种情况下动态SQL的SQL注入问题，良好的改善可能是的 QUOTENAME功能以获得统一code字符串与周围的需要的分隔符输入字符串

EDIT As explained in the comments below from @RBarryYoung, a good improvement on the SQL Injection problem for this case of dynamic sql could be the usage of the QUOTENAME function to obtain an Unicode string with the required delimiters around the input string

string execSQL = "DECLARE @sup nvarchar(15); " + 
                 "SET @sup = QUOTENAME('" + suppliernotxt + "'); " +
                 "EXEC ('ALTER TABLE ProductNormalDB  ADD ' + @sup + ' nvarchar(20) NULL')"

这篇关于C＃alter table和添加列编程ASP.Net和放大器; SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！