SQL Injection Error

Question

I am working on a project in which the client reported an SQL injection error in the code. Here is my code ...

1 public int ExecuteNonQuery(string query, SqlParameter[] parameters)
2 {
3   using (SqlCommand command = CreateCommand(query, parameters))
4   {
5       int rowsAffected = command.ExecuteNonQuery();
6       return rowsAffected;
7   }
8 }

And the method CreateCommandworks like

private SqlCommand CreateCommand(string commandText, SqlParameter[] parameters)
{
    SqlCommand retVal = this.connection.CreateCommand();
    retVal.CommandText = commandText;
    retVal.CommandTimeout = this.commandsTimeout;
    retVal.Parameters.AddRange(parameters);
    return retVal;
}

Failure is reported on line number 3 . I can’t understand what kind of attack is happening here, as this is a console application. But I have to fix the flaw, and I don't know how to fix it.

Inquiry

@"delete from {0} where runId in 
( select runId from {0}
  inner join 
    ( select sId as sId_last,
        wfId as wfId_last,
        max(runId) as runId_last from {0} where endTime is NULL
        group by sId, wfId ) t1
  on endTime is NULL and sId = sId_last and wfId = wfId_last
  and (runId <> runId_last or startTime < @aDateTime)
)";

Help evaluate. Thank.

Answer 1

You have uncovered a method publicthat can be accessed with any code that allows any SQL statement to be executed.

I would look at changing this method to internalor privateinstead, so that not only any code can call this method.

Answer 2

... , , ExecuteNonQuery, query, .

, - :

string name = ...; // A name selected by the user.
string query = "SELECT * FROM MyTable WHERE Name = '" + name + "'";

, , , .

, :

string name = // The result of a query to the db that retrieves some data
              // sadly this data has been manipulated by the attacker

string query = "SELECT * FROM MyTable WHERE Name = '" + name + "'";

, , ...

- -/ db db ( ), ... : , , , - , , . .

( ) , {0}. , ? - :

string tableName;

if (foo)
   tableName = "Foo";
else if (bar)
   tableName = "Bar";

- ?

, . "" /- , , , .

Answer 3

3:

 using (SqlCommand command = CreateCommand(query, parameters))

.

SQL- ; .

, .

. SQL , . , , SQL.

SQL . link2