Generic way of adding SqlParameter to the SqlCommand

Question

We have a legacy C# application where we have a lot of inline SQL queries that are being executed against the input that was passed by the user. So, obviously SQL injection came into the picture. Now we want to fix it but the thing is we want to go with minimalistic approach. Not touching to much code. So ORM and stored procedures are kind of out of the equation for us.

We have to update every inline statement to make use of SQL parameterization approach. However, what I am looking for is may be if there is a generic way of doing it. Like send the query to a method and generate the SqlParameter array dynamically.

By query I mean literally the query whenever I can without using the conventional @

Select * 
From table 
Where id = 1 and name = 'Sean'  and            
location like  '%cali'

To something like this

List<Sqlparameters> params = new 
List<Sqlparameters>() 

var query = Select * from table where id = @v1 and name =@v2 and location like @v3

params.Add("v1", 1)
params.Add("v2",  'Sean')
params.Add("v3","%cali")

Update

Now, I have a method which does this for me. This will take the sql text like this

var sql = "select * from merchants where merchantID={" + Request["merchantid"] +"}"

Method

Public command Method(string query) 
{
var cmd =new Command();
cmd.text="";
cmd.params=new List<SqlParameter>() ;


// code to trasform the query. Identify values based on the special char '{'. Dynamically adds placeholder variables and values into an array

cmd.text="select * from merchants where merchantID=@variable1

//Loops through variables and adds values
// to parameters 

cmd.parameters.Add(new SqlParameter("variable1",  value1);

return cmd;

}

Sure there is, but can you post an example of one of your more complex SQL queries? — Jon
– Jon, Commented Apr 23, 2019 at 17:10
Can you provide a more elaborate example, showing all types of parameters you are dealing with? Your example shows only a parameter of type int. I guess that you also have parameters of type string and date (at least). — Theodor Zoulias
– Theodor Zoulias, Commented Apr 23, 2019 at 19:16
Sure. The point of SQL parameterization is to prevent attackers from forming malicious queries by ensuring that their input will be parsed as literal data, and not as SQL syntax. It is harmless to store the string "DROP TABLE Users" in a record in the database, but it is catastrophic if the same string is executed as SQL command by the RDBMS engine. In your case the injection has already been performed at this stage, and parameterizing will not make any difference. — Theodor Zoulias
– Theodor Zoulias, Commented Apr 24, 2019 at 4:16
In case you have an urgent need to raise some kind of defense against SQL injection attacks, and not being 100% protected is acceptable, I would suggest going the route of black-listing keywords associated with the most dangerous types of attacks. Keywords like DROP, EXEC, EXECUTE, sysobjects, syscolumns etc, and also UPDATE and DELETE not in the start of the query. It is not safe, and it creates a false sense of security, but it is better than nothing. This technique is so outdated that you will find more easily such code for ASP/VBScript than for modern languages though. — Theodor Zoulias
– Theodor Zoulias, Commented Apr 24, 2019 at 4:51

Jon · Accepted Answer · 2019-04-23 17:46:41Z

2

I wrote a quick generic method that will do what you want. It uses Regular Expressions to find all the parameters (they must be prefixed with the @ character)

    // This will build a SqlCommand from query text, and build SqlParameters
    // foreach "@Param" in the query e.g. WHERE Name = @Name and Date = @Date
    private SqlCommand GenerateSqlCommand(string queryText, params object[] paramValues)
    {
        // Build SqlCommand
        var sqlCommand = new SqlCommand(queryText);
        sqlCommand.CommandType = System.Data.CommandType.Text;

        // Find all instances of @Param sql parameter names in the query
        var matches = Regex.Matches(queryText, @"[@#]\w+");
        for (int i = 0; i < matches.Count; i ++)
        {
            // Add this parameter to the command with the value from the paramValues
            // Parameters passed into the method must be in order 
            // E.g. if the Query is "SELECT * FROM TABLE where Name = @Name and Date = @Date
            // then paramValues must be { 'My Name', '04-24-2019' }
            sqlCommand.Parameters.AddWithValue(matches[i].Value, paramValues[i]);
        }

        // Return command
        return sqlCommand;
    }

You can use this to build your SQL Command using the params object[] to pass in the values for each parameter in the query. An example of usage is:

        using (var conn = GetSqlConnection())
        {
            var param1 = "This is a parameter";
            var param2 = "04/23/2019";
            var param3 = 2;

            using (var comm = GenerateSqlCommand("SELECT * FROM Users WHERE Username = @Username and Date = @Date and Id = @Id", param1, param2, param3))
                {
                comm.Connection = conn;
                using (var reader = comm.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // TODO: handle the results
                        reader.GetString(0);
                        reader.GetInt32(1);
                    }
                }
            }
        }

edited Apr 23, 2019 at 17:46

answered Apr 23, 2019 at 17:20

Jon

3,2751 gold badge19 silver badges28 bronze badges

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

9 Comments

Silly Volley Over a year ago

Thanks for the quick response Jon. Where is the @ prefix being added?

Jon Over a year ago

Well I presume your SQL query will have these already prefixed in the parameters. Example of a query would be "SELECT * FROM Users WHERE Username = @Username and Active = @Active"

Jon Over a year ago

In order to use SqlCommand with parameters you must have the parameters in your query prefixed with @

Jon Over a year ago

I updated the answer to include a more clear example of a SQL query text

user47589 Over a year ago

I think your regex will match only the @, but not the rest of the variable name.

|

Alen.Toma · Accepted Answer · 2019-04-23 18:58:37Z

0

What @Jon wrote is a good solution, but just in case you want more. There is an ORM library i build that could handle almost all those possible senario.

Nuget

CodeProject

Github

Now you could simple execute an sql like this instead.

var cmd = rep.GetSqlCommand("SELECT * FROM Users WHERE UserName = String[admin]");

String[..] Date[..] and Guid[..] will be converted to a parameter later on. You could also add parameter manually to, read the documentation to understand more

answered Apr 23, 2019 at 18:58

Alen.Toma

4,8782 gold badges19 silver badges37 bronze badges

Collectives™ on Stack Overflow

Generic way of adding SqlParameter to the SqlCommand

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related