SQL parameterized query. Adding unnecessary parameters

I am using parameterized queries in a project to prevent SQL injection and I ran into an interesting query scenario. I have a query that will have more parameters multiple times than others, i.e. the where clause changes. Is there any performance or other difference between the two blocks of code? This code is inside the object, so "variables" are properties and both methods have access.

In this I only add parameters if the condition is met.

    public bool TestQuery()
    {
        SqlCommand command = new SqlCommand();
        string query = GetQuery(command);
        command.CommandText = query;
        //execute query and other stuff
    }
    private string GetQuery(SqlCommand command  )
    {
        StringBuilder sb = new StringBuilder("SELECT * FROM SomeTable WHERE Active = 1 ");
        if (idVariable != null)
        {
            sb.Append("AND id = @Id");
            command.Parameters.Add("@Id", SqlDbType.Int).Value = idVariable;
        }
        if (!string.IsNullOrEmpty(colorVariable))
        {
            sb.Append("AND Color = @Color");
            command.Parameters.Add("@Color", SqlDbType.NVarChar).Value = colorVariable;
        }
        if (!string.IsNullOrEmpty(sizeVariable))
        {
            sb.Append("AND Color = @Size");
            command.Parameters.Add("@Size", SqlDbType.NVarChar).Value = sizeVariable;
        }
        return sb.ToString();
    }

      

In this I add all the parameters every time and only add the where clause arguments if the condition is met.

    public bool TestQuery()
    {
        SqlCommand command = new SqlCommand(GetQuery());
        command.Parameters.Add("@Id", SqlDbType.Int).Value = idVariable;
        command.Parameters.Add("@Color", SqlDbType.NVarChar).Value = colorVariable;
        command.Parameters.Add("@Size", SqlDbType.NVarChar).Value = sizeVariable;
        //execute query and other stuff
    }
    private string GetQuery()
    {
        StringBuilder sb = new StringBuilder("SELECT * FROM SomeTable WHERE Active = 1 ");
        if (idVariable != null)
            sb.Append("AND id = @Id");
        if (!string.IsNullOrEmpty(colorVariable))
            sb.Append("AND Color = @Color");
        if (!string.IsNullOrEmpty(sizeVariable))
            sb.Append("AND Color = @Size");
        return sb.ToString();
    }

      

As per the test I did, any of these will work. I personally prefer the second one because I feel it is cleaner and easier to read, but I am wondering if there is some performance / security reason, I shouldn't add parameters that are not used and will probably be empty / empty string.

+3


source to share


1 answer


I guess I'll go with option one for the HABO comment, since the answer to the question of what doesn't really work in my situation.



0


source







All Articles