我想利用WEB页输入,
根据WEB输入的参数,动态生成相应的SQL代码,然后执行。
问题是现在的代码不能够抵抗SQL注入,
09年2月的程序员P92页里说的PreparedStatement方法,
让我感觉有点心动,结果一看才发现。。人是JAVA的。。.NET我找了好久没找到类似的。
希望有同问者,或者是已解决者给个想法。
using (SqlConnection connection = new SqlConnection(connectionString))
{ connection.Open();
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = connection; sqlcom.CommandText = "use xsdate"; sqlcom.ExecuteNonQuery(); string sqlstring = "create table " + txtTableName.Text + "(t1 int,t2 int)";
sqlcom.CommandText = sqlstring;
sqlcom.ExecuteNonQuery();
// Do work here.
根据WEB输入的参数,动态生成相应的SQL代码,然后执行。
问题是现在的代码不能够抵抗SQL注入,
09年2月的程序员P92页里说的PreparedStatement方法,
让我感觉有点心动,结果一看才发现。。人是JAVA的。。.NET我找了好久没找到类似的。
希望有同问者,或者是已解决者给个想法。
using (SqlConnection connection = new SqlConnection(connectionString))
{ connection.Open();
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = connection; sqlcom.CommandText = "use xsdate"; sqlcom.ExecuteNonQuery(); string sqlstring = "create table " + txtTableName.Text + "(t1 int,t2 int)";
sqlcom.CommandText = sqlstring;
sqlcom.ExecuteNonQuery();
// Do work here.
using (SqlConnection connection = new SqlConnection(connectionString))
{ connection.Open();
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = connection;
sqlcom.CommandText = "select * from Employees where Country=@country and City=@city";
sqlcom.Parameters.AddWithValue("@country","China");
sqlcom.Parameters.AddWithValue("@city", "Beijing");
sqlcom.ExecuteNonQuery();
}
sqlCommand.Paremeters conn is an instance of the System.Data.IDbConnection interface
// creating a plain text command object
IDbCommand cmdPlainText = conn.CreateCommand();
cmdPlainText.CommandType = System.Data.CommandType.Text;
cmdPlainText.CommandText = "SELECT * FROM titles"; // creating a plain text command object using user input
// the resulting statement will be
// "SELECT * FROM titles WHERE title_id=myTitleID"
IDbCommand cmdUserInputText = conn.CreateCommand();
cmdUserInputText.CommandType= System.Data.CommandType.Text;
cmdUserInputText.CommandText = "SELECT * FROM titles WHERE
title_id=@title_id"; /* note the previous line could have been:
cmdUserInputText.CommandText =
"SELECT * FROM titles WHERE title_id='" + "myTitleID" + "'"
and have the same effect on the database,
but that would expose a security risk */
IDbDataParameter userParam = cmdUserInputText.CreateParameter();
userParam.ParameterName = "@title_id";
userParam.Value = "myTitleID";
userParam.DbType = System.Data.DbType.String;
cmdUserInputText.Parameters.Add(userParam); /* creating a command to call the stored procedure
"spMyStoredProcsName" which has a single parameter "@spParam". */
IDbCommand cmdStoredProc = comm.CreateCommand();
cmdStoredProc.CommandType = System.Data.CommandType.StoredProcedure;
cmdStoredProc.CommandText = "spMyStoredProcsName";
IDbDataParameter spParam = cmdStoredProc.CreateParameter();
spParam.ParameterName = "@spParam";
spParam.Value = "spParamValue";
spParam.DbType = System.Data.DbType.String;
cmdStoredProc.Parameters.Add(spParam);
它们的值可以在SqlCommand 的Parameters集合添加
错误说明:'@newTableName' 附近有语法错误。
代码如下:
using (SqlConnection connection = new SqlConnection(connectionString))
{ connection.Open();
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = connection;
sqlcom.CommandText = "use xsdate";
sqlcom.ExecuteNonQuery(); sqlcom.CommandType = System.Data.CommandType.Text;
sqlcom.CommandText = "create table @newTableName(t1 int,t2 int)";
IDbDataParameter sqlparameter = sqlcom.CreateParameter();
sqlparameter.ParameterName = "@newTableName";
sqlparameter.Value = "abcde";
sqlcom.Parameters.Add(sqlparameter);
sqlcom.ExecuteNonQuery();
// Do work here.
}
SqlConnection conn = new SqlConnection(connstring);
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.Connection.Open();
comm.CommandText = "select * from zhang where name=@name";
SqlParameter par = new SqlParameter("@name", SqlDbType.VarChar);
par.Value = "zhangsan";
comm.Parameters.Add(par);
其中 表名 不能使用参数
这恐怕是由SqlServer决定的