First, there is not such thing as an SNI SSL certificate or an SNI based SSL certificate. SNI is a TLS extension in that the client includes the servers name in the TLS handshake so that the server can pick the correct certificate in case multiple certificates are served by the same IP address. There is no difference in the kind of certificate used with SNI and the one used without. Browsers use the SNI extension for all TLS connections because they don't know if the server needs it or not to serve the proper certificate.
The main question instead is where the endpoint of the TLS connection is. In case of a CDN like Cloudflare this endpoint is the CDN. This means that Cloudflare can look at the decrypted traffic. And, since multiple hosts are served on the same IP address Cloudflare uses SNI to make this possible. But this is only a side effect, not a security problem by itself.
In a similar problem you run if you use shared or managed hosting. In this case at least the company managing the system can have access to the decrypted traffic. And with shared hosting you additionally share the same system with 100's of other users which increases the attack surface a lot: An attacker often needs a single weakness in one of the web applications or a weak password for shell access to get access to the system and with a privilege escalation attack he then can quickly own the whole system and look at the decrypted traffic. Also, SNI is often used in shared hosting to share the same IP address between multiple certificates. But again, the problem is not SNI in this case.
The best option would be if you use your own server as the TLS endpoint, not shared by others and managed in a secure way. And not to use any CDN in front which will be the TLS endpoint instead. But as always you need to clearly evaluate all the risks together: while having a CDN in front which works as a TLS endpoint is bad for security having a CDN which protects you against DOS attacks is again good for security.