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 works

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.

Preventing an attack

The only reliable way to prevent this vulnerability is to avoid using string formatting to prepare SQL statements. Instead, you should use SQL parameters to build the query instead. 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. It should not be the measure that you use to protect against injection attacks correctly sanitizing inputs is complex.

Preventing an attack under Flowgear

In Flowgear when using Nodes such as Microsoft SQL Query, MySQL Query, ODBC Query or OLEDB Query, avoid assigning the Query Property directly. Instead, statically define a query in the Query Property and then add Custom Properties to define SQL Parameters that will be included in the command.