Paramaterized Query With SQL Data Reader C#

Question

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();

You shouldn't use string concatenation or string formatting for generate sql query text, instead use sqlCmd.Parameters.Add(...) — Oleg, Jun 16 '15 at 14:42
@Oleg His question is how to turn non-parameterized queries into parameterized queries and you tell him not to use string concatenation/formatting to build queries? That leads me to the impression that you did either not read or understand what he's asking. — Thorsten Dittmar, Jun 16 '15 at 14:49

Fabian Bigler · Accepted Answer · 2015-06-16T14:47:09.183

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.

And yet people release nhibernate and entity framework based applications to live still. Sigh. — Po-ta-toe, Jun 16 '15 at 15:58

score 2 · Answer 2 · answered Jun 16 '15 at 14:41

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();

Paramaterized Query With SQL Data Reader C#

2 Answers2