Are there any other ways of refactoring SQL-heavy code like this? In other words: is this approach reasonable, or are there pitfalls or better methods I didn't mention here?

Rather than putting just the SQL into another module, I would put all of the DB related code there using something like DBIx::AnyDBD. So rather than something like:

use StandardQueries;
...
 my $sth = $dbh->prepare(StandardQueries::get_username);
...
# some code to get the username using the statement handle
[download]

I would have

use StandardQueries;
...
my $username = $dbh->get_username($user_id);
[download]

In the first you're just hiding the SQL. In the second you're hiding the database and a lot of the schema.