Cabal writes local tilde (`~`) directory

**Describe the bug**

Under some circumstances, cabal will write a local directory with the name `./~` when `~` is not expanded. This is clearly related to https://github.com/haskell/cabal/issues/6776, however I am opening a new issue as I think this is more dangerous than mere inconvenience.

**To Reproduce**

In contrast to the description of that issue, its reproducer:

```
$ cabal v2-install cabal-install --installdir=~/.cabal/bin
```

does not error out for me (I guess it used to?). It creates a local directory `./~`:

```
$ tree -a './~'
./~
└── .cabal
└── bin
└── cabal -> ...
```

**Expected behavior**

IMO this is dangerous because the user is now a mere typo away from accidentally deleting their home directory (this just happened to me; I was only saved by a write-protected file).

While I am sure that @phadej is correct that actual expansion requires a systematic approach (`--store-dir` is also affected), at least throwing an error when `~` is detected on a case-by-case basis would be safer than the status quo.

**System information**

- NixOS
- Cabal: `3.14` and `master`.
- GHC: `9.10.2`

**Additional context**

Incidentally, you won't see this problem with `git status` in this repository because of `*~` in `.gitignore`. Hence if you try to reproduce this in your local `cabal` copy, **be very careful cleaning up** e.g. run `ls './~'` first then replace `ls`.

Also related: https://github.com/haskell/cabal/issues/7812

Start by carefully reproducing `cabal v2-install cabal-install --installdir=~/.cabal/bin`, checking the resulting `./~` directory without deleting it. Read the related issues #6776 and #7812 for prior context. Done means the command no longer writes an unintended `./~` directory and handles the tilde path safely.

Written by the indexing model from the issue text.