Tag Archives: Querying

Avoiding SQL injection

During a code review the other day, I encountered an example of how it is still trivial to write queries which are susceptible to SQL injection.

Take the following example:

var query = new SqlQuery(
    string.Format(
      "SELECT * FROM [Documents] WHERE DocumentName = '{0}' AND [Searched] = 1",
      criteria.DocumentName));

The fact that you are doing string.Format is a strong indication that you are making a mistake which could lead to SQL injection.

You should always use parameterised queries, all the examples for MicroLite are shown in this way and we provide a powerful fluent SQL Builder to allow you to easily construct more complex queries. There will be cases where using the SQL Builder, but even in those situations do not concatenate inline SQL.

The correct way to create the above query would be:

var query = new SqlQuery(
      "SELECT * FROM [Documents] WHERE DocumentName = @p0 AND [Searched] = @p1",
      criteria.DocumentName,
      true);

The lesson here is “just because you are using an ORM, you are not guaranteed to be safe from SQL injection”.

Advertisements

Upgrading to MicroLite 6.1

There are a couple of changes in MicroLite 6.1 which you need to be aware of when updating from a previous version.

Listeners

In MicroLite 6.1, we have split the IListener interface into 3:

IDeleteListener containing the following methods:

void AfterDelete(object instance, int rowsAffected);
void BeforeDelete(object instance);

IInsertListener containing the following methods:

void AfterInsert(object instance, object executeScalarResult);
void BeforeInsert(object instance);

IUpdateListener containing the following methods:

void AfterUpdate(object instance, int rowsAffected);
void BeforeUpdate(object instance);

You can create as many listeners as you like implementing all or some of the above interfaces and register them in the appropriate collections.

Example – Create a listener which only cares about insert operations:

public class InsertOnlyListener : IInsertListener
{
   public void AfterInsert(object instance, int rowsAffected) { ... }
   public void BeforeInsert(object instance) { ... }
}

Listener.InsertListeners.Add(new MyInsertListener());

Example – Create a listener which cares about insert and update operations:

public class AuditListener : IInsertListener, IUpdateListener
{
   public void AfterInsert(object instance, int rowsAffected) { ... }
   public void AfterUpdate(object instance, int rowsAffected) { ... }
   public void BeforeInsert(object instance) { ... }
   public void BeforeUpdate(object instance) { ... }
}

var auditListener = new AuditListener();
Listener.InsertListeners.Add(auditListener);
Listener.UpdateListeners.Add(auditListener);

SQL Builder

The “write” builders (SqlBuilder.Delete and SqlBuilder.Update) now offer the same functionality for single columns as the read builder (SqlBuilder.Select):

  • Between(object lower, object upper);
  • In(params object[] args);
  • In(params SqlQuery[] subQueries);
  • In(SqlQuery subQuery);
  • IsEqualTo(object comparisonValue);
  • IsGreaterThan(object comparisonValue);
  • IsGreaterThanOrEqualTo(object comparisonValue);
  • IsLessThan(object comparisonValue);
  • IsLessThanOrEqualTo(object comparisonValue);
  • IsLike(object comparisonValue);
  • IsNotEqualTo(object comparisonValue);
  • IsNotLike(object comparisonValue);
  • IsNotNull();
  • IsNull();
  • NotBetween(object lower, object upper);
  • NotIn(params object[] args);
  • NotIn(params SqlQuery[] subQueries);
  • NotIn(SqlQuery subQuery);

Introducing the SqlBuilder

To make it easier to create an SqlQuery, you can use the MicroLite.Query.SqlBuilder class.

The SqlBuilder uses a fluent interface to help you create an SqlQuery.

A basic example:

var query = SqlBuilder.Select("CustomerId", "Name", "DoB", "StatusId")
    .From("Customers")
    .ToSqlQuery();

A parameterised example:

var query = SqlBuilder.Select("CustomerId", "Name", "DoB", "StatusId")
    .From("Customers")
    .Where("DoB > @p0", new DateTime(1980, 1, 1))
    .ToSqlQuery();

A slightly more complex example:

var query = SqlBuilder.Select("CustomerId", "Name", "DoB", "StatusId")
    .From("Customers")
    .Where("Name LIKE @p0", "%John Smith%")
    .AndWhere("StatusId = @p0", CustomerStatus.Active)
    .OrderByAscending("DoB")
    .ToSqlQuery();

There is also a convenience method to pre specify all columns and the table name, it shortcuts the Select() and From() methods so you can add where and order by statements after.

var query = SqlBuilder.SelectFrom(typeof(Customer)).ToSqlQuery();

var query = SqlBuilder.SelectFrom(typeof(Customer)).Where("DoB > @p0", new DateTime(1980, 1, 1)).ToSqlQuery();

There are a few things to note and understand about the SqlBuilder.

  • Although we have gone through the fluent api in a single chain, we could pass the result of Select().From().Where() to other methods to conditionally append values.
  • The fluent API will return the valid options after each method call, when you have built up the query, call .ToSqlQuery() to return the query as an SqlQuery.
  • The parameters are named @pX where X is the zero indexed position of the argument value in the argument parameters.
  • Each time we add to the where clause, we start at zero again.
  • We can refer to the same argument value multiple times
  • var customerName = !string.IsNullOrEmpty(customerNameTextBox.Text) ? customerNameTextBox.Text : null;
    
    .Where("Name LIKE @p0 OR @p0 IS NULL", customerName)
    

.AndWhere() will AND the criteria, there is also an .OrWhere() which will OR the criteria however it is important to understand that these are all appended.

So the following query

.Where("Name = @p0", "Fred")
.AndWhere("StatusId = @p0", CustomerStatus.Active)
.OrWhere("StatusId = @p0", CustomerStatus.Disabled)

would produce the following SQL:

WHERE (Name = @p0) AND (StatusId = @p1) OR (StatusId = @p2)

If you wanted to produce

WHERE (Name = @p0) AND (StatusId = @p1 OR StatusId = @p2)

you would need to construct the query as follows:

.Where("Name = @p0", "Fred")
.AndWhere("StatusId = @p0 OR StatusId = @p1", CustomerStatus.Active, CustomerStatus.Disabled)

This article was written for version 1.2.2 of the MicroLite framework.

Querying

MicroLite contains a class called SqlQuery which is used to represent the command text and argument values for a command.

A basic query with no parameters.

var query = new SqlQuery("SELECT [CustomerId], [Name], [DoB], [StatusId] FROM [Customers]");

A parameterised query.

var query = new SqlQuery("SELECT [CustomerId], [Name], [DoB], [StatusId] FROM [Customers] WHERE [Dob] > @p0", new DateTime(1980, 1, 1));

Note – Objects will be populated based upon the values in the select list which allows you to choose which properties are populated, however take care if you then want to issue an update based upon a partially read object as it could result in data loss (e.g. nulling or zeroing existing values).

A query which executes a stored procedure. The syntax has been simplified, you just add the parameter names and the values in the order specified in the query text.

var query = new SqlQuery("EXEC CustomersByStatus @StatusId", CustomerStatus.Active);

The recommended approach for queries is to create a class that contains them per type being queried. This allows you to maintain them easily and easily see what queries you already have to promote re-use and reduce duplication. It also makes it easier to unit test code to ensure that the correct query is being used.

public static class CustomerQueries
{
    public static SqlQuery FindByStatus(CustomerStatus customerStatus)
    {
        return new SqlQuery("SELECT [CustomerId], [Name], [DoB], [StatusId] FROM [Customers] WHERE [StatusID] = @p0", customerStatus);
    }

    ...
}

To use it, you can then call

var query = CustomerQueries.FindByStatus(CustomerStatus.Active);

This article was written for version 1.2.2 of the MicroLite framework.