sqlHelper做增删改查,SQL注入处理，存储值，cookie，session

一、存储值

  eg:登录一个页面，在进入这个页面之前你怎么知道它登没登录呢？[在登录成功之后我们把状态保存起来]

    存储值得方式有两种，一种是cookie，一种是session

  1.1区别:

     

  代码：

               if (SqlHelper.Exists(sSql, para))
                {
                   //cookie保存状态
                    if (chkRPwd.Checked)
                    {
                        Response.Cookies["name"].Expires = DateTime.Now.AddMinutes(1);//设置过期时间
                        //删除cookie
                        //Response.Cookies.Clear();
                    }
                    Response.Cookies["name"].Value = username;
                    Response.Redirect("HomeAdoSqlHelper.aspx");
                }
                else
                {
                    Response.Write("<script>alert('密码错误！');</script>");
                }
            }
 1                if (SqlHelper.Exists(sSql, para))
 2                 {
 3                     //把session保存安全系数高的东西,保存于浏览器缓存里
 4                     Session["name"] = username;
 5                     Response.Redirect("HomeAdoSqlHelper.aspx");
 6                 }
 7                 else
 8                 {
 9                     Response.Write("<script>alert('密码错误！');</script>");
10                 }
11             }
 1        protected void Page_Load(object sender, EventArgs e)
 2         {
 3             //cookie获取状态
 4             if (Request.Cookies["name"] == null)
 5             {
 6                 Response.Redirect("loginSqlHelper.aspx");
 7             }
 8             if (!(IsPostBack))
 9             {
10                 BindUserInfor();
11             }
12         }
 1          protected void Page_Load(object sender, EventArgs e)
 2         {
 3             //session
 4             if (Session["name"] == null)
 5             {
 6                 Response.Redirect("loginSqlHelper.aspx");
 7             }
 8             if (!(IsPostBack))
 9             {
10                 BindUserInfor();
11             }
12         }

 

二、sqlHelper做增删改查,SQL注入处理[后台绑定下拉一定要对应]

  1     <form id="form1" runat="server">
  2        <div>
  3           <table>
  4               <tr>
  5                   <td> 用户名:</td>
  6                   <td>
  7                       <asp:TextBox ID="txtSUserName" runat="server"></asp:TextBox>
  8                   </td>
  9                   <td>班级：</td>
 10                   <td>
 11                       <asp:DropDownList ID="ddlselPhase" runat="server">
 12                           <asp:ListItem>---请选择---</asp:ListItem>
 13                           <asp:ListItem>.NET高级班01期</asp:ListItem>
 14                           <asp:ListItem>.NET高级班02期</asp:ListItem>
 15                           <asp:ListItem>.NET讲师</asp:ListItem>
 16                           <asp:ListItem>.NET网站开发01期</asp:ListItem>
 17                           <asp:ListItem>.NET网站开发02期</asp:ListItem>
 18                           <asp:ListItem>.NET网站开发03期</asp:ListItem>
 19                           <asp:ListItem>.NET网站开发04期</asp:ListItem>
 20                           <asp:ListItem>.NET网站开发05期</asp:ListItem>
 21                           <asp:ListItem>.NET网站开发06期</asp:ListItem>
 22                           <asp:ListItem>.NET网站开发07期</asp:ListItem>
 23                           <asp:ListItem>.NET网站开发08期</asp:ListItem>
 24                           <asp:ListItem>.NET网站开发09期</asp:ListItem>
 25                           <asp:ListItem>.NET网站开发10期</asp:ListItem>
 26                           <asp:ListItem>.NET网站开发11期</asp:ListItem>
 27                           <asp:ListItem>.NET网站开发12期</asp:ListItem>
 28                           <asp:ListItem>.NET网站开发13期</asp:ListItem>
 29                           <asp:ListItem>.NET网站开发14期</asp:ListItem>
 30                           <asp:ListItem>.NET网站开发15期</asp:ListItem>
 31                           <asp:ListItem>.NET网站开发16期</asp:ListItem>
 32                           <asp:ListItem>java第一期</asp:ListItem>
 33                           <asp:ListItem>JAVA讲师</asp:ListItem>
 34                           <asp:ListItem>ps设计01期</asp:ListItem>
 35                           <asp:ListItem>ps设计02期</asp:ListItem>
 36                           <asp:ListItem>ps设计03期</asp:ListItem>
 37                           <asp:ListItem>网页前端01期</asp:ListItem>
 38                       </asp:DropDownList>
 39                   </td>
 40                   <td>
 41                       <asp:Button ID="btnSel" runat="server" Text="查询" OnClick="btnSel_Click" style="height: 21px" />
 42                   </td>
 43               </tr>
 44           </table>
 45        </div>
 46        <div>
 47             <table>
 48               <tr>
 49                   <td> 用户名:</td>
 50                   <td>
 51                       <asp:TextBox ID="txtAddUserName" runat="server"></asp:TextBox>
 52                   </td>
 53                   <td> 密码:</td>
 54                   <td>
 55                       <asp:TextBox ID="txtAddPwd" runat="server" TextMode="Password"></asp:TextBox>
 56                   </td>
 57                   <td> QQ:</td>
 58                   <td>
 59                       <asp:TextBox ID="txtAddQq" runat="server"></asp:TextBox>
 60                   </td>
 61                   <td>班级：</td>
 62                   <td>
 63                       <asp:DropDownList ID="ddlAddPhase" runat="server">
 64                           <asp:ListItem>---请选择---</asp:ListItem>
 65                           <asp:ListItem>.NET高级班01期</asp:ListItem>
 66                           <asp:ListItem>.NET高级班02期</asp:ListItem>
 67                           <asp:ListItem>.NET讲师</asp:ListItem>
 68                           <asp:ListItem>.NET网站开发01期</asp:ListItem>
 69                           <asp:ListItem>.NET网站开发02期</asp:ListItem>
 70                           <asp:ListItem>.NET网站开发03期</asp:ListItem>
 71                           <asp:ListItem>.NET网站开发04期</asp:ListItem>
 72                           <asp:ListItem>.NET网站开发05期</asp:ListItem>
 73                           <asp:ListItem>.NET网站开发06期</asp:ListItem>
 74                           <asp:ListItem>.NET网站开发07期</asp:ListItem>
 75                           <asp:ListItem>.NET网站开发08期</asp:ListItem>
 76                           <asp:ListItem>.NET网站开发09期</asp:ListItem>
 77                           <asp:ListItem>.NET网站开发10期</asp:ListItem>
 78                           <asp:ListItem>.NET网站开发11期</asp:ListItem>
 79                           <asp:ListItem>.NET网站开发12期</asp:ListItem>
 80                           <asp:ListItem>.NET网站开发13期</asp:ListItem>
 81                           <asp:ListItem>.NET网站开发14期</asp:ListItem>
 82                           <asp:ListItem>ps设计01期</asp:ListItem>
 83                           <asp:ListItem>ps设计03期</asp:ListItem>
 84                           <asp:ListItem>网页前端01期</asp:ListItem>
 85                       </asp:DropDownList>
 86                   </td>
 87                   <td>
 88                       <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" />
 89                   </td>
 90               </tr>
 91           </table>
 92         </div>
 93        <div>
 94             <table>
 95                 <tr>
 96                     <td>用户ID:</td>
 97                     <td>
 98                         <asp:TextBox ID="txtDUId" runat="server"></asp:TextBox>
 99                     </td>
100                     <td>
101                         <asp:Button ID="btnD" runat="server" Text="删除" OnClick="btnD_Click" />
102                     </td>
103                 </tr>
104             </table>
105         </div>
106        <%--<div>
107            <table>
108                <tr>
109                    <td>ID:</td>
110                    <td>
111                        <asp:TextBox ID="txtUId" runat="server"></asp:TextBox>
112                    </td>
113                    <td>
114                        <asp:TextBox ID="txtUUserName" runat="server"></asp:TextBox>
115                    </td>
116                    <td>
117                        <asp:Button ID="btnU" runat="server" Text="更新" OnClick="btnU_Click" />
118                    </td>
119                </tr>
120            </table>
121        </div>--%>
122        <div>
123            <table>
124                <tr>
125                    <td>ID:</td>
126                    <td>
127                        <asp:TextBox ID="txtUuid" runat="server"></asp:TextBox>
128                    </td>
129                    <td>
130                        <asp:TextBox ID="txtUuserName2" runat="server"></asp:TextBox>
131                    </td>
132                    <td>
133                        <asp:Button ID="btnU2" runat="server" Text="更新" OnClick="btnU2_Click"/>
134                    </td>
135                </tr>
136            </table>
137        </div>
138        <div class="dItem">
139            <table>
140                <tr>
141                    <td>
142                        <asp:Button ID="btnDelAll" runat="server" Text="批量删除" OnClick="btnDelAll_Click" OnClientClick="return confirm('你确定删除吗？');"/>
143                    </td>
144                    <td>
145                        <asp:Button ID="btnUpAll" runat="server" Text="批量修改" OnClick="btnUpAll_Click" />
146                    </td>
147                    <td>
148                        <asp:Button ID="btnAllAdd" runat="server" Text="添加" OnClick="btnAllAdd_Click"/>
149                    </td>
150                </tr>
151            </table>
152         </div>
153        <div>
154         <asp:GridView ID="GriVShow" runat="server" AutoGenerateColumns="False" OnRowDataBound="GriVShow_RowDataBound">
155             <Columns>
156                 <asp:TemplateField>
157                     <HeaderTemplate>
158                         <asp:CheckBox ID="chkAll" runat="server" AutoPostBack="True" OnCheckedChanged="chkAll_CheckedChanged" />
159                     </HeaderTemplate>
160                     <ItemTemplate>
161                         <asp:CheckBox ID="chkItem" runat="server" />
162                     </ItemTemplate>
163                 </asp:TemplateField>
164                 <asp:BoundField DataField="Userid" HeaderText="ID" />
165                 <asp:BoundField DataField="UserName" HeaderText="用户名：" />
166                 <asp:BoundField DataField="phonenum" HeaderText="电话号码" />
167                 <asp:BoundField DataField="qq" HeaderText="QQ" />
168                 <asp:BoundField DataField="phase" HeaderText="班级" />
169                 <asp:BoundField DataField="CreatedTime" HeaderText="创建时间" />
170                 <asp:TemplateField HeaderText="用户名">
171                     <ItemTemplate>
172                         <asp:TextBox ID="txtgvUserName" runat="server" Text='<%#Eval("UserName")%>'></asp:TextBox>
173                     </ItemTemplate>
174                 </asp:TemplateField>
175                 <asp:TemplateField HeaderText="密码">
176                     <ItemTemplate>
177                         <asp:TextBox ID="txtgvPwd" runat="server" Text='<%#Eval("Pwd")%>'></asp:TextBox>
178                     </ItemTemplate>
179                 </asp:TemplateField>
180                 <asp:TemplateField HeaderText="QQ">
181                     <ItemTemplate>
182                         <asp:TextBox ID="txtgvQq" runat="server" Text='<%#Eval("QQ")%>'></asp:TextBox>
183                     </ItemTemplate>
184                 </asp:TemplateField>
185                 <asp:TemplateField HeaderText="班级">
186                     <ItemTemplate>
187                         <asp:DropDownList ID="ddlgvPhase" runat="server" ToolTip='<%#Eval("phase")%>'>
188                             <asp:ListItem>---请选择---</asp:ListItem>
189                             <asp:ListItem>.NET高级班01期</asp:ListItem>
190                             <asp:ListItem>.NET高级班02期</asp:ListItem>
191                             <asp:ListItem>.NET讲师</asp:ListItem>
192                             <asp:ListItem>.NET网站开发01期</asp:ListItem>
193                             <asp:ListItem>.NET网站开发02期</asp:ListItem>
194                             <asp:ListItem>.NET网站开发03期</asp:ListItem>
195                             <asp:ListItem>.NET网站开发04期</asp:ListItem>
196                             <asp:ListItem>.NET网站开发05期</asp:ListItem>
197                             <asp:ListItem>.NET网站开发06期</asp:ListItem>
198                             <asp:ListItem>.NET网站开发07期</asp:ListItem>
199                             <asp:ListItem>.NET网站开发08期</asp:ListItem>
200                             <asp:ListItem>.NET网站开发09期</asp:ListItem>
201                             <asp:ListItem>.NET网站开发10期</asp:ListItem>
202                             <asp:ListItem>.NET网站开发11期</asp:ListItem>
203                             <asp:ListItem>.NET网站开发12期</asp:ListItem>
204                             <asp:ListItem>.NET网站开发13期</asp:ListItem>
205                             <asp:ListItem>.NET网站开发14期</asp:ListItem>
206                             <asp:ListItem>ps设计01期</asp:ListItem>
207                             <asp:ListItem>ps设计03期</asp:ListItem>
208                             <asp:ListItem>网页前端01期</asp:ListItem>
209                         </asp:DropDownList>
210                     </ItemTemplate>
211                 </asp:TemplateField>
212                 <asp:TemplateField HeaderText="详情">
213                     <ItemTemplate>
214                         <a href="UserInforManagerContext.aspx?Id=<%#Eval("UserId")%>">详情</a>
215                     </ItemTemplate>
216                 </asp:TemplateField>
217             </Columns>
218         </asp:GridView>
219     </div>
220     </form>
  1 namespace sqlHelperStudy2160907
  2 {
  3     public partial class HomeAdoSqlHelperFirst : System.Web.UI.Page
  4     {
  9         SqlParameter[] para=new SqlParameter[2];
 10         protected void Page_Load(object sender, EventArgs e)
 11         {
 12             if (!(IsPostBack))
 13             {
 14                 BindUserInfor();
 15             }
 16         }
 17         public void BindUserInfor()
 18         {
 19             try
 20             {
 21                 DataTable dt = SqlHelper.ExecuteDataSetText(GetSql(), para).Tables[0];
 22                 GriVShow.DataSource = dt;
 23                 GriVShow.DataBind();
 24             }
 25             catch (Exception ex)
 26             {
 27                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
 28             }
 29         }
 30         public string GetSql()
 31         {
 32             StringBuilder sb = new StringBuilder();
 33             //sb.Append("select Userid,UserName,phonenum,sex,phase,qq,Message,HeadPic,CreatedTime from UserInfor where 1=1");
 34             sb.Append("select top 100 * from UserInfor where 1=1 ");
 35             if (!string.IsNullOrEmpty(txtSUserName.Text.Trim()))
 36             {
 37                 //sb.Append(string.Format("and UserName='{0}'", txtSUserName.Text.Trim()));
 38                 sb.Append("and UserName=@UserName");
 39                 para[0] = new SqlParameter("@UserName",txtSUserName.Text.Trim());
 40             }
 41             if (ddlselPhase.SelectedIndex > 0)
 42             {
 43                 sb.Append("and phase=@phase");
 44                 para[1] = new SqlParameter("@phase",ddlselPhase.SelectedValue);
 45                 //sb.Append(string.Format("and phase='{0}'", ddlselPhase.SelectedValue));
 46             }
 47             return sb.ToString();
 48         }
 49 
 50         protected void btnSel_Click(object sender, EventArgs e)
 51         {
 52             BindUserInfor();
 53         }
 54 
 55         protected void chkAll_CheckedChanged(object sender, EventArgs e)
 56         {
 57             //1.当我们点击按钮时，去找chkAll它的事件源的对象把它变成checkBox
 58             CheckBox chkAll = sender as CheckBox;
 59             //2.对它的每一行进行遍历循环
 60             foreach (GridViewRow gvr in GriVShow.Rows)
 61             {
 62                 //2.1获得到第一行的第一列，找到每一列id等于chkitem的对象把它变成checkbox
 63                 CheckBox chkItem = gvr.Cells[0].FindControl("chkItem") as CheckBox;
 64                 //2.2让它当前全选的checked属性等于下面每一行的属性，选中就为true，没选就为Fulse
 65                 chkItem.Checked = chkAll.Checked;
 66             }
 67         }
 68         protected void GriVShow_RowDataBound(object sender, GridViewRowEventArgs e)
 69         {
 70             if (e.Row.RowType == DataControlRowType.DataRow)
 71             {
 72                 DropDownList ddlphase = e.Row.FindControl("ddlgvPhase") as DropDownList;
 73                 string phase = ddlphase.ToolTip;
 74                 if (!string.IsNullOrEmpty(phase))
 75                 {
 76                     ddlphase.ClearSelection();
 77                     ddlphase.Items.FindByValue(phase).Selected = true;
 78                 }
 79             }
 80         }
 81 
 82         protected void btnAdd_Click(object sender, EventArgs e)
 83         {
 84             string username = txtAddUserName.Text.Trim();
 85             string pwd = txtAddPwd.Text.Trim();
 86             string qq = txtAddQq.Text.Trim();
 87             string phase = ddlAddPhase.SelectedIndex > 0 ? ddlAddPhase.SelectedValue : "";
 88             if (!string.IsNullOrEmpty(username))
 89             {
 90                 string sSql = string.Format("insert into UserInfor(UserName,Pwd,QQ,Phase)values(@UserName,@Pwd,@QQ,@Phase)");
 91                 SqlParameter[] paraA = new SqlParameter[] 
 92                 {
 93                       new SqlParameter("@UserName",username),
 94                       new SqlParameter("@Pwd",pwd),
 95                       new SqlParameter("@QQ",qq),
 96                       new SqlParameter("@Phase",phase)
 97                 };
 98                 SqlHelper.ExecteNonQueryText(sSql, paraA);
 99                 BindUserInfor();
100             } 
101         }
102         public void Del(int UserId)
103         {
104             try
105             {
106                     //string ssql = string.Format("select UserId from UserInfor where UserId='{0}'", UserId);
107                     string ssql = "select UserId from UserInfor where UserId=@UserId";
108                     SqlParameter[] paraD = new SqlParameter[] { new SqlParameter("@UserId", UserId) };
109                     if(SqlHelper.Exists(ssql,paraD))
110                     {
111                         string sSql1 = string.Format("delete UserInfor where UserId=@UserId");
112                         if(SqlHelper.ExecteNonQueryText(sSql1,paraD)>0)
113                         {
114                            Response.Write("<script>alert('删除成功！');</script>");
115                         }
116                         BindUserInfor();
117                     }
118                     else
119                     {
120                         Response.Write("该用户不存在！");
121                     }
122             }
123             catch (Exception ex)
124             {
125                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
126             }
127         }
128         protected void btnD_Click(object sender, EventArgs e)
129         {
130             int UserId = txtDUId.Text.Trim() == "" ? 0 : Convert.ToInt32(txtDUId.Text.Trim());
131             Del(UserId);
132             BindUserInfor();
133         }
134 
135         protected void btnU2_Click(object sender, EventArgs e)
136         {
137             int Uid2 = txtUuid.Text.Trim() == "" ? 0 : Convert.ToInt32(txtUuid.Text.Trim());
138             string uusername2 = txtUuserName2.Text.Trim();
139             try
140             {
141                     string sSql = string.Format("select count(*) from UserInfor where UserId=@UserId");
142                    SqlParameter[] paraU = new SqlParameter[] { new SqlParameter("@UserId", Uid2) };
143                     int icount = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text,sSql,paraU));
144                     if (icount > 0)
145                     {
146                         string sSql1 = string.Format("update UserInfor set UserName=@UserName where UserId=@UserId");
147                         //string ssql1 = "update UserInfor set UserName=@UserName where UserId=@UserId";
148                         SqlParameter[] paraU1 = new SqlParameter[] 
149                         { 
150                             new SqlParameter("@UserName",uusername2),
151                             new SqlParameter("@UserId",Uid2)
152                         };
153                         if (SqlHelper.ExecteNonQueryText(sSql1,paraU1)>0)
154                         {
155                             Response.Write("<script>alert('数据更新成功！');</script>");
156                         }
157                         BindUserInfor();
158                     }
159                     else
160                     {
161                         Response.Write("该用户不存在！");
162                     }
163                 }
164             catch (Exception ex)
165             {
166                 Response.Write("<script>alret('系统正在维护，请联系管理员!');</script>");
167             }
168         }
169 
170         protected void btnDelAll_Click(object sender, EventArgs e)
171         {
172             foreach (GridViewRow gvr in GriVShow.Rows)
173             {
174                 int UserId = Convert.ToInt32(gvr.Cells[1].Text);
175                 CheckBox chkItem = gvr.Cells[0].FindControl("chkItem") as CheckBox;
176                 if (chkItem.Checked)
177                 {
178                     Del(UserId);
179                 }
180             }
181             BindUserInfor();
182         }
183 
184         public void UpUser(int UserId, string UserName, string Pwd, string qq, string phase)
185         {
186             try
187             {
188                 string sSql = string.Format("update UserInfor set UserName=@UserName,Pwd=@Pwd,QQ=@QQ,Phase=@Phase where UserId=@UserId");
189                 SqlParameter[] paraAll = new SqlParameter[] 
190             {
191                 new SqlParameter("@UserName",UserName),
192                 new SqlParameter("@Pwd",Pwd),
193                 new SqlParameter("@qq",qq),
194                 new SqlParameter("@Phase",phase),
195                new SqlParameter("@UserId",UserId)
196             };
197                 if (SqlHelper.ExecteNonQueryText(sSql, paraAll) > 0)
198                 {
199                     Response.Write("<script>alert('数据更新成功！');</script>");
200                 }
201             }
202             catch (Exception)
203             {
204                 Response.Write("网页正在维护!");
205             }
206         }
207         protected void btnUpAll_Click(object sender, EventArgs e)
208         {
209             foreach (GridViewRow gvr in GriVShow.Rows)
210             {
211                 int UserId = Convert.ToInt32(gvr.Cells[1].Text);
212                 CheckBox chkItem = gvr.Cells[0].FindControl("chkItem") as CheckBox;
213                 if (chkItem.Checked)
214                 {
215                     TextBox txtUserName = gvr.Cells[7].FindControl("txtgvUserName") as TextBox;
216                     TextBox txtPwd = gvr.Cells[8].FindControl("txtgvPwd") as TextBox;
217                     TextBox txtqq = gvr.Cells[9].FindControl("txtgvQq") as TextBox;
218                     DropDownList ddlphase = gvr.Cells[10].FindControl("ddlgvPhase") as DropDownList;
219                     UpUser(UserId, txtUserName.Text.Trim(),txtPwd.Text.Trim(), txtqq.Text.Trim(), ddlphase.SelectedValue);
220                 }
221             }
222             BindUserInfor();
223         }
224 
225         protected void btnAllAdd_Click(object sender, EventArgs e)
226         {
227             try
228             {
229                 string sSql = string.Format("insert into UserInfor (Phase,CreatedTime) values(@Phase,@CreatedTime)");
230                 SqlParameter[] paraAdd = new SqlParameter[] 
231                 {
232                     new SqlParameter("@Phase","网页前端01期"),
233                     new SqlParameter("@CreatedTime",DateTime.Now.ToString())
234                 };
235                 SqlHelper.ExecteNonQueryText(sSql, paraAdd);
236                 BindUserInfor();
237             }
238             catch (Exception)
239             {
240                 Response.Write("网页正在维护!");
241             }
242         }
243     }
244 }

 

三、post传值:

postProject.aspx

 1     <form id="form1" action="PostProjectT.aspx" method="post">
 2         <table>
 3             <tr>
 4                 <td><input type="text" name="UserName"/></td>
 5             </tr>
 6             <tr>
 7                 <td><input type="password" name="Pwd"/></td></tr>
 8             <tr>
 9                 <td><input type="submit"value="提交"/></td>
10             </tr>
11         </table>
12     </form>

PostProjectT.aspx.cs

1         protected void Page_Load(object sender, EventArgs e)
2         {
3             //获取到用户名和密码
4             string username = Request.Form["UserName"].ToString();
5             string pwd = Request.Form["Pwd"].ToString();
6             //把用户名和密码返回回去
7             Response.Write(username+"-"+pwd);
8         }
posted @ 2016-09-18 13:39  wangwangwangMax  阅读(1223)  评论(0)  编辑  收藏  举报