This begs the question, why are you using a pattern match (~~*) instead of an exact match (=) in this query? If you don't want the underscore to act as a wildcard, don't use it in a syntax where it would be interpreted as a wildcard.

select uid from user_accounts where username = ?
[download]

DBI should escape the underscore in the code above, correct?

$dbh->quote only escapes data enough to keep it from busting out of its quotes. It doesn't escape pattern-match special characters like underscores, because (a) every underscore in your database would now have a backslash in front of it when you fetched it, (b) you could never use a placeholder to replace a genuine pattern in a query.