ADO.NET防SQL注入与使用参数增删改查_实用技巧_

private void btLogin_Click(object sender, EventArgs e) { //1-定义连接字符串 string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; //2-定义连接对象，打开连接 SqlConnection conn = new SqlConnection(connStr); conn.Open(); //3-编写sql语句(此处如果用户名密码同时输入' or '1'='1 则可以造成注入) string sql = string.Format("select * from Member where MemberAccount='{0}' and MemberPwd='{1}'" ,this.txtAccount.Text,this.txtPwd.Text); //4-数据适配器抽取信息 SqlDataAdapter adp = new SqlDataAdapter(sql, conn); DataTable dt = new DataTable(); //数据表格 adp.Fill(dt); conn.Close(); if (dt.Rows.Count == 0) MessageBox.Show("用户名或密码错误！"); else MessageBox.Show("登录成功！"); }

if (this.txtAccount.Text.IndexOf("'") >= 0 || this.txtPwd.Text.IndexOf("'") >= 0) { MessageBox.Show("非法登录!"); return; }

private void btLogin_Click(object sender, EventArgs e) { //1-定义连接字符串 string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; //2-定义连接对象，打开连接 SqlConnection conn = new SqlConnection(connStr); conn.Open(); //3-编写sql语句 string sql = string.Format("select * from Member where MemberAccount='{0}'" , this.txtAccount.Text); //4-数据适配器抽取信息 SqlDataAdapter adp = new SqlDataAdapter(sql, conn); DataTable dt = new DataTable(); //数据表格 adp.Fill(dt); conn.Close(); if (dt.Rows.Count == 0) MessageBox.Show("用户名错误！"); else { if (dt.Rows[0]["MemberPwd"].ToString().Equals(this.txtPwd.Text)) MessageBox.Show("登录成功！"); else MessageBox.Show("密码错误！"); } }

private void btLogin_Click(object sender, EventArgs e) { //1-定义连接字符串 //string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; //2-编写连接字符串（sql用户名密码方式连接） string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; //2-定义连接对象，打开连接 SqlConnection conn = new SqlConnection(connStr); conn.Open(); //3-编写sql语句 string sql = "select * from Member where MemberAccount=@MemberAccount and MemberPwd=@MemberPwd"; //4-数据适配器抽取信息 SqlDataAdapter adp = new SqlDataAdapter(sql, conn); adp.SelectCommand.Parameters.Add(new SqlParameter("@MemberAccount", this.txtAccount.Text)); adp.SelectCommand.Parameters.Add(new SqlParameter("@MemberPwd",this.txtPwd.Text)); DataTable dt = new DataTable(); //数据表格 adp.Fill(dt); conn.Close(); if (dt.Rows.Count == 0) MessageBox.Show("用户名或密码错误！"); else MessageBox.Show("登录成功！"); }

//绑定数据的方法 public void BindData() { //1-定义连接字符串 //string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; //2-编写连接字符串（sql用户名密码方式连接） string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; //2-定义连接对象，打开连接 SqlConnection conn = new SqlConnection(connStr); conn.Open(); //3-编写sql语句 string sql = "select * from Member"; //4-数据适配器抽取信息 SqlDataAdapter adp = new SqlDataAdapter(sql, conn); DataTable dt = new DataTable(); //数据表格 adp.Fill(dt); this.dataGridView1.AutoGenerateColumns = false; //自动列取消 this.dataGridView1.DataSource = dt; conn.Close(); } private void FrmSelect_Load(object sender, EventArgs e) { BindData(); }

private void 删除ToolStripMenuItem_Click(object sender, EventArgs e) { DialogResult r = MessageBox.Show("您确定要删除吗?", "****系统", MessageBoxButtons.YesNo); if (r == System.Windows.Forms.DialogResult.No) { return; } int memId = int.Parse(this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString()); string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; SqlConnection conn = new SqlConnection(connStr); conn.Open(); string sql = "delete from Member where MemberId = " + memId; SqlCommand cmd = new SqlCommand(sql, conn); int rowCount = cmd.ExecuteNonQuery(); conn.Close(); if (rowCount == 1) MessageBox.Show("删除成功!"); else MessageBox.Show("删除失败!"); BindData(); }

private void btAdd_Click(object sender, EventArgs e) { //1-编写连接字符串（windows方式连接） //string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; //2-编写连接字符串（sql用户名密码方式连接） string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; //2-创建连接对象，打开数据库连接 SqlConnection conn = new SqlConnection(connStr); conn.Open(); //3-编写sql语句 string sql = string.Format("insert into Member(MemberAccount,MemberPwd,MemberName,MemberPhone) values(@MemberAccount,@MemberPwd,@MemberName,@MemberPhone)" , this.txtAccount.Text, this.txtPwd.Text, this.txtNickName.Text, this.txtPhone.Text); //4-定义执行命令的对象执行命令 SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@MemberAccount", this.txtAccount.Text)); cmd.Parameters.Add(new SqlParameter("@MemberPwd", this.txtPwd.Text)); cmd.Parameters.Add(new SqlParameter("@MemberName", this.txtNickName.Text)); cmd.Parameters.Add(new SqlParameter("@MemberPhone", this.txtPhone.Text)); int rowCount = cmd.ExecuteNonQuery(); conn.Close(); if (rowCount == 1) MessageBox.Show("添加成功!"); else MessageBox.Show("添加失败!"); //刷新查询窗体数据并关闭当前窗体 ((FrmSelect)this.Owner).BindData(); this.Close(); }

public int MemId { get; set; } //接受外部传递过来的会员编号 //绑定会员详情到文本框 private void BindDetail() { //1-定义连接字符串 string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; //2-定义连接对象，打开连接 SqlConnection conn = new SqlConnection(connStr); conn.Open(); //3-编写sql语句 string sql = "select * from Member where MemberId = " + this.MemId; //-抽取数据 SqlDataAdapter adp = new SqlDataAdapter(sql, conn); DataTable dt = new DataTable(); adp.Fill(dt); conn.Close(); this.txtAccount.Text = dt.Rows[0]["MemberAccount"].ToString(); this.txtPwd.Text = dt.Rows[0]["MemberPwd"].ToString(); this.txtNickName.Text = dt.Rows[0]["MemberName"].ToString(); this.txtPhone.Text = dt.Rows[0]["MemberPhone"].ToString(); } private void FrmEdit_Load(object sender, EventArgs e) { BindDetail(); } private void btUpdate_Click(object sender, EventArgs e) { string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; SqlConnection conn = new SqlConnection(connStr); conn.Open(); string sql = "update Member set MemberAccount=@MemberAccount,MemberPwd=@MemberPwd,MemberName=@MemberName,MemberPhone=@MemberPhone where MemberId=@MemberId"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@MemberAccount", this.txtAccount.Text)); cmd.Parameters.Add(new SqlParameter("@MemberPwd", this.txtPwd.Text)); cmd.Parameters.Add(new SqlParameter("@MemberName", this.txtNickName.Text)); cmd.Parameters.Add(new SqlParameter("@MemberPhone", this.txtPhone.Text)); cmd.Parameters.Add(new SqlParameter("@MemberId", this.MemId)); int rowCount = cmd.ExecuteNonQuery(); conn.Close(); if (rowCount == 1) MessageBox.Show("修改成功!"); else MessageBox.Show("修改失败!"); //刷新查询窗体数据并关闭当前窗体 ((FrmSelect)this.Owner).BindData(); this.Close(); }

private void btAdd_Click(object sender, EventArgs e) { FrmAdd frm = new FrmAdd(); frm.Owner = this; frm.Show(); } private void btEdit_Click(object sender, EventArgs e) { if (this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString().Equals("")) { MessageBox.Show("请正确选择!"); return; } int memId = int.Parse(this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString()); FrmEdit frm = new FrmEdit(); frm.MemId = memId; frm.Owner = this; frm.Show(); }

class DBHelper { //SQL连接字符串-SQL身份认证方式登录 public static string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456;"; //SQL连接字符串-Windows身份认证方式登录 //public static string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; //读取配置文件appSettings节点读取字符串(需要添加引用System.Configuration) //public static string connStr = ConfigurationManager.AppSettings["DefaultConn"].ToString(); //对应的配置文件如下： // //  // //读取配置文件ConnectionStrings节点读取字符串(需要添加引用System.Configuration) //public static string connStr = ConfigurationManager.ConnectionStrings["DefaultConn"].ConnectionString; //对应配置文件如下： // //  // public static SqlConnection conn = null; public static SqlDataAdapter adp = null; #region 连接数据库 /// 
 /// 连接数据库 /// 
 public static void OpenConn() { if (conn == null) { conn = new SqlConnection(connStr); conn.Open(); } if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } if (conn.State == System.Data.ConnectionState.Broken) { conn.Close(); conn.Open(); } } #endregion #region 执行SQL语句前准备 /// 
 /// 准备执行一个SQL语句 /// 
 /// 提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                

                

                

                                            
上一篇：.Net连接数据库的方式详解_实用技巧_
                                                                
下一篇：EF Core项目中不同数据库需要的安装包介绍_实用技巧_