ADO.NET调用带参数的SQL语句

//获取连接字符串 static string url = ConfigurationManager.ConnectionStrings["SqlServer"].ToString(); //根据学号修改学生信息 static void UpdateByNo(string no,string name,string sex,int age) { using(SqlConnection con = new SqlConnection(url)) { con.Open(); string sql = "UPDATE Student SET name=@name,sex=@sex,age=@age WHERE no=@no"; SqlCommand command = new SqlCommand(sql,con); SqlParameter[] para = new SqlParameter[] { new SqlParameter("@no",no), new SqlParameter("@name",name), new SqlParameter("@sex",sex), new SqlParameter("@age",age) }; command.Parameters.AddRange(para); int result=command.ExecuteNonQuery(); if(result!=0) Console.WriteLine("修改成功！"); else Console.WriteLine("修改失败！"); } } //插入学生信息 static void Insert(string no,string name,string sex,int age) { //获取连接对象 using(SqlConnection con = new SqlConnection(url)) { con.Open(); //string sql = string.Format("INSERT INTO Student VALUES ('{0}','{1}','{2}',{3})",no,name,sex,age); string sql = "INSERT INTO Student VALUES (@no,@name,@sex,@age)"; SqlCommand command = new SqlCommand(sql,con); //int[] arr = {1,2,3}; //int[] arr2 = new int[3]; //int[] arr3 = new int[]{1,2,3}; SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@no",no), new SqlParameter("@name",name), new SqlParameter("@sex",sex), new SqlParameter("@age",age) }; command.Parameters.AddRange(paras); int result=command.ExecuteNonQuery(); if(result!=0) Console.WriteLine("插入成功！"); else Console.WriteLine("插入失败"); } }

 

使用带参的SQL语句好处在于：

1、防止非法参数注入

2、避免单引号问题

 

关于防止非法参数注入问题

static void Login_NonPara(string loginId, string loginPwd) { string url = ConfigurationManager.ConnectionStrings["sql"].ConnectionString; using (SqlConnection con = new SqlConnection(url)) { con.Open(); string sql = string.Format("SELECT * FROM Student WHERE LoginId='{0}' AND LoginPwd='{1}'",loginId,loginPwd); SqlCommand command = new SqlCommand(sql, con); SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LoginId",loginId), new SqlParameter("@LoginPwd",loginPwd) }; command.Parameters.AddRange(paras); using (SqlDataReader dr = command.ExecuteReader()) { if (dr.Read()) { Console.WriteLine("登录成功!"); } else Console.WriteLine("登录失败!"); } } }

若调用此方法：

static void Main(string[] args) { Login_NonPara("Li Jinxiang", "' OR 1=1--"); Console.Read(); }

则会发现即使密码输入错误，也会显示登录成功，这是因为将登录用户名和密码与SQL语句连接后实际上会变为：

SELECT * FROM Student WHERE LoginId='Li   Jinxiang' AND LoginPwd='' OR 1=1--'

我们可以看到WHERE条件子句其实永远为真，这就导致既使不知道用户名也能登录进入系统，为了避免这种情况的发生，我们可以使用带参的SQL语句，系统会对输入的密码是否合法进行校验，一旦发现有类似OR之类的非法字符，则不允许登录。

static void Login_Para(string loginId, string loginPwd) { string url = ConfigurationManager.ConnectionStrings["sql"].ConnectionString; using (SqlConnection con = new SqlConnection(url)) { con.Open(); string sql = "SELECT * FROM Student WHERE LoginId=@LoginId AND LoginPwd=@LoginPwd"; SqlCommand command = new SqlCommand(sql, con); SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LoginId",loginId), new SqlParameter("@LoginPwd",loginPwd) }; command.Parameters.AddRange(paras); using (SqlDataReader dr = command.ExecuteReader()) { if (dr.Read()) { Console.WriteLine("登录成功!"); } else Console.WriteLine("登录失败！"); } } }

则调用方法Login_Para()后，登录结果显示为“登录失败！”：

static void Main(string[] args) { Login_Para("Li Jinxiang", "' OR 1=1--"); Console.Read(); }