You have to have a multi-layered approach. This is the "Swiss cheese" model of prevention: individual layers may have holes, but if none of the holes in the stack line up, nothing makes it all the way through.
I approach this by thinking of verification surfaces. Each one is a layer.
For SQL, I have a tool that the agent can call to run queries. (I actually keep a range of tools: for known queries the SQL is baked into the tool and only results come back; I also have a general SQL tool. Prefer the former; deploy the latter only if you must.)
The general SQL tool itself is multi-layered. The raw SQL produced by the agent is a verification surface, as are the parameters used in the tool call.
There are plenty of existing utilities that validate SQL and define its claims, scope, and if it mutates in a structured way. My tool runs those against the SQL as a verification surface and it hard gates some requests.
It also verifies the parameters. It does this in two ways: at a shallow level, it just makes sure they are valid and sane, so it can fail fast if the LLM does the wrong thing.
But it also does a deeper check, where it calls out to supporting microservices to verify a) permissions embedded in the request and b) consistency of the request with the rest of the system.
Since you asked about challenges in prod: if you are not careful, the LLM can write wildly inefficient queries. Some of those made it through and I had to go back and tweak the prompts to give enough context to have it tune properly.
My suggestion: think through the verification surfaces, figure out what you can verify deterministically, and use that as gates in your tool.
I actually did a little writeup of the "verification surface" here, seems relevant: https://michael.roth.rocks/blog/verification-surface/