Gendarme Rule: Avoid Unsafe SQL Queries.

[Néstor Salceda]

Title: Avoid Unsafe Sql Queries.

References:
http://www.gotdotnet.com/Team/FxCop/Docs/Rules/Security/ReviewSqlQueriesForSecurityVulnerabilities.html

Description: The rule should check that doesn't exist security
vulnerabilities in your sql code. Sometimes, I used to see some Sql
Injection attacks and this rule can be useful for this avoid this
tricks. Plase, follow the examples.

Examples:

Bad:

public void UnsafeQuery (IDbConnection connection, string name)
{
IDbCommand command = connection.CreateCommand ();
command.CommandText = "SELECT name, bank_account FROM customers WHERE
name = '" + name + "'";
IDataReader reader = command.ExecuteReader ();

while (reader.Read ()) {
PrintValue (reader.GetString (0));
PrintValue (reader.GetString (1));
}
}

Well, then we suppose the following code:

connection.Open ();

UnsafeQuery (connection, "' OR 1 = 1-");

connection.Close ();

Then the query will be: SELECT name, bank_account FROM customers WHERE
name = '' OR 1 = 1-; and we can retrieve all customer names and bank
accounts.

Then we should write:

Good:

public void SafeQuery (IDbConnection connection, string name)
{
IDbCommand command = connection.CreateCommand ();
command.CommandText = "SELECT name, bank_account FROM customers WHERE
name=@name";

IDataParameter parameter = new NpgsqlParameter ("@name",
DbType.String);
parameter.Value = name;

command.Parameters.Add (parameter);

IDataReader reader = command.ExecuteReader ();

while (reader.Read ()) {
PrintValue (reader.GetString (0));
PrintValue (reader.GetString (1));
}
}

or we can write other solutions, we can write the parameter validation
or call to a stored procedure. But the best solution IMHO is use a
parametrized query. At this moment I'm not sure if the code for the
examples is well written but I will write okey for the tests.

Any comments?

Néstor.