0

I have heard that I can prevent SQL injection attacks by using parameterized queries, but I do not know how to write them.

How would I write the following as a parameterized query?

SqlConnection con = new SqlConnection(
    "Data Source=" + globalvariables.hosttxt + "," + globalvariables.porttxt + "\\SQLEXPRESS;" + 
    "Database=ha;" + 
    "Persist Security Info=false;" + 
    "UID='" + globalvariables.user + "';" + 
    "PWD='" + globalvariables.psw + "'");

string query = "SELECT distinct ha FROM app WHERE 1+1=2";

if (comboBox1.Text != "")
{
    query += " AND firma = '" + comboBox1.Text + "'";
}

if (comboBox2.Text != "")
{
    query += " AND type = '" + comboBox2.Text + "'";
}

if (comboBox3.Text != "")
{
    query += " AND farve = '" + comboBox3.Text + "'";
}

SqlCommand mySqlCmd = con.CreateCommand();
mySqlCmd.CommandText = query;

con.Open();
…
stakx - no longer contributing
  • 83,039
  • 20
  • 168
  • 268
user3888775
  • 113
  • 3
  • 16

2 Answers2

2

You need to use parameters instead of just concatenating together your SQL:

using (SqlConnection con = new SqlConnection(--your-connection-string--))
using (SqlCommand cmd = new SqlCommand(con))
{
    string query = "SELECT distinct ha FROM app WHERE 1+1=2";

    if (comboBox1.Text != "")
    {
        // add an expression with a parameter
        query += " AND firma = @value1 ";

        // add parameter and value to the SqlCommand
        cmd.Parameters.Add("@value1", SqlDbType.VarChar, 100).Value = comboBox1.Text; 
    }

    .... and so on for all the various parameters you want to add

    cmd.CommandText = query;

    con.Open();

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
         while(reader.Read())
         {
             // do something with reader -read values 
         }

         reader.Close();
    }

    con.Close();
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

instead of comboBox1.Text use parameters like @firma

command.Parameters.Add("@firma", SqlDbType.Varchar);
command.Parameters["@firma"].Value = comboBox1.Text;

 query += " AND firma = @firma ";

apply this to all parameters

Dgan
  • 10,077
  • 1
  • 29
  • 51
  • OK - now as a next step, I'd recommend to always define an explicit **length** : `command.Parameters.Add("@firma", SqlDbType.Varchar, 100);` – marc_s Sep 13 '14 at 08:26
  • due to this is there will be any difference in Query Execution Plan?? or Performance ?? – Dgan Sep 13 '14 at 08:53
  • No, it doesn't have any impact on performance - but it might save your from experiencing an unexpected, unpleasant surprise when - without specifying a length explicitly - a default length of e.g. 1 character will be used ..... – marc_s Sep 13 '14 at 11:33