I think I'm making a fairly amateur mistake somewhere here, but I can't get SQL Parameters to reliably work in C#. Consider the following code:
protected string[] Query(string dataToFind, string tableName, string fieldToCheck, string fieldToReturn)
{
SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlDataReader dataReader = null;
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT " + fieldToReturn + " FROM " + tableName + " WHERE " + fieldToCheck " = '" + dataToFind "'";
try
{
connection.Open();
dataReader = command.ExecuteReader();
etc...
This executes as you would expect, returning the fieldToReturn from the table tableName. However, I understand that this is vulnerably to SQL injections, and that the correct way to avoid this is to use parameters. So I change my code to the following:
protected string[] Query(string dataToFind, string tableName, string fieldToCheck, string fieldToReturn)
{
SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlParameter[] parameters = new SqlParameter[4];
parameters[0] = new SqlParameter("@dataToFind", dataToFind);
parameters[1] = new SqlParameter("@name", tableName);
parameters[2] = new SqlParameter("@fieldToCheck", fieldToCheck);
parameters[3] = new SqlParameter("@fieldToReturn", fieldToReturn);
SqlDataReader dataReader = null;
SqlCommand command = connection.CreateCommand();
command.Parameters.AddRange(parameters);
command.CommandText = "SELECT @fieldToReturn FROM @tableName WHERE @fieldToCheck = @dataToReturn";
try
{
connection.Open();
dataReader = command.ExecuteReader();
etc...
If I have 3 matches in my database, the first code example returns 3 matches. The second code returns 0 results?!
Am I being stupid and missing something obvious?
