If you do it wrong, yes. Sure, there is no 100% security, but honestly, it's 2025. We already know the techniques how to prevent SQL injection of any kind. I wrote about this here: https://valentin.willscher.de/posts/sql-api/
> Right but the case that is being imagined here is a site that perfectly sanitises * but somehow still allows SQL injection? I don't think so.
It could literally just reject anything with asterisks.
It doesn't even need to do anything perfectly, it just needs to do it enough to produce hurdles for you. Like blowing through the number of attempts you realistically have remaining.
I think sanitizing is the wrong word in this context. That sounds as if the idea were to take the SQL, try to ensure it's valid and then run it. That approach is of course very risky and I'd never do that.
No, my idea (as you can see from my post) is: parse the SQL, then check the resulting structure (that is basically a whitelisting process) and then turn the structure into SQL again. The last part is crucial, because it means that you have turned a whitelisted structure into SQL. Or in other words: even if some evil person found a bug and was able to convince the sql parser that everything is fine even though it is not, they would not be able to leverage that, because you are not actually running their SQL.
Or to be more concrete: let's say the parser thinks that something is a comment, but the DMBS would actually run it as SQL - then that would be a problem if you jsut sanitize the SQL. But it's not a problem if you turn the SQL structure into SQL again, because your code to do that would just reject anything that it doesn't expect (and that definitely includes comments).
Hurdles mean nothing to a determined penetration tester. Unless you have watched one in action, or tried an exercise yourself against a hard target, it is likely to surprise you how little "hurdles" mean.
Well, I would suppose that you pick an existing parser that is well tested and battle proven. No need to write your own. For most languages those parsers already exist. I would also not write my own json parser, so why would I do that for SQL, which is even more complex?
But in any case, the weird input would just be rejected. In my case I'd get a "parser error" from my library and then wrap it into my own "query not supported" error and return that as a 400.
> Have you had anyone do a penetration test on it?
Actually, yes. The pen-testers were surprised about the technique but did not find any problems with it.
I mean, there are still different reputations for certain techniques. And SQL sanitization has a bad reputation for good reasons. But SQL parsing, whitelisting and then reserialization still has a bad reputation in the eyes of many people, but that's only because they don't understand the difference between the two.
No one would say "parsing json is broken if it's not tested" right? Then for SQL the same would be true.
So if you were to use this technique in a business that doesn't mandate regular pentests, it would not change how well the technique works and how secure/safe it is.
In my case yes, I use an OSS library that is based on Scala's (a bit outdated) parser combinators (https://github.com/scala/scala-parser-combinators). So the parsing part is not actually written by hand, the library only defines the keywords, the precedence etc.
