参数化查询(Parameterized Query )是指在设计与数据库链接并访问数据时,在需要填入数值或数据的地方,使用参数 (Parameter) 来给值,这个方法目前已被视为最有效可预防SQL注入攻击 (SQL Injection) 的攻击手法的防御方式。下面将重点总结下Parameter构建的几种常用方法。
说起参数化查询当然最主要的就是如何构造所谓的参数:比如,我们登陆时需要密码和用户名,一般我们会这样写sql语句,select count(*) from username where names=@names and passwd=@passwd,为了防止sql注入,我们该如何构建@names和@passwd两个参数呢,下面提供7种(其实大部分原理都是一样,只不过代码表现形式不一样,以此仅作对比,方便使用)构建参数的方法,根据不同的情况选用合适的方法即可:
通用数据库连接字符串: private static string constr = “Password=111111;Persist Security Info=True;User ID=sa;Initial Catalog=news;Data Source=.”;
方法一
SqlConnection conn = new SqlConnection(constr);
string strs = "select count(id) from username where names=@names and passwd=@passwd";
conn.Open();
SqlCommand cmd = new SqlCommand(strs,conn);
cmd.Parameters.Add("@names", SqlDbType.Varchar);
cmd.Parameters.Add("@passwd", SqlDbType.Varchar);
cmd.Parameters["@names"].Value = TextBox1.Text;
cmd.Parameters["@passwd"].Value = TextBox2.Text;
int num=(int)cmd.ExecuteScalar();
if (num > 0)
{
conn.Close();
Response.Write("<script type="text/javascript">alert("成功");</script>");
} else
{
Response.Write("<script type="text/javascript">alert("失败");</script>");
}
方法二
SqlConnection conn = new SqlConnection(constr);
string strs = "select count(id) from username where names=@names and passwd=@passwd";
conn.Open();
SqlCommand cmd = new SqlCommand(strs,conn);
cmd.Parameters.Add(new SqlParameter("@names",TextBox1.Text));
cmd.Parameters.Add(new SqlParameter("@passwd", TextBox2.Text));
int num=(int)cmd.ExecuteScalar();
if (num > 0)
{
conn.Close();
Response.Write("<script type="text/javascript">alert("成功");</script>");
} else
{
Response.Write("<script type="text/javascript">alert("失败");</script>");
}
方法三
SqlConnection conn = new SqlConnection(constr);
string strs = "select count(id) from username where names=@names and passwd=@passwd";
conn.Open();
SqlCommand cmd = new SqlCommand(strs,conn);
cmd.Parameters.Add("@names",SqlDbType.Varchar).Value=TextBox1.Text;
cmd.Parameters.Add("@passwd", SqlDbType.Varchar).Value = TextBox2.Text;
int num=(int)cmd.ExecuteScalar();
if (num > 0)
{
conn.Close();
Response.Write("<script type="text/javascript">alert("成功");</script>");
} else
{
Response.Write("<script type="text/javascript">alert("失败");</script>");
}
方法四
SqlConnection conn = new SqlConnection(constr);
string strs = "select count(id) from username where names=@names and passwd=@passwd";
conn.Open();
SqlCommand cmd = new SqlCommand(strs,conn);
cmd.Parameters.AddWithValue("@names",TextBox1.Text);
cmd.Parameters.AddWithValue("@passwd", TextBox2.Text);
int num=(int)cmd.ExecuteScalar();
if (num > 0)
{
conn.Close();
Response.Write("<script type="text/javascript">alert("成功");</script>");
} else
{
Response.Write("<script type="text/javascript">alert("失败");</script>");
}
方法五
SqlConnection conn = new SqlConnection(constr);
string strs = "select count(id) from username where names=@names and passwd=@passwd";
conn.Open();
SqlCommand cmd = new SqlCommand(strs,conn);
SqlParameter pm1 = new SqlParameter("@names",SqlDbType.Varchar);
pm1.Value = TextBox1.Text;
SqlParameter pm2 = new SqlParameter("@passwd",SqlDbType.Varchar);
pm2.Value = TextBox2.Text;
cmd.Parameters.Add(pm1);
cmd.Parameters.Add(pm2);
int num=(int)cmd.ExecuteScalar();
if (num > 0)
{
conn.Close();
Response.Write("<script type="text/javascript">alert("成功");</script>");
} else
{
Response.Write("<script type="text/javascript">alert("失败");</script>");
}
方法六
SqlConnection conn = new SqlConnection(constr);
string strs = "select count(id) from username where names=@names and passwd=@passwd";
conn.Open();
SqlCommand cmd = new SqlCommand(strs,conn);
SqlParameter[] SqlPame=new SqlParameter[2];
SqlPame[0] = new SqlParameter("@names",SqlDbType.Varchar);
SqlPame[0].Value = TextBox1.Text;
SqlPame[1] = new SqlParameter("@passwd", SqlDbType.Varchar);
SqlPame[1].Value = TextBox2.Text;
foreach(SqlParameter p in SqlPame)
{
cmd.Parameters.Add(p);
}
int num=(int)cmd.ExecuteScalar();
if (num > 0)
{
conn.Close();
Response.Write("<script type="text/javascript">alert("成功");</script>");
} else
{
Response.Write("<script type="text/javascript">alert("失败");</script>");
}
方法七
此方法在2.0以后的版本中已经遗弃了。在后续的版本中移除了,所以在高版本中使用会显示“已过时”。不过仍可使用,但不建议使用。
SqlConnection conn = new SqlConnection(constr);
string strs = "select count(id) from username where names=@names and passwd=@passwd";
conn.Open();
SqlCommand cmd = new SqlCommand(strs,conn);
cmd.Parameters.Add("@names",TextBox1.Text);
cmd.Parameters.Add("@passwd",TextBox2.Text);
int num=(int)cmd.ExecuteScalar();
if (num > 0)
{
conn.Close();
Response.Write("<script type="text/javascript">alert("成功");</script>");
} else
{
Response.Write("<script type="text/javascript">alert("失败");</script>");
}
转载请注明:清风亦平凡 » ADO.NET基础参数化SQL查询