CS.SQL.INJECT.LOCAL

SQL injection vulnerability. When a SQL statement is created by using unvalidated input, it is possible for a malicious user to inject a string and execute arbitrary SQL statements with the privileges of the attacked application on the database used by this application.

Vulnerability and risk

When input to code is not validated properly, an attacker can craft the input in a form that will allow the attacker to execute arbitrary SQL statements. With this sort of opportunity, an attacker could:

  • read confidential data in the application's database;
  • modify data in the application's database;
  • execute arbitrary commands such as deleting all the information in the database.

To avoid this issue, it is best to:

  • use only constant strings when creating SQL statement;
  • use safe libraries to create parameterized SQL statements that use inputs;
  • add validation code before using inputs in a SQL statement.

Vulnerable code example

1  public static void DeleteUser(string username, string connectionString)
2  {
3      using (SqlConnection connection = new SqlConnection(connectionString))
4      {
5          string sqlQuery = String.Format("DELETE FROM Users WHERE UserName='{0}'", username);
6          SqlCommand command = new SqlCommand(sqlQuery, connection);
7          command.Connection.Open();
8          command.ExecuteNonQuery();
9      }
10  }

Klocwork produces a SQL injection report for line 8 indicating that an unsafe SQL query string has been used as a command. A SQL injection in this case could happen using the input “; DROP TABLE Users; --” that will delete all the users from the database instead of only deleting the users that have an exact name of username.

Fixed code example

1  public static void DeleteUser(string username, string connectionString)
2  {
3      using (SqlConnection connection = new SqlConnection(connectionString))
4      {
5          string sqlQuery = "DELETE FROM Users WHERE UserName=@Username";
6          SqlCommand command = new SqlCommand(sqlQuery, connection);
7          command.Parameters.Add(new SqlParameter("@Username", username));
8          command.Connection.Open();
9          command.ExecuteNonQuery();
10      }
11  }

The problem from the previous snippet is fixed: the username is now used to create the SQL statement using a safe library call. This call will validate the input and transform it to prevent the SQL injection.