Parameterized Query with SQL Data Reader C #
I know that non-parameterized queries are discouraged due to SQL injection. Well, I have a lot of queries in my application that are susceptible to SQL injection. I just don't feel like I'm enveloping it with SqlDataReader
. I can do it with ExecuteNonQuery
just not SqlDataReader
.
Can anyone give me some pointers and examples of the best way to do this, the query does and returns exactly what it should, I just want to make it as safe 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();
source to share
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 client's name should be something like "Bigler, Fabian" DROP TABLE [myTable], it won't work. It completely removes the possibility of "evil" input changing the value of your query.
Unparameterized queries are not just "frowned upon". This can be disastrous for you, your company and, of course, your client.
source to share
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();
source to share