CODEWITHSUNDEEP
c#sqlsql-serverparameters
Thomas Wormald
Thomas Wormald

Reputation: 187

SQL Parameters in C# aren't working as expected

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?

Upvotes: 1

Views: 725

Answers (1)

Haney
Haney

Reputation: 34762

Your parameters are:

@dataToFind
@name
@fieldToCheck
@fieldToReturn

Your Query's CommandText has:

@fieldToReturn
@tableName
@fieldToCheck
@dataToReturn

These do not match. They must match in order to be properly applied.

Upvotes: 1

Related Questions