CODEWITHSUNDEEP
c#sql-server
codeBoy
codeBoy

Reputation: 533

Paramaterized Query With SQL Data Reader C#

I know that non parameterized queries are frowned upon because of SQL injection. Well, I have a lot of queries in my application that are susceptible to SQL injection. I just can't seem to wrap my head around doing it with SqlDataReader. I am able to do it with ExecuteNonQuery just not SQLDataReader.

Can someone give me some pointers and or examples of the best way to do this, the query is executing and returning exactly what it should, I just want to make it as secure as possible....

Code: 

string myQuery = "Select [shoeSize] AS 'Shoe Size', [shoeBrand] AS 'Shoe Brand' FROM [myTable] "
                 + "WHERE [customerName] = '" + customer + "' AND " + "[customerPin] = '" + customerID + "'";

 sqlCmd = new SqlCommand(myQuery, conn);
 sqlCmd.Connection.Open();
 SqlDataReader rdr2 = sqlCmd.ExecuteReader();

  if (rdr2.HasRows)
     {
        rdr2.Read();

        shoeSize= rdr2["Shoe Size"].ToString();       
        shoeBrand= rdr2["Shoe Brand"].ToString();
     }
     conn.close();

Upvotes: 3

Views: 201

Answers (2)

Fabian Bigler
Fabian Bigler

Reputation: 10915

There you go

string myQuery = "Select [shoeSize] AS 'Shoe Size', [shoeBrand] AS 'Shoe Brand' FROM [myTable] "
                 + "WHERE [customerName] = @customerName AND [customerPin] = @customerID"

 sqlCmd = new SqlCommand(myQuery, conn);
 sqlCmd.Connection.Open();
 sqlCmd.Parameters.AddWithValue("@customerName", customerName);
 sqlCmd.Parameters.AddWithValue("@customerID", customerID");
 --rest stays the same as before

Whereas @customerName and @customerID are now your parameters. So even if the customer's name should be something like "Bigler, Fabian' DROP TABLE [myTable]" it will not work. It completely removes the possibility of "evil" input changing the meaning of your query.

Non-parameterized queries are not simply 'frowned upon'. It can be disastrous for you, your company and - of course - your customer.

Upvotes: 5

Onur Gazioğlu
Onur Gazioğlu

Reputation: 511

Like this:

        string myQuery = "Select [shoeSize] AS 'Shoe Size', [shoeBrand] AS 'Shoe Brand' FROM [myTable] "
              + "WHERE [customerName] = @customerName AND [customerPin] = @customerPin";

        sqlCmd = new SqlCommand(myQuery, conn);
        sqlCmd.Connection.Open();
        sqlCmd.Parameters.Add("@customerName", SqlDbType.NVarChar, 50).Value = customer;
        sqlCmd.Parameters.Add("@customerPin", SqlDbType.NVarChar, 20).Value = customerID;
        SqlDataReader rdr2 = sqlCmd.ExecuteReader();

        if (rdr2.HasRows)
        {
            rdr2.Read();

            shoeSize = rdr2["Shoe Size"].ToString();
            shoeBrand = rdr2["Shoe Brand"].ToString();
        }
        conn.close();

Upvotes: 2

Related Questions