Drupal's Views module has a lot of hooks that can be used to modify the behavior of a hook, from building the query to putting together the output. Hooks are also used by modules which want to add to the Views building interface, even the basic node-based Views.

Views supports a number of hooks which aren't documented. One I came across recently was hook_views_query_substitutions. This is potentially a powerful hook, one which the module Views arguments in filters (currently a sandbox module only for developers) takes advantage of to allow filter value substitution from the Views UI.

Hook hook_views_query_substitutions is pretty basic. By implementing the hook, you return an array of values you want to substitute with the keys you want to substitute for. This is how the magic values like ***ADMINISTER_NODES*** which you see in the Views query preview get turned into valid SQL.

For example:

function user_views_query_substitutions($view) {
  global $user;
  return array('***CURRENT_USER***' => intval($user->uid));
}

After this hook is called, Views will then run str_replace with the keys of the substitutions array and its values and apply it to the breadcrumb, title, and views arguments, as well as the query string that has been built so far.

Hook hook_views_query_substitutions gets called in the execute() call of Views. The first thing execute() does is build(), which you can hook into with hook_views_pre_build, hook_views_query_alter, and views_post_build (in that order), then you can hook into hook_views_pre_execute and hook_views_post_execute (the latter happens after the views query is executed and shows an unsubstituted query, there is no hook where one can see the substituted query). You also have a chance to hook into the db_rewrite_sql call.

Module "Views arguments in filters" is pretty basic and only allows substitution in filters which allow you to enter open text (so that you can set a value like '%1'). But by doing something similar, we can use a string passed in as a Null argument to command a Drupal module to do even more, like string substitution on any part of the query. I have a module in development now that does exactly that.