You are correct to be suspicious and you are correct that problems arise from some of the low cell counts in this case. However, there is nothing wrong with Fisher's test itself. We just need to be careful in interpreting its results.
Let's review the data:
0 1 Total
Site 1 7 2 | 9
Site 2 95 9 | 104
Site 3 0 1 | 1
Totals 102 12 | 114
Fisher's test sums the probabilities of all configurations of the data that are (a) consistent with the row and column totals and (b) have lower probabilities than the observed table (under the null hypothesis of no column-row association).
Suppose the one result for Site 3 were not included. Fisher's test, applied to the first two rows only, gives a p-value of $0.2123$--far from "significant" evidence of any association within the first two sites. Consider now the effect of including that single value from Site 3. There are only two ways to maintain the value of $1$ for that row total: either the $1$ appears in the left column or in the right and a $0$ appears in the other entry. Because the column totals are 102 and 12, the null hypothesis suggests that the $1$ should appear in the left column with a frequency of $12/114$ and in the right column with a frequency of $102/114$. The former case actually weakens the evidence of a row-column association and so would tend to elevate the p-value, whereas the latter case--which is what actually is observed--strengthens the evidence of an association and decreases the p-value.
At this point I will make an incorrect but suggestive observation: if the p-value for the test of the first two rows actually were a probability (of the null hypothesis being true), we could update this probability (in a Bayesian sense) by multiplying the odds. The odds of the data for Site 3 are 12:102, whence
$$0.2123 / (1 - 0.2123) \times 12 / 102 = 0.0317.$$
This corresponds to a new probability or "p-value" of $0.0307$--remarkably close to the two-sided p-value of $0.0287$ obtained for the full table.
Whether we believe this intuition or not, the discrepancy in p-values is telling us that the apparently significant result for the full table is due almost entirely to the single observation obtained at Site 3.
Do you really want to draw a conclusion about the first two sites based on a single result from a third, different site? It is difficult to imagine a setting in which this would be wise. Instead, you might conclude something like this:
Almost all the data were obtained at Sites 1 and 2. Most of the observations (102 out of 114) were "zeros" (the left column's attribute). They do not show significant evidence of an association with the columns (Fisher's Exact Test, p = 0.212). A single value obtained at a third Site was one of the relatively rare "ones" (the right column's attribute). Including this observation creates the appearance of an association in the entire table (Fisher's Exact Test, p = 0.029). This may be taken as a (very) weak initial suggestion that Site 3 might differ from Sites 1 and 2 in having a greater tendency to exhibit "ones."