Avoiding SQL Injection Attacks

SQL Injection is a technique that an attacker can use to cause malicious SQL statements to execute, using a legitimate SQL query as a channel.

How SQL injection attacks work

This attack is possible when user-provided input is not handled in a safe way (either by properly sanitizing the input or by using SQL parameters to assign variable values).

Consider a program that returns the information of a specific contact from a table called contacts. The program allows the user to provide the ID of the contact to be returned. The SQL template used by the program looks like this:

select * from contacts where id = {id}

Under normal operation, the user is expected to provide the integer ID of a contact which will then be formatted into the string. For example, if the user provides contact 1, the resultant query is:

select * from contacts where id = 1

However, if the user input is not properly sanitized, an attacker could add a SQL statement into the input. Instead of providing an integer value, a string such as the following could be provided:

1; drop table contacts

The resultant string is now the following:

select * from contacts where id = 1; drop table contacts

When this statement is executed, the contacts table will be deleted.

Using SQL parameters

The only reliable way to prevent this is to avoid using string formatting to prepare SQL statements. Instead, you should use SQL parameters to build the query. In the example above, the query should be:

select * from contacts where id = @id

@id is a SQL parameter. This value should be assigned programmatically before executing the query. In C#, the code to do this would look like this:

var command = new SqlCommand("select * from contacts where id = @id", connection); 
command.Parameters.AddWithValue("@id", idProvidedByUser);

In addition, you should sanitize the user input. In the scenario above, the user should only be providing integer ID's. Therefore, the input could be verified with an expression such as int.Parse(idProvidedByUser).

Note that sanitization is a secondary measure that will ensure your application fails more gracefully. You should still use SQL parameters to inject variable data into a query even if you have sanitized the input.

Using SQL parameters in Flowgear

Flowgear's SQL Connectors use SQL parameters correctly, thereby negating the risk of an injection attack.

These Connectors include:

When using these connectors, don't inject a value into the Query Property. Instead, specify a query that references parameters, add matching Custom Properties to the Node and then inject values into the Custom Properties via Flow Connectors.

Injection attacks aren't just for SQL

Although this article discusses SQL injection attacks, it's worth noting that whenever a document is being prepared, it is susceptible to an injection attack.

Consider a case where you're preparing a JSON payload to update a user record:

{
	"user": {
		"username": "{username}",
		"firstName":"{firstname}
		"lastName":"{lastname}"
	}		
}

An attacker could provide this value for lastname:

somelastname", isAdmin: "true

This would cause the full document to resolve to:

{
	"user": {
		"username": "someusername",
		"firstName":"somefirstname
		"lastName":"somelastname",
		"isAdmin":"true"
	}		
}

Take these steps to avoid this problem:

Whenever possible, don't translate strings into template values (i.e. avoid using Formatter) and use QuickMap instead.
If you need to use Formatter, ensure you specify the correct escaping option in the Escaping Property. Escaping ensures that control characters are handled as data. In the JSON example above, specifying JSON escaping would cause the control character " to be replaced with \" which means the quote is considered part of the string rather than being a control character.
For Nodes support Implicit Custom Properties, ensure you specify the appropriate escaping on the Custom Property overlay.