Open command doesn't like non escaped characters even between quotes?
It's not open, it's the fact that ~ only works when it's not in quotes. ~ isn't actually part of a standard file path, it's a shell metacharacter that the shell replaces in contexts where it looks like the beginning of a path (and quotes suppress this). Here are some examples:

$ echo ~
/Users/gordon
$ echo "~"
~
$ ls ~/Library
Accounts        Fonts Disabled      Printers
[...]
$ ls "~/Library"
ls: ~/Library: No such file or directory


Note the last command above: since ~ is inside double-quotes, it doesn't get replaced by the path to my home folder, and then ls just treats is as a normal filename (that happens not to exist). The same thing is happening in your open command.

Since the file you're actually trying to open has other shell metacharacters in its name, what you should do is partially-quote the path: leave the ~ outside the quotes, but make sure the filename part is inside quotes. Something like this:

open ~/"Library/Messages/Attachments/87/07/079C263B-0586-48C6-B721-3C6AABAC76DF/Messages Image(1020754653).jpeg"