C# and SQL parameters to prevent injections

Question

I am wondering how (in my code below), that I make sure that dataID is a text (or varchar or int) in the parameter passing below?

public T ExecuteQuery<T>(Func<IDataReader, T> getResult, string query, params IDataParameter[] parameters)
    {
        using (SqlConnection conn = new SqlConnection(this.DefaultConnectionString))
        {
            conn.Open();

            // Declare the parameter in the query string
            using (SqlCommand command = new SqlCommand(query, conn))
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }

                command.Prepare();

                using (SqlDataReader dr = command.ExecuteReader())
                {
                    return getResult(dr);
                }
            }
        }
    }

    public string GetMySpecId(string dataId)
    {
        return ExecuteQuery(
            dr =>
            {
                if (dr.Read())
                {
                    return dr[0].ToString();
                }

                return string.Empty;
            },
            "select specId from MyTable where dataId = @dataId",
            new SqlParameter("dataId", dataId));
    }

I am used to code like so:

command.Parameters.Add(new SqlParameter("key", SqlDbType.Text));

command.Prepare();

command.Parameters[0].Value = dataId;

How else in the code at the top, do I pass two or more parameters into the ExecuteQuery?

score 2 · Answer 1 · answered Dec 05 '12 at 21:02

2

ExecuteQuery already takes multiple parameters. It is a ParamArray which means it takes any number of parameters and turns them into an array.

For example, if I have a function like this:

public int Sum(params int[] numbersToSum) { ... }

I can call it like this:

Sum(1, 2, 5, 6, 1)

In your case you can call it this way:

ExecuteQuery(
   dr => { //snip }, 
   query,
   new SqlParameter("dataId", dataId),
   new SqlParameter("anotherParm", parm2),
   new SqlParameter("anotherParm", parm3),
   ... );

answered Dec 05 '12 at 21:02

Jeff B

8,572
17
61
140

how do i make it so the type is passed too, like SqlDBType.NVarchar? – cdub Dec 05 '12 at 21:29
Assuming you're using the the `SqlParameter` class in `System.Data.SqlClient`, that's something that you can set on the `SqlParameter` objects you are passing (the `SqlDBType` property). However, the `SqlDBType` is inferred from the .NET object given in the constructor so you don't really need to specify it: http://msdn.microsoft.com/en-us/library/0881fz2y.aspx – Jeff B Dec 05 '12 at 21:57

score 1 · Answer 2 · answered Dec 05 '12 at 20:59

The code is using the params keyword, so it seems that you should just write additional escaped SQL and keep appending the parameters to the end of the method. Here is an example using your code as a base.

The link provides a fuller explanation, but params makes it so that you can have a variable number of parameters (as long as they are the same type) at the end of a method signature. It puts all of the final parameters that fall in this params variable into an array.

 return ExecuteQuery(
        dr =>
        {
            if (dr.Read())
            {
                return dr[0].ToString();
            }

            return string.Empty;
        },
        "select specId from MyTable where dataId = @dataId and somethingelse = @else",
        new SqlParameter("dataId", dataId), 
        new SqlParameter("else", elseVar);

i would also want to pass different types, maybe I should change it to a dictionary? — cdub, Dec 05 '12 at 21:34

C# and SQL parameters to prevent injections

2 Answers2