Thursday, March 6, 2014

Remove SQL Injected Data from Data Column using asp.net c#

 using System;  
 using System.Data;  
 using System.Data.SqlClient;  
 public partial class Check : System.Web.UI.Page  
 {  
   SqlConnection con = new SqlConnection();  
   DataSet ds = new DataSet();  
   SqlDataAdapter adapt;  
   protected void Page_Load(object sender, EventArgs e)  
   {  
     retrievedata("tbltablename", "clmcolumnname");  
     remove("tbltablename", "clmcolumnname");  
   }  
   protected void retrievedata(string tablename, string columnname)  
   {  
     con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;  
     con.Open();  
     string squery = " Select " + columnname + " from " + tablename;  
     adapt = new SqlDataAdapter(squery, con);  
     adapt.Fill(ds);  
     con.Close();  
   }  
   protected void remove(string tablename, string columnname)  
   {  
     using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myconn"].ConnectionString))  
     {  
       int colcount = ds.Tables[0].Rows.Count;  
       int j = 0;  
       while (colcount > 0)  
       {  
         string injecteddata= Convert.ToString(ds.Tables[0].Rows[j][0]);  
         if (!injecteddata.IndexOf('<') < 0)  
          {  
           string datapart= "";  
           if (injecteddata.IndexOf('<') > 0)  
           {  
              datapart = injecteddata.Substring(0, injecteddata.IndexOf('<'));  
           }  
           string newqry = "UPDATE " + tablename + " SET " + columnname + "= @datapart WHERE " + columnname + " = @injecteddata";  
           SqlCommand command = new SqlCommand(newqry, connection);  
           command.Parameters.Add("@datapart", SqlDbType.VarChar);  
           command.Parameters["@datapart"].Value = datapart;  
           command.Parameters.Add("@injecteddata", SqlDbType.VarChar);  
           command.Parameters["@injecteddata"].Value = injecteddata;  
           try  
           {  
             connection.Open();  
             Int32 rowsAffected = command.ExecuteNonQuery();             
           }  
           catch (Exception ex)  
           {  
             Console.WriteLine(ex.Message);              
           }  
           finally  
               {  
                 connection.Close();  
               }  
         }  
         j++;  
         colcount--;  
       }  
     }  
   }  
 }