我该如何重新编写一个SQL查询作为参数化查询? [英] How do I re-write a SQL query as a parameterized query?
本文介绍了我该如何重新编写一个SQL查询作为参数化查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我听说,我可以通过使用参数化查询prevent SQL注入攻击,但我不知道该怎么写。
我怎么会写下面的参数化查询?
的SqlConnection CON =新的SqlConnection(
数据源=+ globalvariables.hosttxt +,+ globalvariables.porttxt +\\ SQLEX $ P $的pSS; +
数据库=公顷; +
持续安全信息= FALSE; +
UID ='+ globalvariables.user +; +
PWD ='+ globalvariables.psw +');
查询字符串=SELECT DISTINCT公顷,从应用程序的WHERE 1 + 1 = 2;
如果(comboBox1.Text!=)
{
查询+ =和FIRMA ='+ comboBox1.Text +';
}
如果(comboBox2.Text!=)
{
查询+ =AND型='+ comboBox2.Text +';
}
如果(comboBox3.Text!=)
{
查询+ =和farve ='+ comboBox3.Text +';
}
SqlCommand的mySqlCmd = con.CreateCommand();
mySqlCmd.CommandText =查询;
con.Open();
...
解决方案
您需要使用参数,而不仅仅是串联起来您的SQL:
使用(SqlConnection的CON =新的SqlConnection( - 你的连接-string--))
使用(CMD的SqlCommand =新的SqlCommand(CON))
{
查询字符串=SELECT DISTINCT公顷,从应用程序的WHERE 1 + 1 = 2;
如果(comboBox1.Text!=)
{
//增加一个前pression与参数
查询+ =和FIRMA = @值1;
//添加参数和值给SqlCommand
cmd.Parameters.Add(@值1,SqlDbType.VarChar,100)。价值= comboBox1.Text;
}
....等等所有的各种参数要添加
cmd.CommandText =查询;
con.Open();
使用(SqlDataReader的读卡器= cmd.ExecuteReader())
{
而(reader.Read())
{
//做一些与读者-read值
}
reader.Close();
}
con.Close();
}
I have heard that I can prevent SQL injection attacks by using parameterized queries, but I do not know how to write them.
How would I write the following as a parameterized query?
SqlConnection con = new SqlConnection(
"Data Source=" + globalvariables.hosttxt + "," + globalvariables.porttxt + "\\SQLEXPRESS;" +
"Database=ha;" +
"Persist Security Info=false;" +
"UID='" + globalvariables.user + "';" +
"PWD='" + globalvariables.psw + "'");
string query = "SELECT distinct ha FROM app WHERE 1+1=2";
if (comboBox1.Text != "")
{
query += " AND firma = '" + comboBox1.Text + "'";
}
if (comboBox2.Text != "")
{
query += " AND type = '" + comboBox2.Text + "'";
}
if (comboBox3.Text != "")
{
query += " AND farve = '" + comboBox3.Text + "'";
}
SqlCommand mySqlCmd = con.CreateCommand();
mySqlCmd.CommandText = query;
con.Open();
…
解决方案
You need to use parameters instead of just concatenating together your SQL:
using (SqlConnection con = new SqlConnection(--your-connection-string--))
using (SqlCommand cmd = new SqlCommand(con))
{
string query = "SELECT distinct ha FROM app WHERE 1+1=2";
if (comboBox1.Text != "")
{
// add an expression with a parameter
query += " AND firma = @value1 ";
// add parameter and value to the SqlCommand
cmd.Parameters.Add("@value1", SqlDbType.VarChar, 100).Value = comboBox1.Text;
}
.... and so on for all the various parameters you want to add
cmd.CommandText = query;
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
// do something with reader -read values
}
reader.Close();
}
con.Close();
}
这篇关于我该如何重新编写一个SQL查询作为参数化查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文