Not quite an answer, but Cruz-Uribe and Neugebauer analyzed and obtained sharp error bounds of the trapezoidal and Simpson's rules for low regularity functions. They don't actually consider $C^2$ functions; but their results apply to the Sobolev space $W^{2,\infty}$, which is essentially the same norm you use in the $C^2$ estimate anyway. (By doing a transformation $f(x) \to f(x) + Cx$ for large $C$, you see that issues with regards to error estimates for your original problem is identical to issues for error estimates for integrals of generic twice differentiable functions.) They showed that in the scale of $O(h^s)$, $s = 2$ is sharp for both trapezoidal and Simpson's rules. Their analysis likely can carry over also for all numerical integration schemes in the Newton-Cotes series, yielding the same result.
But particularly interesting is Theorem 1.23, which shows that using Simpson's rule for the twice differentiable case the error is actually $o(h^2)$, while trapezoidal rule is bona fide $O(h^2)$ (unless your function is linear). In view of their previous theorem, this suggests that there may be a further logarithmic correction available in the error estimate if you use Simpson's rule. (On the other hand, it is most likely that higher order Newton-Cotes schemes won't do better [and possibly will do even worse; in the paper it was shown that for once differentiable or merely Hölder continuous functions, trapezoidal rule actually out performs Simpson, since the latter may pick up high frequency artifacts] for only twice differentiable functions.)