Click here to Skip to main content
15,358,946 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have an SQL query that uses string format to insert the int array(pubIds) into the query argument which is set to CommandText (As inserted below). Although this works fine, I have a fortify SQL injection vulnerability detected here and need to fix it.

C#
using (IDbCommand cmd = conn.createCommand())
{
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = string.Format(Query, string.Join(", ", pubIds))



I am trying to parameterise the query as a resolution.
Can Someone help me with it. as solutions found online are mostly using sql command. is there a diff way to solve the issue.

What I have tried:

https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause

https://stackoverflow.com/questions/20739578/pass-string-array-as-parameter-in-sql-query-in-c-sharp
Posted
Updated 29-Mar-22 2:49am

1 solution

Simple enough:
C#
using (IDbCommand cmd = conn.createCommand())
{
    List<string> parameterNames = new List<string>();
    foreach (var pubId in pubIds)
    {
        string parameterName = "@p" + parameterNames.Count);
        parameterNames.Add(parameterName);
        
        var parameter = cmd.CreateParameter();
        parameter.ParameterName = parameterName;
        parameter.Value = pubId;
        // TODO: Specify the correct type and size/precision/scale to match your database.
        cmd.Parameters.Add(parameter);
    }
    
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = string.Format(Query, string.Join(", ", parameterNames));
NB: Your scanner may still flag this code for review, depending on how clever it is. But since you're only inserting parameter names into the query, and passing all data values as parameters, the code is not obviously vulnerable.

(If you're using dynamic SQL incorrectly, the code could still be vulnerable; but without seeing the full query, there's no way to know for sure.)
   
Comments
Maciej Los 29-Mar-22 15:52pm
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900