SQL Parameterization versus using string.format

Question

In terms of defending against SQL injection, for simpler queries, are one of the below strategies more effective than the other?:

Using parameterization:

using (SqlCommand command = new SqlCommand(@"SELECT * FROM @table", connection))
{
    command.Parameters.AddWithValue("@table", table_name);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            ...
        }
    }
}

Using string.Format:

using (SqlCommand command = new SqlCommand(string.Format(@"SELECT * FROM {0}",table_name), connection))
using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        ...
    }
}

I would always go with parametrisation. However, the query you have above "SELECT * FROM @table" can't be parametrised like that; that's a dynamic object. Parametrising those queries is different. You can't use a variable to replace an objects name. — Thom A
– Thom A ♦, Commented Feb 22, 2019 at 15:43
@larnu - Can you expand on that a bit? What exactly do you mean by "You can't use a variable to replace an objects name."? — Mike Bruno
– Mike Bruno, Commented Feb 22, 2019 at 15:55
Exactly what it sounds like, to be honest. This db<>fiddle should explain. — Thom A
– Thom A ♦, Commented Feb 22, 2019 at 15:58
What exactly do you mean by "You can't use a variable to replace an objects name."? <= Try to run your 1st code example in a console app and see, you will get an SqlException. — Igor
– Igor, Commented Feb 22, 2019 at 15:59

Thom A · Accepted Answer · 2019-02-22 16:21:49Z

I've touched on this a little in my comments, but I'll post an answer here as well.

Parametrisation (in my opinion) is always the way to go, as it ensures the "security" of your query (it's much harder/impossible to inject into a parametrised query), and also allows for reuse of query plans, which can also be of great benefit.

For what you have here, however, you can't parametrise your SQL as you expect. A variable can't replace the name of an object (db<>fiddle). To do that you need to you dynamic SQL.

I'm not going to pretend I know C#, I don't, however, for what you have, that would be mean you have a query along the lines of "something" like:

using (SqlCommand command = new SqlCommand(@"DECLARE @SQL nvarchar(MAX) = N'SELECT * FROM ' + QUOTENAME(@table) + N';'; EXEC sp_executesql @SQL;", connection))
{
    command.Parameters.AddWithValue("@table", table_name);

Honestly, I've no idea if that works in C# that way, but that would be how you would parametrise a dynamic object in (very) simple terms.

Danielle Paquette-Harvey · Accepted Answer · 2019-02-22 15:56:39Z

1

Parametrisation is safer in terms of SQL injection. It's also better for dealing strings and dates. For example, if your string is like this :

"I haven't sleep in two days"

If you try to String.Format it in your query, you will have to double the ' character or else your query will fail. As if you parametrise it, SQL will do it by itself.

The only reason I do String.Format, is when I have for example a list of int and want to do a "WHERE COL IN () " condition. In that case I'll do a String.Format and join the List on int to generate the values inside the "IN" clause. Note that in that case, I have a list of int, so no chances of SQL injection here.

I always sometime use String.Format for dynamic SQL like specifying the name of a table, like in your example.

answered Feb 22, 2019 at 15:56

Danielle Paquette-Harvey

1,8011 gold badge17 silver badges33 bronze badges

5 Comments

Igor Over a year ago

WHERE COL IN () <= even in this case you can use parameters in as few as 2 or 3 lines of code using some Linq extensions.

Mike Bruno Over a year ago

I think I see where you're coming from. So the point of parameterization is less about controlling the actual data elements being included in the SQL command & more about eliminating opportunities to inject things like escape sequences inputted by a malicious user via the GUI. That makes sense; thanks!

Igor Over a year ago

@MikeBruno - there is more to it than that. You can better specify the data types of the incoming values so Sql Server does not have to use inferencing to figure them out. It also allows Sql Server to cache and reuse query plans and build better statistics which can help with performance.

Thom A Over a year ago

Personally, i wouldn't use String.Format for a list of values either; you should be use a table variable for things like that.

Danielle Paquette-Harvey Over a year ago

You're right that you can code the "COL IN ()" easily, it always depends on the developers. I am not the only one doing queries and most of the people do it this way for that kind of conditions so I kind of took the habit of doing it this way too ;-)

Tomasz Andrzejewski · Accepted Answer · 2019-02-22 15:55:53Z

0

always use parametrized queries to have only one instance of sql plan (just for best performance).

Parametrized queries looks like that one

select * from Table t where t.Id = @P1

answered Feb 22, 2019 at 15:55

Tomasz Andrzejewski

1363 bronze badges

Collectives™ on Stack Overflow

SQL Parameterization versus using string.format

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related