Recursively get ids of tree
Basically, you need to move your @starting_id predicate to the base case of the recursive part:
WITH chainIDsUpwards AS
(
    SELECT id, parent_id FROM foo
    WHERE id = @starting_id

    UNION ALL

    SELECT foo.id, foo.parent_id FROM foo
    JOIN chainIDsUpwards p ON foo.id = p.parent_id
)
SELECT * FROM chainIDsUpwards;

If you put the predicate as:
SELECT * FROM chainIDsUpwards WHERE ...;

it will filter all rows that do not satisfy the predicate.
I also believe that you need to change the join clause in both cte:s if you want the behavior to correspond with the name. I find it easier mentally to start with the cte in the join like:
SELECT foo.id, foo.parent_id 
FROM chainIDsUpwards p
JOIN foo 
    ON p.parent_id = foo.id

Fiddle