33 ms·

This fails if either the UI sanitizes wildcards, or if the database prohibits them, or if it produces so much data that you can't ingest it in time, etc.

wglb 2y ago
Sanitization almost always fails. This becomes an arms race.
- valenterry 2y ago
  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/ https://valentin.willscher.de/posts/sql-api/
  - IshKebab 2y ago
    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.
    - dataflow 2y ago
      > 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.
      - IshKebab 2y ago
        You vastly underestimate the creativity of people that bypass imperfect sanitisers.
        bdangubic 2y ago
        vastly is a big understatement :)
        valenterry 2y ago
        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).
      - wglb 2y ago
        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.
  - wglb 2y ago
    The parser isn't shown there, so it isn't clear what would happen with weird input. Have you had anyone do a penetration test on it?
    - valenterry 2y ago
      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.
      - wglb 2y ago
        > Actually, yes That is good. I have long believed that "if it is not tested, it is broken"
        valenterry 2y ago
        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.
        wglb 2y ago
        Is the parser available for review?
IshKebab 2y ago
There are trivial ways around all of those. `LIMIT 1`, `SELECT .. FROM information_schema...`, etc.
- dataflow 2y ago
  > There are trivial ways around all of those. `LIMIT 1` LIMIT 1 limits row count. The issue here was columns. Like a giant blob someone might've stored in there. > `SELECT .. FROM information_schema...` no such table: information_schema.columns > etc. https://news.ycombinator.com/item?id=43181799 https://news.ycombinator.com/item?id=43181799
  - IshKebab 2y ago
    > no such table: information_schema.columns Oh I guess I will try the other very small number of options that it could be. > LIMIT 1 limits row count. The issue here was columns. Like a giant blob someone might've stored in there. Come on, this is pure nonsense.
wglb 2y ago
Injections don't always need ''. The statements 1=1 and 1=0 if injected into a query will give different answers if SQLI exists. There are MANY other tricks that don't involve ''. Besides, consider the number of valid queries done by the application that involve '*'. You are not going to turn that off.
mcv 2y ago
It also fails if the system was written using parameterized queries. I wouldn't expect a system to be sanitizing anything if fails to take the most basic step for db access. This whole discussion is only relevant for systems developed by amateurs. SQL injection can only work at all if you use string concatenation to create queries, which you should never do.

wglb 2y ago

Sanitization almost always fails. This becomes an arms race.

valenterry 2y ago
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/ https://valentin.willscher.de/posts/sql-api/
- IshKebab 2y ago
  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.
  - dataflow 2y ago
    > 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.
    - IshKebab 2y ago
      You vastly underestimate the creativity of people that bypass imperfect sanitisers.
      - bdangubic 2y ago
        vastly is a big understatement :)
      - valenterry 2y ago
        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).
    - wglb 2y ago
      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.
- wglb 2y ago
  The parser isn't shown there, so it isn't clear what would happen with weird input. Have you had anyone do a penetration test on it?
  - valenterry 2y ago
    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.
    - wglb 2y ago
      > Actually, yes That is good. I have long believed that "if it is not tested, it is broken"
      - valenterry 2y ago
        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.
        wglb 2y ago
        Is the parser available for review?

IshKebab 2y ago

There are trivial ways around all of those. `LIMIT 1`, `SELECT .. FROM information_schema...`, etc.

dataflow 2y ago
> There are trivial ways around all of those. `LIMIT 1` LIMIT 1 limits row count. The issue here was columns. Like a giant blob someone might've stored in there. > `SELECT .. FROM information_schema...` no such table: information_schema.columns > etc. https://news.ycombinator.com/item?id=43181799 https://news.ycombinator.com/item?id=43181799
- IshKebab 2y ago
  > no such table: information_schema.columns Oh I guess I will try the other very small number of options that it could be. > LIMIT 1 limits row count. The issue here was columns. Like a giant blob someone might've stored in there. Come on, this is pure nonsense.

wglb 2y ago

Injections don't always need ''. The statements 1=1 and 1=0 if injected into a query will give different answers if SQLI exists. There are MANY other tricks that don't involve ''. Besides, consider the number of valid queries done by the application that involve '*'. You are not going to turn that off.

mcv 2y ago

It also fails if the system was written using parameterized queries. I wouldn't expect a system to be sanitizing anything if fails to take the most basic step for db access. This whole discussion is only relevant for systems developed by amateurs. SQL injection can only work at all if you use string concatenation to create queries, which you should never do.