SQL injection is, perhaps, the most well-known of them all. It's based on some characteristics of the SQL language:

As you can see in the figure, you just have to pass the sentence or 1=1 -- to make it work. If the final sentence is something like Select [User] from [Users] where [Password] = whatever, although you don't include the right password, the following sentence is true, since 1 = 1 is true, and whatever the programmer put next to it is ignored due to the double dash comment. So, you're validated and you get into the system. Many other possibilities or variations are also possible but are always based on the same idea. The risk can be enormous, since they can even concatenate or delete sentences or even call stored procedures, such as xp_cmsShell, which executes sentences in the target system, thus getting total control over it.

In the worst case, it can even insert a Trojan inside the machine. Imagine the Trojan is called xp_tr.dll and that it's located in our C: emp directory. We can use a sentence like this (next to the previous code):

master..sp_addextendedproc 'xp_webserver ', 'c:	empxp_tr.dll'—

This will register our Trojan as a stored procedure, which we will call using xp_webserver, from that moment obtaining the functionality installed therein.

The defense? Don't trust any input from the user and therefore utilize a parsing mechanism that forces the coming string to be what you expect. As you can see, the problem goes beyond the type of application: it could be a desktop application or a website: the problem is always the same.

So, any data input is potentially evil. It doesn't matter who's coming from or where. That's what OWASP calls a threat agent.

The are three main strategies for defense against these kind of attacks:

Let's take a look at how the first case looks:

// Case 1
var connection = newOleDbConnection();
string query =
"SELECT account_balance FROM user_data WHERE user_name = ?";
try
{
  OleDbCommand command = newOleDbCommand(query, connection);
  command.Parameters.Add(newOleDbParameter("customerName", txtCustomerName.Text));
  OleDbDataReader reader = command.ExecuteReader();
  // …
}
catch (OleDbException ex)
{
  // Give some exception information
}

In this case, the potential dangerous parameter is created as a new OleDbParameter object, and that would not be possible if the user inserts a string not suitable for the task. This can be said for other types of parameters, such as SQLParameter if the client is SQLClient.

The second solution is to use stored procedures. As long as the programmer doesn't include any unsafe stored procedure generation, the effect of parameterized queries is the same as in the previous case.

The following code assumes that there is a SQLConnection object available and there's a stored procedure object stored in the SQL server that the connection points to, named sp_getAccountBalance. The process of the creation of a new SQLParameter object goes through a similar check as the first case:

// Case 2
try
{
  SqlCommand command = newSqlCommand("sp_getAccountBalance", connectionSQL);
  command.CommandType = CommandType.StoredProcedure;
  command.Parameters.Add(newSqlParameter("@CustomerName", txtCustomerName.Text));
  SqlDataReader reader = command.ExecuteReader();
}
catch (Exception)
{
  throw;
  // Give some excepcion information
}

The third case deals with escaping the input (or White List Input Validation), which can be done in several ways. This could be the case when the table to be used is selected dynamically by the user. The best way to avoid risks in this scenario is to provide a white list of possible values, avoiding any other input.

This is equivalent to the usage of an Enum type, specifying the possible tables that the query is going to admit:

// Case 3
String tableName = "";
switch (tableName) { 
  case"Customers":
    tableName = "Customers";
  break;
  case"Balance":
    tableName = "Balance";
  break;
  // ...                
  default: thrownewInputValidationException(
    "Invalid Table Name");
}

Besides the previous techniques, there are other specific solutions related to the distinct RDBMS. For SQL Server databases, a good article on the subject can be found at https://blogs.msdn.microsoft.com/raulga/2007/01/04/dynamic-sql-sql-injection/.

The official documentation offers some insights about possible attacks using SQL injection against non relational engines.

In the case of the MongoDB engine we examined in Chapter 7, NoSQL Database Programming, the problem arises when an attacker is able to operate on the information passed using the $where operator, including some JavaScript code that can be parsed as part of the MongoDB query.

Consider the following example in which the code is passed directly into the MongoDB query without any checking:

db.myCollection.find( { active: true, $where: function() { return obj.credits - obj.debits < $userInput; } } ); 

The trick here lies in using special characters with a special meaning to the API behind the engine. An attacker can observe if the application is sanitizing the input by checking the results on including certain characters to observe whether that triggers an error.

The injection of special characters relevant to the target API language and observation of the results may allow a tester to determine if the application correctly sanitized the input. For example, within MongoDB, if a string containing any of the following special characters (' " ; { }) was passed without control, it would trigger a database error.

Nonetheless, since JavaScript is a fully featured language, it allows an attacker to manipulate data and also run arbitrary code. Imagine the following code being inserted into the $userInput variable mentioned in the previous code:

0; var date = new Date(); do { curDate = new Date(); } while (curDate - date < 10000)

The JavaScript code will be executed…

The previously mentioned resource in OWASP will give you clues and advice about other types of injections: LDAP Injection, XML Injection, Command Injection, ORM Injection, SSI (Server-side includes) Injection, and so on.

In general, the OWASP Testing Guide v4 Table of Contents documentation (https://www.owasp.org/index.php/OWASP_Testing_Guide_v4_Table_of_Contents) of the initiative is an exhaustive and updated source to analyze and look for guidance through the amazing number of attacks related to these types of security threats.