Parameterized query for like clause to avoid sql injection

private void Form2_Load(object sender, EventArgs e)

{

con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\DATABASE\data.sql.mdf;Integrated Security=True;Connect Timeout=30");

con.Open();

adpt = new SqlDataAdapter(" select * from Tab ", con);

dt = new DataTable("Tab");

adpt.Fill(dt);

DataGridView.DataSource = dt;

DataGridView.Columns["Drawing"].DefaultCellStyle.ForeColor = Color.Blue;

DataGridView.Columns["Drawing"].DefaultCellStyle.Font = new Font(DataGridView.DefaultCellStyle.Font, FontStyle.Underline);

gridview_data();

}

private void TxtSearch_TextChanged(object sender, EventArgs e)

{

try

{

// = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\DATABASE\data.sql.mdf;Integrated Security=True;Connect Timeout=30");

//con.Open();

con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\DATABASE\data.sql.mdf;Integrated Security=True;Connect Timeout=30");

con.Open();

adpt = new SqlDataAdapter(" select * from Tab Where [Part_No] like '%" + TxtSearch.Text + "%' AND [Company_Name] like '%" + TextBox1.Text + "%'", con);

//adpt = new SqlDataAdapter(" select * from Tab Where [Part_No] like '%" + TxtSearch.Text + "%' AND [Company_Name] like '%" + TextBox1.Text + "%'", con);

// SqlCommand sda = new SqlCommand(" select * from Tab Where [Part_No] like '%@Part_No%' and [Company_Name] like '%@Company_Name%'", con);

//sda.Parameters.Clear();

// sda.Parameters.AddWithValue("Part_No", TxtSearch.Text );

//sda.Parameters.AddWithValue("Company_Name", TextBox1.Text );

dt = new DataTable("Tab");

adpt.Fill(dt);

DataGridView.DataSource = dt;

con.Close();

if (dt.Rows.Count <= 0)

{

DataRow row1 = dt.NewRow();

row1[0] = "No Record Found";

dt.Rows.Add(row1);

DataGridView.Rows[0].DefaultCellStyle.BackColor = Color.Yellow;

DataGridView.DefaultCellStyle.Font = new Font("Regular", 12, FontStyle.Regular);

}

else

{

DataGridView.Rows[0].DefaultCellStyle.BackColor = Color.Red;

DataGridView.DefaultCellStyle.Font = new Font("Regular", 12, FontStyle.Regular);

}

if (TextBox1.Text == "" && TxtSearch.Text == "")

{

con.Open();

adpt = new SqlDataAdapter(" select * from Tab ", con);

dt = new DataTable("Tab");

adpt.Fill(dt);

DataGridView.DataSource = dt;

con.Close();

}

please give me a query in parameterized form to avod sql injection attack.

Hi,

I have one for my self.

Function

///
/// This is extended version of selected query.
/// Here user has to pass query with parameters. ie. "select column_name from table_name where column_name=@parameter".
/// And second parameter will be two dimensional array contains first argument as passed in query ie. @parameter in above example.
///
/// Any valid selected statement with parameter name.
/// An two dimensional array of object containing parameter names and its value.ie.
/// 1st element @parametername and 2nd parameter will be value.
/// Returns datatable with result.
internal DataTable GetDataUsingQuery(string QueryWithParameters, object[,] ParameterValue)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = CONNECTION;
SqlParameter[] param = new SqlParameter[ParameterValue.GetUpperBound(0) + 1];
for (int i = 0; i < param.Length; i++)
{
param[i] = new SqlParameter("@" + ParameterValue[i, 0].ToString(), ParameterValue[i, 1]);
}
cmd.CommandText = QueryWithParameters;
cmd.Parameters.AddRange(param);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

Calling

DataTable DT = GetDataUsingQuery("select top 1 * from CHARGE_VIEW where CHARGEID=@CHARGEID order by chargeid desc", new object[,] { { "CHARGEID", ID } });

It is easy to understand and use.

Please let me know if not helpful.

Thank you.

Cheers....

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vishal Jadav8y ago
Hi,

I have one for my self.

Function
///

      /// This is extended version of selected query.
      /// Here user has to pass query with parameters. ie. "select column_name from table_name where column_name=@parameter".
      /// And second parameter will be two dimensional array contains first argument as passed in query ie. @parameter in above example.
      ///
      /// Any valid selected statement with parameter name.
      /// An two dimensional array of object containing parameter names and its value.ie.
      /// 1st element @parametername and 2nd parameter will be value.
      /// Returns datatable with result.
      internal DataTable GetDataUsingQuery(string QueryWithParameters, object[,] ParameterValue)
      {
          SqlCommand cmd = new SqlCommand();
          cmd.Connection = CONNECTION;
          SqlParameter[] param = new SqlParameter[ParameterValue.GetUpperBound(0) + 1];
          for (int i = 0; i < param.Length; i++)
          {
              param[i] = new SqlParameter("@" + ParameterValue[i, 0].ToString(), ParameterValue[i, 1]);
          }
          cmd.CommandText = QueryWithParameters;
          cmd.Parameters.AddRange(param);
          SqlDataAdapter da = new SqlDataAdapter(cmd);
          DataTable dt = new DataTable();
          da.Fill(dt);
          return dt;
      }
Calling
DataTable DT = GetDataUsingQuery("select top 1 * from CHARGE_VIEW where CHARGEID=@CHARGEID order by chargeid desc", new object[,] { { "CHARGEID", ID } });
It is easy to understand and use.
Please let me know if not helpful.

Thank you.
Cheers....