Category Archives: Tip

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

Understanding InvalidCastException

We can sometimes get an InvalidCastException thrown when reading a record from the database, the usual reason for this is that the database column does not match the property type on the class. For example, we have a column defined in the database as:

PhoneNumber int not null

and in the class we have

public string PhoneNumber { get; set; }

The reason this will fail is that MicroLite will read the value using the most efficient method and since the property is a string and IDataReader has a method GetString that will be called, essentially doing this:

obj.PhoneNumber = dataReader.GetString(idx);

However since the actual returned value in the IDataReader is an int which cannot be implicitly cast to a string we get an InvalidCastException. To resolve this, change the property type and database column to be compatible types (e.g. change the database column to string or change the class property to int).

Compiled Help File

Although it isn’t immediately obvious, the NuGet package of MicroLite contains a compiled help file (since release 3.0.3).

You can find it in solution folder\packages\MicroLite.x.x.x\MicroLite.chm it is built from the XML documentation (which also is used by Visual Studio to provide tool tips).

It looks like this:

MicroLite Compiled Help

It is improved with each release an contains example usage for the more commonly used methods.

Unit Testing MVC Controllers

If you use the ASP.NET MVC Extension with ASP.NET MVC 4, you may encounter problems running unit tests if you inherit from the MicroLiteController or MicroLiteReadOnlyController.

This is because the MVC Extension for MicroLite is currently built against ASP.NET MVC 3.

Since the Controller classes in ASP.NET MVC 4 are backwards compatible with MVC 3, you can fix this by adding an app.config to your unit test project with a binding redirect for ASP.NET MVC as follows:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
      <bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
    </dependentAssembly>
  </assemblyBinding>
</runtime>

MVC and WebApi extensions

If you use the MVC or WebApi extensions for MicroLite and you use an ISession or IReadOnlySession for every action (method) on your controllers, instead of applying the [MicroLiteSession] to each controller or method you can simply register it in GlobalFilters so that it is invoked for every controller action.

GlobalFilters.Filters.Add(new MicroLiteSessionAttribute());

You can still add a [MicroLiteSession] to specific controller actions if you want to use an Isolation Level or transaction style which differs from the default specified in GlobalFilters:

public class HomeController : MicroLiteController
{
    public ActionResult Index()
    {
        // For this action, this.Session will be resolved using the configuration of 
        // the MicroLiteSessionAttribute in GlobalFilters.
    }

    [MicroLiteSession(AutoManageTransaction = false)]
    public ActionResult Index2()
    {
        // For this action, we want to do something specific with the transaction so we add the
        // MicroLiteSessionAttribute to this method to override the default configuration.
        using (var transaction = this.Session.BeginTransaction())
        {
            ...
        }
    }
}