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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s