5 ms·

Ok, so your example consists of switching between semantically equivalent queries in the application layer to compensate for weaknesses in the DBMS execution pl

by bmm6o 16y ago

Ok, so your example consists of switching between semantically equivalent queries in the application layer to compensate for weaknesses in the DBMS execution planner. I get that CPU time is a valuable resource and you don't want to open yourself up to DOSing and all, but this isn't really anything like SQL injections. The attacker still can't subvert the structure of the query - changing the structure of the query was the solution to the problem.

btilly 16y ago
Ok, so your example consists of switching between semantically equivalent queries in the application layer to compensate for weaknesses in the DBMS execution planner. Not quite. My example consists of the DBMS execution planner deciding that it will switch between different semantically equivalent query plans depending on the parametrized data passed. This switch should not be noticed in the application layer. Back to the main thread, I suspect that you are interpreting tptacek as saying something he didn't. You seem to be looking for a potential security flaw. But he didn't ever say that there was one. He only said that the query structure can vary with the parameters passed to a parametrized query. I just described one way that this can happen.
- tptacek 16y ago
  Yeah these are great comments and all but I'm just saying that, last time I checked, you can't bind a table name as a parameter in the MySQL protocol; if your table names change at runtime based on input, you're back to concatenated queries.
  - btilly 16y ago
    Oops, I had misunderstood. You're right. It makes no sense to bind table names as parameters, because if you do then the database really has no information from which it can generate possible query plans from. The way that I solved that particular problem in the past is that I used a templating system to generate my SQL. User input was allowed in ifs in my templates, and as parameters for my queries, and that was it. At no point did I trust user data inside of my SQL.
  - rbanffy 16y ago
    > you're back to concatenated queries. Wouldn't it be safer to use a switch statement?
    - bmm6o 16y ago
      In theory, it should be possible to enumerate your SQL queries ahead of time rather than ever generating one on the fly. In practice, that could be a huge pain of the O(2^n) variety, depending on the application type.
      - rbanffy 16y ago
        I assume the choice of table names or query templates in the case mentioned (make a report for atypical client C run faster) would be limited to a handful of possible query templates.

by bmm6o 16y ago

btilly 16y ago

Ok, so your example consists of switching between semantically equivalent queries in the application layer to compensate for weaknesses in the DBMS execution planner. Not quite. My example consists of the DBMS execution planner deciding that it will switch between different semantically equivalent query plans depending on the parametrized data passed. This switch should not be noticed in the application layer. Back to the main thread, I suspect that you are interpreting tptacek as saying something he didn't. You seem to be looking for a potential security flaw. But he didn't ever say that there was one. He only said that the query structure can vary with the parameters passed to a parametrized query. I just described one way that this can happen.

tptacek 16y ago
Yeah these are great comments and all but I'm just saying that, last time I checked, you can't bind a table name as a parameter in the MySQL protocol; if your table names change at runtime based on input, you're back to concatenated queries.
- btilly 16y ago
  Oops, I had misunderstood. You're right. It makes no sense to bind table names as parameters, because if you do then the database really has no information from which it can generate possible query plans from. The way that I solved that particular problem in the past is that I used a templating system to generate my SQL. User input was allowed in ifs in my templates, and as parameters for my queries, and that was it. At no point did I trust user data inside of my SQL.
- rbanffy 16y ago
  > you're back to concatenated queries. Wouldn't it be safer to use a switch statement?
  - bmm6o 16y ago
    In theory, it should be possible to enumerate your SQL queries ahead of time rather than ever generating one on the fly. In practice, that could be a huge pain of the O(2^n) variety, depending on the application type.
    - rbanffy 16y ago
      I assume the choice of table names or query templates in the case mentioned (make a report for atypical client C run faster) would be limited to a handful of possible query templates.