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
– Oleg, Commented Jun 16, 2015 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
– Thorsten Dittmar, Commented Jun 16, 2015 at 14:49

Fabian Bigler · Accepted Answer · 2015-06-16 14:47:09Z

5

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.

edited Jun 16, 2015 at 14:47

answered Jun 16, 2015 at 14:41

Fabian Bigler

10.9k6 gold badges52 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Po-ta-toe Over a year ago

And yet people release nhibernate and entity framework based applications to live still. Sigh.

Onur Gazioğlu · Accepted Answer · 2015-06-16 14:41:27Z

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

Collectives™ on Stack Overflow

Paramaterized Query With SQL Data Reader C#

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related