今天主要任务就是把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,所以就不能合作了。