An answer to another question touched on the use of writing custom plugins as a more robust alternative to writing a SQL query in a template via the {exp:query} tag.
{exp:query} tag?Extra credit for code examples!
The advantage of using a custom plugin (per-project usually) is that you will invariably come up with several small utility tasks to perform during the course of development (not just query-related). It's far cleaner to house these functions under one roof, in one file, rather than sprinkle your templates with custom queries, PHP blocks, and your /third_party folder with a half dozen simple plugins that do one tiny thing.
I would argue strongly in favour of keeping all custom functionality for a site within one package (if not one plugin ... sometimes you'll need an extension as well, and in more complex cases a module). So much less clutter, plus you can use class variables for commonly-used data throughout all of your custom functions (say, an API key if you're making custom API calls, etc).
As for documentation - using the plugin usage() function is the way to go for plugins - you can document everything and have it visible right from the Plugins screen in EE. For your extensions and modules, you can use PHPDoc with each function, or just add paragraph-style comments with each function to explain what your code does, and then optionally add EE comments in your templates wherever you call your custom functions so that folks know that it's a) custom, and b) what it does.
The disadvantage of going the custom add-on route would be if you, say, only have a couple of custom queries across your site, and no need for custom helper functions - in which case sticking with the Query module omits any overhead from loading another class (i.e., your custom plugin) needlessly. Plus - no need to write a custom plugin!
It is also important to note that the Query Module is not safe if you are allowing any user-entered data. The only thing the Query Module does, besides adjusting for pagination if present, is passes the string through to the CI db class (in EE 2.5.3 this is line 181 of /system/expressionengine/modules/query/mod.query.php )
$query = $this->EE->db->query($sql);
The CodeIgniter documentation specifically states that you should escape your queries using one of the escape functions (see this page: http://codeigniter.com/user_guide/database/queries.html); however, there is NO escaping being done in the EE core code.
The way around this is to use a custom addon, and ensure that you escape your queries (or use CI's Active Record, which does automatic escaping) within your addon methods.
