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?

Jeff B · Accepted Answer · 2012-12-05 21:02:10Z

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 5, 2012 at 21:02

Jeff B

9,14220 gold badges68 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cdub Over a year ago

how do i make it so the type is passed too, like SqlDBType.NVarchar?

Jeff B Over a year ago

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: msdn.microsoft.com/en-us/library/0881fz2y.aspx

Justin Pihony · Accepted Answer · 2012-12-05 20:59:39Z

1

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);

answered Dec 5, 2012 at 20:59

Justin Pihony

67.2k20 gold badges154 silver badges185 bronze badges

2 Comments

cdub Over a year ago

what is they are of different types?

cdub Over a year ago

i would also want to pass different types, maybe I should change it to a dictionary?

Collectives™ on Stack Overflow

C# and SQL parameters to prevent injections

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related