工作日志5/27 - SQL Parameterization & Character Count

最新推荐文章于 2020-09-11 22:01:29 发布

原创最新推荐文章于 2020-09-11 22:01:29 发布 · 458 阅读

0 ·

CC 4.0 BY-SA版权

工作生涯同时被 2 个专栏收录

35 篇文章

订阅专栏

Web developing

21 篇文章

订阅专栏

本文介绍了修复一个存在SQL注入漏洞的在线目录系统的经历，通过使用参数化查询替代字符串拼接，增强了系统的安全性。

今天主要任务就是把Online Directory那个表格的错误修好，主要是擦之前老头的屁股上的屎，他没用SQL injection，所以我要用Parameterization来修复所有的。

用了

strAcademics =
                        @"INSERT INTO [OnlineDirectoryFacultyAcademics] (
                          SerialId, 
                          EDUCATION, 
                          TEACHING, 
                          RESEARCH, 
                          CreativeActivities, 
                          EXTENSION, 
                          PROFESSIONAL, 
                          GeneralPosDescr, 
                          SPECIALTY, 
                          AreaOfResearch,  
                          ApplicationsOfResearch,  
                          COMMITTEEACTIVITIES,  
                          GRANTS,  
                          PATENTS, 
                          PUBLICATIONS, 
                          AWARDS, 
                          VITA,
                          FORMERPOSITIONSHELD,
                          LINKTOMYPAGE, 
                          AREAOFSTUDYINTEREST, 
                          WANTTOBEEXPERT, 
                          MODIFIEDBY, 
                          PROFILEAPPROVED, 
                          PHOTO,  
                          DATETIMEMODIFIED, 
                          BIOGRAPHY ) 
                          VALUES(@SerialId, 
                          @Education,
                          @Teaching,
                          @Research,
                          @CreativeActivities,
                          @Extension, 
                          @Professional,
                          @GenPosDescr,
                          @Specialty,
                          @AreaOfResearch, 
                          @ApplicationsOfResearch, 
                          @CommitteeAct,
                          @Grants,
                          @Patents,
                          @Publications,
                          @Awards,
                          '" + uploadedFile + "'," +
                          @"@FHP,
                          @Link,
                          @Area,
                          '" + bExpert + "'," +
                          "'" + LoginUserName + "'," +
                          "'" + profileApproved + "'," +
                          "'" + uploadedPhoto + "'," +
                          "'" + DateTime.Now.ToLocalTime() +
                          "',@Bio)";
                        /*
						"VALUES " +
						"( " +
						"   " + Request.QueryString["SerialId"] + ", " +
						"   '" + ChangeString(txtEducation.Text) + "', " +
						"   '" + ChangeString(txtTeaching.Text) + "', " +
						"   '" + ChangeString(txtResearch.Text) + "', " +
						"   '" + ChangeString(txtCreativeActivities.Text) + "', " +		//	JM, 4/24/12 - Code added for "Creative Activities" field
						"   '" + ChangeString(txtExtension.Text) + "', " +
						"   '" + ChangeString(txtProfessional.Text) + "', " +
						"   '" + ChangeString(txtGenPosDescr.Text) + "', " +	//	JM, 4/23/12 - Code added for "General Position Description" field
                        "   '" + ChangeString(txtSpecialty.Text) + "', " +
                        // "   '" + ChangeString(txtAreaOfResearch.Text) + "', " +//added CQ
                        // "   '" + ChangeString(txtApplicationsOfResearch.Text) + "', " +//added CQ
                        "   '" + ChangeString(txtCommitteeAct.Text) + "', " +
						"   '" + ChangeString(txtGrants.Text) + "', " +
						"   '" + ChangeString(txtPatents.Text) + "', " +
						"   '" + ChangeString(txtPublications.Text) + "', " +
						"   '" + ChangeString(txtAwards.Text) + "', " +
						"   '" + uploadedFile + "', " +
						"   '" + ChangeString(txtFHP.Text) + "', " +
						"   '" + ChangeString(txtLink.Text) + "', " +
						"   '" + ChangeString(txtArea.Text) + "', " +
						"   '" + bExpert + "', " +
						"   '" + LoginUserName + "', " +
						"   '" + profileApproved + "', " +
						"   '" + uploadedPhoto + "', " +
						"   '" + DateTime.Now.ToLocalTime() + "', " +
						"   '" + ChangeString(txtBio.Text) + "' " +
                        ")";*/
                    SqlCommand cmdAcademics = new SqlCommand(strAcademics, dbConn);
                    cmdAcademics.Parameters.AddWithValue("@SerialId", Request.QueryString["SerialId"]);
                    cmdAcademics.Parameters.AddWithValue("@Education",  txtEducation.Text);
                    cmdAcademics.Parameters.AddWithValue("@Teaching",  txtTeaching.Text);
                    cmdAcademics.Parameters.AddWithValue("@Research",  txtResearch.Text);
                    cmdAcademics.Parameters.AddWithValue("@CreativeActivities",  txtCreativeActivities.Text);
                    cmdAcademics.Parameters.AddWithValue("@Extension",  txtExtension.Text);
                    cmdAcademics.Parameters.AddWithValue("@Professional",  txtProfessional.Text);
                    cmdAcademics.Parameters.AddWithValue("@GenPosDescr",  txtGenPosDescr.Text);
                    cmdAcademics.Parameters.AddWithValue("@Specialty",  txtSpecialty.Text);
                    cmdAcademics.Parameters.AddWithValue("@AreaOfResearch",  txtAreaOfResearch.Text);
                    cmdAcademics.Parameters.AddWithValue("@ApplicationsOfResearch",  txtApplicationsOfResearch.Text);
                    cmdAcademics.Parameters.AddWithValue("@CommitteeAct",  txtCommitteeAct.Text);
                    cmdAcademics.Parameters.AddWithValue("@Grants",  txtGrants.Text);
                    cmdAcademics.Parameters.AddWithValue("@Patents",  txtPatents.Text);
                    cmdAcademics.Parameters.AddWithValue("@Publications",  txtPublications.Text);
                    cmdAcademics.Parameters.AddWithValue("@Awards",  txtAwards.Text);
                    cmdAcademics.Parameters.AddWithValue("@FHP",  txtFHP.Text);
                    cmdAcademics.Parameters.AddWithValue("@Link",  txtLink.Text);
                    cmdAcademics.Parameters.AddWithValue("@Area",  txtArea.Text);
                    cmdAcademics.Parameters.AddWithValue("@Bio",  txtBio.Text);
                    cmdAcademics.ExecuteNonQuery();
 
                }	//	End SQL to Add new record to Academics Table
                //moved up to the two conditions by CQ 5/27/2014
                //SqlCommand cmdAcademics = new SqlCommand(strAcademics, dbConn);
                //cmdAcademics.ExecuteNonQuery();
				academicsInserted = true;
			}
			catch (Exception ex)
			{
				if (dbConn.State == ConnectionState.Open)
				{
					dbConn.Close();
				}
				LogToDatabase(ex.ToString());
				academicsInserted = false;
			}

之前他那个changeString为了达到同样的目的，把单引号转换成两个单引号，但是如果SQL statement里面出现双引号也是不行的，他fails to do so，parameterize是最好的解决方法。用到了AddWithValue这个method，比较直白。

需要注意的是，bExpert是个int值，我这里要是把他放在SQL语句的引号里面，他会自动把“bExpert”这个string传过去，就错了，我搞了半天，原来发现传进去的就是string，于是改回来，就好了。

另个活，就是改动validation的，在asmx文件里，

<asp:TextBox ID="txtGenPosDescr" runat="server" Rows="8" TextMode="MultiLine" Width="350px"
					Visible="False" onKeyDown="textCounter2('txtGenPosDescr','txtRemLen2',1500)"
					onKeyUp="textCounter2('txtGenPosDescr','txtRemLen2',1500)" Font-Names="Arial"
					Font-Size="Small" MaxLength="1500"></asp:TextBox>
				<asp:TextBox ID="txtRemLen2" runat="server" ReadOnly="True" Width="30px" Visible="False"
					ForeColor="blue"></asp:TextBox>
				<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtGenPosDescr"
					Display="Dynamic" ErrorMessage="Max 1500 characters" Font-Bold="True" ValidationExpression="^[\s\S]{0,1500}$"
					></asp:RegularExpressionValidator>

里面有两个，一个数还剩多少数值，script里另外有专门的textCounter2（）method与之对应，一个是数超没超过最大值，都是动态的，鼠标一点开文本框就会给出结果，很酷。

最后git 如果comment最后不是自己，应该clone到本地，在继续work，目前因为只有一个fork，所以就不能合作了。