Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

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

share|improve this question
    
Do you think you could rephrase the question in such a way that it isn't asking for an opinion? Polling for opinions doesn't fit well into the Q&A format of Stack Exchange. – user40980 Oct 16 '13 at 14:14
    
You can always abstract your "Micro ORM" queries in a separate class, where when necessary you swap the queries being executed as you change your DBMS. – CodeCaster Oct 16 '13 at 14:57
    
haven't heard the term "Micro ORM". do you mean ORMs that don't attempt to completely take over SQL, or is it something different? – Javier Oct 16 '13 at 15:06
    
nevermind, after a little googling, it seems to be a .net thing. – Javier Oct 16 '13 at 15:07
1  
@GrandmasterB: Explain why in an answer. I've pretty much sidestepped that issue in my answer, since I believe that it is a question of tradeoffs. – Robert Harvey Oct 16 '13 at 17:16
up vote 25 down vote accepted

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.

share|improve this answer
    
Good examples, I did already mention that you can sidestep the SQL injection via parameters, as this is just one piece of the larger question, have also added an edit clarifying what I am meaning when I use the term inline sql. – Grofit Oct 16 '13 at 15:47
    
I've added some detail to my answer. – Robert Harvey Oct 16 '13 at 16:37
1  
+1 for information on SimpleCRUD didn't know this existed. – Grofit Oct 17 '13 at 7:51
    
In Java you will find Mybatis which is ligther than Hibernate or EclipseLink and. Its way to go is by predefined sentences in XML (instead of hardcoding on code). It also add some interesting features as preconditions to enhance the final SQL. We used recently into a API web that has quite high concurrency and a not complex business and it worked great. – Laiv 8 hours ago

Parameterized queries as well as the repository design pattern give you full control over what goes into the query to prevent injection attacks. Inline SQL in this case is not an issue other than readability for large and complex queries, which should be in stored procedures anyway.

share|improve this answer

I love sql, and couldn't stand seeing it chopped up in string literals, so I wrote a little VS extension to let you work with real sql files in c# projects. Editing sql in its own file gives you intellisense for columns and tables, syntax validation, test execution, execution plans and the rest. When you save the file, my extension generates c# wrapper classes, so you never write a line of connection code, or command code, or parameter or reader code. Your queries are all parameterized because there's no other way, and you have generated repositories and POCOs for unit testing, with intellisense for your input parameters and your results.

And I've thrown in free steak knives... When an expert needs to come and rework your developer's sql, she's looking at a real sql file, and doesn't need to touch any C#. When she goes back and tidies up the DB, deleting some columns, references to missing columns jump straight out as compile errors in the c#. The database becomes just like another project in your solution that you can hack into, it's interface discoverable in code. If the solution compiles, you know that all your queries are working. There are no runtime errors due to invalid casts or invalid column names, or invalid queries.

Lookin back at dapper, which we're still using at work, I can't believe that in 2016 this is the coolest thing in data access. Runtime msil generation is clever n all, but all the errors are runtime errors, and string manipulation, like string manipulation for any other purpose, is time consuming, fragile and error prone. And how can it be Dry to have to repeat your column names in your POCO, which you have to write and maintain by hand.

So there you go. If you want to look at an unkown data-access technology from an unheard of developer working in his spare time (with a young child and lots of other hobbies), it's over here.

share|improve this answer
    
You seem to think a lot of your little "innovation," but how is this any different than, say, ExecuteStoreQuery or ExecuteStoreCommand in Entity Framework? – Robert Harvey 12 hours ago
    
I'm not getting into the EF or SQL debate. My thing is for folk who want to use SQL. So as a way of executing SQL, ExecuteStoreQuery() will fill your POCOs for you, but you still need to define your POCOs, by the look of things? And it does nothing to help you put the sql in a file, or create your parameters. Your query is not discoverable in code, or testable. Deleting columns will not produce compile errors in your application. I could go on, but I fear I'm repeating myself :-) Perhaps you could take 3mins to watch the youtube video. It's in French, turn the sound down ! English coming. – user1585345 12 hours ago
    
EF is quite capable of generating all of the POCOs automatically. What am I missing here? The real innovation of Dapper and Massive is that you don't need POCO's at all; you can just use dynamic. – Robert Harvey 12 hours ago
    
I know almost nothing about EF. ExecuteStoreQuery() will fill an entity. Will it generate an entity from the query. Entities are generated from DB objects (or vice versa), not from queries. If your query doesn't correspond to an existing entity, you have to write your POCO, no? – user1585345 12 hours ago
    
That's true. I'll look at your code when I have some time; that particular capability might prove useful to me. – Robert Harvey 12 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.