I really have never understood the ORM hate. I've found them to be immensely useful in 99% of circumstances, and for the remaining 1%, a good ORM will always let you fall back to raw SQL. Aside from providing a simpler syntax for performing basic queries, there are a few features that ORMs provide that have greatly simplified my life:

1. Automatically using prepared statements and validating/escaping query arguments to prevent injection. You have to be quite a bit more careful when you're working with raw SQL.

2. Providing an clean API to construct complex queries.

This typically becomes an issue when you have a query where you are filtering and/or sorting by multiple fields which are specified by the user, some of which are conditional. If you're writing raw SQL you end up needing to do a lot of string manipulation which can get fairly messy (and makes the code more difficult to understand). An ORM which provides some sort of query builder syntax that lets you do:

if (some condition):
query.addWhere(clause)
if (some other condition):
query.addWhere(other clause)
etc.

is pretty convenient.

3. Collating repeated result rows from joins

Whenever you're working with joins you end up with repeated data in your result set, which you generally end up having to collate before display. For example if I have Recipes <- (many to many) -> Categories and do a query to load the both of them, I might end up with something like this:

recipe_id | recipe_name | recipe_ingredients | category_id | category_name
--------------------------------------------------------------------------
1 | Shrimp Pasta | 1 cup tomato sauce | 1 | Pasta
1 | Shrimp Pasta | 1 cup tomato sauce | 2 | Seafood
1 | Shrimp Pasta | 1 cup tomato sauce | 3 | Shrimp
2 | Fruit Cake | 4 cups flour... | 4 | Dessert
2 | Fruit Cake | 4 cups flour... | 5 | Cakes

Without an ORM I have to loop through the result set to re-format the data the way I wanted before displaying it. An ORM takes care of that for me and gives me back 2 recipes with their categories accessible via recipe.categories.

4. Simplified manipulation of many-to-many relationships. Following the above example, if I want to add a new Category to a Recipe I can simply do:

recipe.categories.add(category)

If I want to set the categories to something entirely different, I can do:

recipe.categories = [category1, category2, category3, etc]

Without the ORM I would have to manually sync up the entries in the join table which is kind of a pain in the ass. Working with join tables in general is rather obnoxious, so I'm quite glad that the ORM takes care of that one for me.

5. Some ORMs give you notifications when an object (or collection of objects) changes. This is pretty important on the client side when you want to make sure the data you're displaying stays up to date, even as it is being manipulated. For example: if I'm viewing a recipe on my iPad and I update that recipe on my desktop. A background thread is running which keeps the two synchronized, and at some point the underlying recipe is updated in the database on my iPad.

If I'm working with raw SQL there's basically no way to know when that object is changed (short of polling it periodically, or rolling your own notification system). But my ORM will keep me notified of changes to the object so I can refresh the user interface with the updated recipe after the sync completes.

6. Some ORMs implement a unit of work that allows you to track what changes have been made to an object since it was retrieved from the database. So you can easily see which fields have been modified, and then when you go to save the object back out, it will intelligently only issue the SQL to update the columns which have changed, or won't even touch the database if nothing has actually changed.

7. Some ORMs put their objects into an identity map, so if you query for the same object under multiple different scenarios (e.g different areas of your UI), you always get the same underlying instance back. This means that you don't have multiple copies of what is semantically the same object floating around in different places of your app, and the object is always up to date with the latest changes.

Note: my use case is typically client-side database backed software, so features like (5), (6), and (7) save me from having to do a TON of work. If you're doing more web oriented stuff, I can see how those particular features may be less useful to you. Still, I think ORMs are a huge win overall.

Of course, none of this absolves you from needing to know what's going on at the database level. You still have to know what SQL your ORM is generating in order to make sure you're using it correctly. But I get really confused when people badmouth ORMs and try to tell me that it's simpler to use raw SQL, cause it never has been for me and my use cases. And the funniest thing is, if I stuck with raw SQL while attempting to solve all of the problems I listed above, I probably would end up with a half-assed version of a full-fledged ORM anyway.

(The only use case I can think of where I'd prefer to use raw SQL over an ORM is with report generation type activities: usually those types of queries aren't very dynamic, they can often be too complex to be expressed via the ORM's API, and once you have the data you're just dumping it to display without worrying about interactivity anyway.)