title: Preventing SQL injection asp.net in C#
body: <p>I have been trying to cure this site of SQL injection and a page I have been working on has had me stopped for 2 days.  </p>
  
  <p>So far I am validating user input from the client site using. RegularExpressionValidator. </p>
  
  <p>On this page this is the only typed input from the user.  There is also a dynamic drop down that is being verified using server-side verification. </p>
  
  <p>Data from the username textbox is also being validated on the client side using Regex.  </p>
  
  <p>Initially I converted all of the queries to be parametrized queries. Since I have converted all of the parametrized queries to stored procedures.  </p>
  
  <p>Now I am at a loss on where to go next. From searching forums the mix of client-side validation and parametrized queries will generally secure against injection.  </p>
  
  <p>I feel like I am missing something here.  </p>
  
  <p>Attached is the code for the page as well as the usercontrol in c#.  Any direction would be greatly appreciated!</p>
  
  #
  
  <pre><code>  &lt;%@ Control Language="C#" AutoEventWireup="true"    Inherits="EPayment.AdminSite.AssignUsersUC"   %&gt;
  
  &lt;script runat="server" type="text/javascript" &gt;
  &lt;/script&gt;
  
  &lt;div style="float:left;width:120px"&gt;&lt;asp:Label ID="UserNameLbl" runat="server" Text="User Logon:" CssClass="label"&gt;&lt;/asp:Label&gt;&lt;/div&gt;
  &lt;div style="float:left; height: 22px;"&gt;&lt;asp:TextBox ID="UserNameTxt" runat="server"  OnTextChanged="UserNameTxt_TextChanged"&gt;&lt;/asp:TextBox&gt;
  
   &lt;asp:RegularExpressionValidator id="RegularExpressionValidator1" 
                       ControlToValidate="userNameTxt"
                       ValidationExpression="[a-zA-Zs0-9]{1,40}$"
                       AutoPostBack="true"
                       Display="Static"
                       ErrorMessage="Username must contain only Alpha-Numeric Characters"
                       EnableClientScript="False" 
                       runat="server"/&gt;
  
  
    &lt;div style="float:left"&gt;  &lt;asp:DropDownList ID="ddlcompany" runat="server" AutoPostBack="true" DataTextField="CompanyName" DataValueField="CompanyId" OnSelectedIndexChanged="ddlcompany_SelectedIndexChanged" &gt;
  
    &lt;/asp:DropDownList&gt;&lt;/div&gt;
  
     &lt;/div&gt;
     &lt;br /&gt;
    &lt;div style="clear:both"&gt;&lt;asp:Label ID="companyLbl" runat="server" Text="Company:" CssClass="label"&gt;&lt;/asp:Label&gt;  &lt;/div&gt;
  
     &lt;br /&gt;
     &lt;div&gt; &lt;asp:Button ID="btngetroles" Text="GetRoles" runat="server" Visible="false" OnClick="btngetroles_Click" /&gt;&lt;asp:Button ID="btngetuserobject" Text="GetUserId" runat="server" Visible="false" OnClick="btngetuserobject_Click"  /&gt;&lt;/div&gt;
  &lt;div class="sectionRow" style="width:100%;"&gt;Roles:
  &lt;/div&gt;
  
  &lt;br /&gt;
  
   &lt;div style="width:600px"&gt;
   &lt;asp:GridView ID="GV" runat="server" DataKeyNames="RoleId" AutoGenerateColumns="false" Width="100%" ShowHeader="true" ShowFooter="false" 
   PageSize="100" CellPadding="7"&gt;
       &lt;HeaderStyle CssClass="gridHdr" /&gt;
   &lt;Columns&gt;
  
    &lt;asp:TemplateField&gt;
   &lt;ItemTemplate&gt;
   &lt;asp:CheckBox id="CheckBox2" runat="server" AutoPostBack="True" &gt;&lt;/asp:CheckBox&gt;
  
  
   &lt;/ItemTemplate&gt;
  
   &lt;/asp:TemplateField&gt;
  
   &lt;asp:TemplateField HeaderText="Role Description" &gt;
  &lt;ItemTemplate &gt;
  &lt;%#
                      DataBinder.Eval(Container.DataItem, "RoleDesc").ToString().Trim()
                  %&gt;
  
  &lt;/ItemTemplate&gt;
  
  
  
  &lt;/asp:TemplateField&gt;
  
  
   &lt;/Columns&gt;
  
  
   &lt;/asp:GridView&gt;
  
  
  
   &lt;/div&gt;
  &lt;br /&gt;
   &lt;div style="float:left;width:120px"&gt;&lt;asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click"   /&gt;
   &lt;asp:Button ID="btnReset" runat="server" Text="Reset" OnClick="btnReset_Click"   /&gt;
   &lt;/div&gt;
   &lt;div&gt;
   &lt;asp:Label ID="Result" runat="server" ForeColor="red"&gt;&lt;/asp:Label&gt;&lt;/div&gt;
  </code></pre>
  
  #
  
  <pre><code>using System;
  using System.Data;
  using System.Configuration;
  using System.Collections;
  using System.Web;
  using System.Web.Security;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Web.UI.WebControls.WebParts;
  using System.Web.UI.HtmlControls;
  using EPayment.DatabaseConnectors;
  using EPayment.DataObjects;
  using EPayment.Common;
  using ESource.Security;
  using System.Collections.Generic;
  using System.Collections.ObjectModel;
  using System.Text.RegularExpressions;
  using ESource.Installation;
  
  namespace EPayment.AdminSite
  {
      public partial class AssignUsersUC : System.Web.UI.UserControl
      {
          private string ConnectionString;
          protected SYUserConnector syuserconnector;
          protected SYTaskConnector sytaskconnector;
          protected SYRoleConnector syroleconnector;
          protected SYTask sytask;
          protected SYUser syuser;
          protected SYRole syrole;
          protected SYUtility syutility;
          private DBConnString dbconn;
          private string dbFilePath;
          private string logFilePath;
          protected TextBox UserNameTxt;
          protected DropDownList ddlcompany;
          protected GridView GV;
          //protected TextBox UserIdtxt;
          protected Label Result;
  
          private MerchantDBConnector mConnector;
          private InstallationManager dbReg;
  
  
          protected void Page_Load(object sender, EventArgs e)
          {
  
              UserNameTxt.AutoPostBack = true;
  
              syuserconnector = new SYUserConnector();
              syuserconnector.SetConnection(ConnectionString);
              syroleconnector = new SYRoleConnector();
              syroleconnector.SetConnection(ConnectionString);
  
              sytaskconnector = new SYTaskConnector();
              sytaskconnector.SetConnection(ConnectionString);
  
              syutility = new SYUtility();
              syutility.SetConnection(ConnectionString);
              syuser = new SYUser();
  
  
              if (!IsPostBack)
              {
                  DataTable dt = new DataTable();
                  dt = syutility.GetSYCompanies();
                  ddlcompany.DataSource = dt;
                  ddlcompany.DataBind();
  
                  ArrayList companies = mConnector.GetGPCompanyIds();
                  foreach (string[] company in companies)
                  {
                      ddlcompany.SelectedIndex = -1;
                      ddlcompany.Items.FindByText(company[1]);
                      //Context.Response.Write(ddlcompany.SelectedItem.Text + "&lt;br&gt;");
                      //Context.Response.Write("Before:" + company[1] + "&lt;br&gt;");
                      //Context.Response.Write("Before Company ID:" + company[0] + "&lt;br&gt;");
  
                      if (ddlcompany.SelectedItem.Text.Trim() == company[1].Trim())
                      {
                          //Context.Response.Write("if:" + ddlcompany.SelectedItem.Text.Trim() + "&lt;br&gt;");
                          //Context.Response.Write("Company Name:" + company[1] + "&lt;br&gt;");
                          //Context.Response.Write("Company ID:" + company[0] + "&lt;br&gt;");
                      }
                      else
                      {
  
                          //Context.Response.Write("else:" + ddlcompany.SelectedItem.Text.Trim() + "&lt;br&gt;");
                          //Context.Response.Write("Company ID:" + company[0] + "&lt;br&gt;");
                          DBConnString epConn = new DBConnString(logFilePath, dbFilePath);
  
                          dbReg.InsertGPCompanyIntoSYCompany(epConn.StrGPServer, epConn.StrGPUser, epConn.StrGPPass, ConfigurationManager.AppSettings["EPaymentDBName"], company[0], company[1]);
  
                          //ddlcompany.Items.Add(new ListItem(company[1], company[0]));
                          dt = syutility.GetSYCompanies();
                          ddlcompany.Items.Clear();
                          ddlcompany.DataSource = dt;
                          ddlcompany.DataBind();
                      }
                  }
  
  
                  //ddlcompany.Items.Insert(0, new ListItem("ViewAll", "ViewAll"));
                  string companyname = ConfigurationManager.AppSettings["EPaymentCompanyERPId"];
                  string companyID = syutility.GetCompanyId(companyname);
                  DataView dv = new DataView();
                  dv = syroleconnector.GetAllRoles(companyID, 0);
                  GV.DataSource = dv;
                  GV.DataBind();
              }
  
  
          }
          protected void btngetroles_Click(object sender, EventArgs e)
          {
  
          }
          protected void ddlcompany_SelectedIndexChanged(object sender, EventArgs e)
          {
              Getroles();
  
          }
          protected void Page_UnLoad(object sender, EventArgs e)
          {
              syuserconnector.CloseConnection();
              syroleconnector.CloseConnection();
              sytaskconnector.CloseConnection();
              syutility.CloseConnection();
              syuserconnector = null;
              syroleconnector = null;
              sytaskconnector = null;
              syutility = null;
              syuser = null;
  
          }
          private void Page_Init(System.Object sender, System.EventArgs e) //Handles page_init event
          {
              string serverPath = Request.PhysicalApplicationPath;
              dbFilePath = serverPath + "include\\dbconn.txt";
              logFilePath = serverPath + "logs\\azoxlog.txt";
              dbconn = new DBConnString(logFilePath, dbFilePath);
              ConnectionString = dbconn.StrEPConnString;
  
              MerchantAccount m = new MerchantAccount();
              mConnector = new MerchantDBConnector(dbFilePath, logFilePath, m);
  
              dbReg = new InstallationManager();
              dbReg.UseLogFile = true;
              dbReg.LogFilePath = logFilePath;
  
          }
          protected void btnSave_Click(object sender, EventArgs e)
          {
              if (Page.IsValid)
              {
                  syuser = new SYUser();
                  //Regex r = new Regex("^[a-zA-Z0-9]*$");
                  //if (r.IsMatch(UserNameTxt.Text.Trim()))
                  //{
                  string username = UserNameTxt.Text;
                  string companyID = ddlcompany.SelectedItem.Value;
                  ArrayList companies = mConnector.GetGPCompanyIds();
                  //bool found = companies.Contains(companyID);
                  //if (found == true)
                  //{
                      string userid = syuserconnector.GetUserId(username, companyID);
  
                      if (userid != null &amp;&amp; userid != "")
                      {
  
                          Result.Text = "";
  
                          //string userId = UserIdtxt.Text;
                          Collection&lt;string&gt; idsList = new Collection&lt;string&gt;();
                          foreach (GridViewRow row in GV.Rows)
                          {
                              CheckBox chk = (CheckBox)row.FindControl("CheckBox2");
                              if (chk != null &amp;&amp; chk.Checked)
                              {
                                  string secId = GV.DataKeys[row.RowIndex].Value.ToString();
  
                                  idsList.Add(secId);
  
                                  //Response.Write("TaskId: " +secId + "&lt;br/&gt;");
                                  //Response.End();
                              }
  
                          }
                          syuserconnector.UpdateUserRoles(userid, idsList);
                          //Start Check if user is given access to BatchProcess and add user to ep database so that sql user has access to EP_BatchReport table which is public
                          UserDBConnector userConn = new UserDBConnector(dbFilePath, logFilePath, new EPayment.DataObjects.User());
                          userConn.CreateUserLogOnForBatchReportAccess(UserNameTxt.Text);
                          //End
                          Result.Text = "Roles are Assigned to the User";
  
  
                      }
                      else
                      {
  
  
                          Result.Text = "";
                          syuser = new SYUser();
                          syuser.UserName = UserNameTxt.Text;
                          string companyname = ddlcompany.SelectedItem.Text;
                          companyID = ddlcompany.SelectedItem.Value;
                          syuser.CompanyId = companyID;
                          syuser.StoreId = 0;
                          syuser.CreatedBy = Session["userLogon"].ToString();
  
                          syuser.ExpireDate = DateTime.Now;
  
                          userid = syuserconnector.SaveUser(syuser);
  
                          //UserIdtxt.Text = userid;
  
                          if (userid != null &amp;&amp; userid != "")
                          {
  
                              //string userId = UserIdtxt.Text;
                              Collection&lt;string&gt; idsList = new Collection&lt;string&gt;();
                              foreach (GridViewRow row in GV.Rows)
                              {
                                  CheckBox chk = (CheckBox)row.FindControl("CheckBox2");
                                  if (chk != null &amp;&amp; chk.Checked)
                                  {
                                      string secId = GV.DataKeys[row.RowIndex].Value.ToString();
  
                                      idsList.Add(secId);
  
                                      //Response.Write("TaskId: " +secId + "&lt;br/&gt;");
                                      //Response.End();
                                  }
  
                              }
                              syuserconnector.UpdateUserRoles(userid, idsList);
                              Result.Text = "User is Added and Roles are assigned to the User";
  
                          }
  
  
                      //}
  
  
                      //}
                  }
              }
              //else
              //{
              //    Result.Text = "Username can only contain alpha-numeric characters. ";
              //}
          }
  
  
          protected void btnReset_Click(object sender, EventArgs e)
          {
              resetAllFields();
          }
          private void resetAllFields()
          {
              //UserIdtxt.Text = "";
              UserNameTxt.Text = "";
              Result.Text = "";
              ddlcompany.SelectedIndex = ddlcompany.Items.IndexOf(ddlcompany.Items.FindByValue("E-Payment"));
  
              foreach (GridViewRow row in GV.Rows)
              {
                  CheckBox chk = (CheckBox)row.FindControl("CheckBox2");
                  chk.Checked = false;
              }
          }
          protected void btngetuserobject_Click(object sender, EventArgs e)
          {
  
          }
          public void Getroles()
          {
              if (ValidatePage() == true)
              {
                  Page.Validate();
                  if (Page.IsValid)
                  {
                      string companyID = ddlcompany.SelectedItem.Value;
                      //ArrayList companies = mConnector.GetGPCompanyIds();
                      //bool found = companies.Contains(companyID);
                      //if (found == true)
                      //{
                          Result.Text = "";
                          syuserconnector.UseLogFile = true;
                          syuserconnector.LogFilePath = Request.PhysicalApplicationPath + "logs\\azoxlog.txt";
                          Collection&lt;string&gt; idsList = new Collection&lt;string&gt;();
  
                          string companyname = ddlcompany.SelectedItem.Text;
                          companyID = ddlcompany.SelectedItem.Value;
                          // string ERPcompanyId;
                          //string companyID = "";
                          //if (companyname == "Fabrikam Inc")
                          //{
                          //    ERPcompanyId = "-1";
                          //    companyID = syutility.GetCompanyId(ERPcompanyId);
                          //}
                          //else
                          //{
                          // string companyID = syutility.GetCompanyId(companyname);
                          //}
                          //Response.Write(companyID);
                          Regex r = new Regex("[a-zA-Z]{1,40}");
                          string userid;
                          string username = UserNameTxt.Text;
  
                          if (username != null &amp;&amp; r.IsMatch(UserNameTxt.Text.Trim()))
                          {
                              foreach (GridViewRow row in GV.Rows)
                              {
                                  CheckBox chk = (CheckBox)row.FindControl("CheckBox2");
                                  chk.Checked = false;
                              }
                              userid = syuserconnector.GetUserId(username, companyID);
                              //UserIdtxt.Text = userid;
                              // Response.Write("Test:" + userid);
                              if (userid != null &amp;&amp; userid != "")
                              {
  
                                  syuser = new SYUser();
                                  syuser = syuserconnector.GetUserObject(userid);
                                  idsList = syuser.RoleIds;
                                  foreach (GridViewRow row in GV.Rows)
                                  {
                                      string rolegv = GV.DataKeys[row.RowIndex].Value.ToString();
                                      // Response.Write(securitygv + "&lt;br&gt;");
                                      CheckBox chk = (CheckBox)row.FindControl("CheckBox2");
                                      if (syuser.RoleIds.Contains(rolegv))
                                      {
                                          chk.Checked = true;
                                      }
                                  }
                              }
                              else
                              {
                                  foreach (GridViewRow row in GV.Rows)
                                  {
                                      CheckBox chk = (CheckBox)row.FindControl("CheckBox2");
                                      chk.Checked = false;
                                  }
                                  Result.Text = "User not in any roles for " + companyname;
                              }
                          }
  
                          //else
                          //   {
                          //       Result.Text = "Enter Username";
                          //   }
  
                      //}
                  }
              }
          }
  
  
  
  
          protected void UserNameTxt_TextChanged(object sender, EventArgs e)
          {
              //string Username = UserNameTxt.Text;
              //resetAllFields();
              //UserNameTxt.Text = Username;
  
              //Page.Validate();
              //if (Page.IsValid)
              //{
              if (ValidatePage() == true)
              {
                  //Regex r = new Regex("[a-zA-Z0-9]*$");
                  //if (r.IsMatch(UserNameTxt.Text.Trim()))
                  //{
                  Getroles();
                  //}
              }
          }
  
          protected bool ValidatePage()
          {
              Page.Validate();
              if (Page.IsValid)
              {
                  Regex r = new Regex("[a-zA-Z0-9]");
                  if (r.IsMatch(UserNameTxt.Text.Trim()))
                  {
                      return true;
                  }
                  return false;
              }
              return false;
  
          }
      }
  }
  </code></pre>