Inline SQL – Is It Still Bad Practice with Micro ORMs?

databaseormsql

This is a bit of an open ended question but I wanted some opinions, as I grew up in a world where inline SQL scripts were the norm, then we were all made very aware of SQL injection based issues, and how fragile the sql was when doing string manipulations all over the place.

Then came the dawn of the ORM where you were explaining the query to the ORM and letting it generate its own SQL, which in a lot of cases was not optimal but was safe and easy. Another good thing about ORMs or database abstraction layers were that the SQL was generated with its database engine in mind, so I could use Hibernate/Nhibernate with MSSQL, MYSQL and my code never changed it was just a configuration detail.

Now fast forward to current day, where Micro ORMs seem to be winning over more developers I was wondering why we have seemingly taken a U-Turn on the whole in-line sql subject.

I must admit I do like the idea of no ORM config files and being able to write my query in a more optimal manner but it feels like I am opening myself back up to the old vulnerabilities such as SQL injection and I am also tying myself to one database engine so if I want my software to support multiple database engines I would need to do some more string hackery which seems to then start to make code unreadable and more fragile. (Just before someone mentions it I know you can use parameter based arguments with most micro orms which offers protection in most cases from sql injection)

So what are peoples opinions on this sort of thing? I am using Dapper as my Micro ORM in this instance and NHibernate as my regular ORM in this scenario, however most in each field are quite similar.

What I term as inline sql is SQL strings within source code. There used to be design debates over SQL strings in source code detracting from the fundamental intent of the logic, which is why statically typed linq style queries became so popular its still just 1 language, but with lets say C# and Sql in one page you have 2 languages intermingled in your raw source code now. Just to clarify, the SQL injection is just one of the known issues with using sql strings, I already mention you can stop this from happening with parameter based queries, however I highlight other issues with having SQL queries ingrained in your source code, such as the lack of DB Vendor abstraction as well as losing any level of compile time error capturing on string based queries, these are all issues which we managed to side step with the dawn of ORMs with their higher level querying functionality, such as HQL or LINQ etc (not all of the issues but most of them).

So I am less focused on the individual highlighted issues and more the bigger picture of is it now becoming more acceptable to have SQL strings directly in your source code again, as most Micro ORMs use this mechanism.

Here is a similar question which has a few different view points, although is more about the inline sql without the micro orm context:

https://stackoverflow.com/questions/5303746/is-inline-sql-hard-coding

Best Answer

What you are describing as "Inline SQL" should really be called "string concatenation without parameterization," and you don't have to do that to use a Micro ORM safely.

Consider this Dapper example:

string sql = "SELECT * from user_profile WHERE FirstName LIKE @name;";
var result = connection.Query<Profile>(sql, new {name = "%"+name+"%"});

It's fully parameterized, even though string concatenation is taking place. See the @ sign?

Or this example:

var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", 
          new { Age = (int?)null, Id = guid });

which is roughly equivalent to the following ADO.NET code:

List<Dog> dog = new List<Dog>();
using(var cmd = connection.CreateCommand()) {
    cmd.CommandText = "select Age = @Age, Id = @Id";
    cmd.Parameters.AddWithValue("Age", DBNull.Value);
    cmd.Parameters.AddWithValue("Id", guid);
    using(var reader = cmd.ExecuteReader()) {
        while(reader.Read()) {
            int age = reader.ReadInt32("Age");
            int id = reader.ReadInt32("Id");
            dog.Add(new Dog { Age = age, Id = id });
        }
    }
}

If you need more flexibility than this, Dapper provides SQL Templates and an AddDynamicParms() function. All SQL Injection safe.

So why use SQL strings in the first place?

Well, for the same reasons you would use custom SQL in any other ORM. Maybe the ORM is code-generating sub-optimal SQL, and you need to optimize it. Maybe you want to do something that is difficult to do in the ORM natively, like UNIONs. Or, maybe you simply want to avoid the complexity of generating all those proxy classes.

If you really don't want to write a SQL string for every CRUD method in Dapper (who does?), you can use this library:

https://github.com/ericdc1/Dapper.SimpleCRUD/

That will get you extremely simple and straightforward CRUD, while still giving you the flexibility of hand-written SQL statements. Remember, the ADO.NET example above was the way everyone did it before ORM's came along; Dapper is just a thin veneer over that.

Related Topic