在要求输入邮箱的文本域,请填写真实的邮件地址。非真实邮件地址,将收不到回复信息。

ADO.NET基础参数化SQL查询

DotNet 清风 200℃ 0评论

参数化查询(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查询

喜欢 (0)or分享 (0)
支付宝扫码打赏 微信打赏
发表我的评论
取消评论

CAPTCHA Image
Reload Image
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址