4 ms·

Unless I'm misunderstanding you, changing the table doesn't affect the shape of the parse tree. (and I'm not trying to be pedantic - I'm genuinely curious about

by bmm6o 16y ago

Unless I'm misunderstanding you, changing the table doesn't affect the shape of the parse tree. (and I'm not trying to be pedantic - I'm genuinely curious about this).

btilly 16y ago
The issue isn't how you parse the query. The issue is how you choose to execute it. I'm not sure what tptacek is thinking, but here is an important case where you really want the execution path to change. Tables often have a very uneven distribution of data. This can mean that the right query plan will depend on the data provided. Consider the following query: SELECT ... FROM lease l JOIN property p ON p.id = l.property_id JOIN management_company c ON c.id = p.management_company_id WHERE l.created >= ? AND l.created < ? AND c.name = ?; Pretty straightforward query, right? Now here is the question, should the query plan start by filtering leases on date then flow forward to finally filter on the management company, or by getting the right management company and then flow backwards to filter on dates. It turns out that there is no right answer. You want to start with whichever restriction eliminates the most records, quickest. If the date range is wide, we want to start with the management company. If the date range is narrow, we want to start with that. So let us simplify our life: SELECT ... FROM lease l JOIN property p ON p.id = l.property_id JOIN management_company c ON c.id = p.management_company_id WHERE l.created >= '2011-02-01' AND l.created < '2011-03-01' AND c.name = ?; There is still no right answer! Let's suppose some company, call it 'AIMCO', has a huge number of properties with a lot of lease volume while other companies generally have 1-5. Then it is better to filter on dates first for 'AIMCO', and better to filter on the company first for anything else. Getting the order wrong gives an order of magnitude performance difference. (This is a real life example. I really did have a bug that basically read, "Make this report run for AIMCO." And this was exactly the problem.) Most databases will pick one query plan and go with it. But Oracle allows you to turn on Adaptive Cursor Sharing. With this it will analyze the data, see that AIMCO should get special treatment, and actually execute a different query plan if you passed in 'AIMCO' than if you passed in something else. So now you have a data dependent query plan.
- 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.

by bmm6o 16y ago

Unless I'm misunderstanding you, changing the table doesn't affect the shape of the parse tree. (and I'm not trying to be pedantic - I'm genuinely curious about this).

btilly 16y ago

The issue isn't how you parse the query. The issue is how you choose to execute it. I'm not sure what tptacek is thinking, but here is an important case where you really want the execution path to change. Tables often have a very uneven distribution of data. This can mean that the right query plan will depend on the data provided. Consider the following query: SELECT ... FROM lease l JOIN property p ON p.id = l.property_id JOIN management_company c ON c.id = p.management_company_id WHERE l.created >= ? AND l.created < ? AND c.name = ?; Pretty straightforward query, right? Now here is the question, should the query plan start by filtering leases on date then flow forward to finally filter on the management company, or by getting the right management company and then flow backwards to filter on dates. It turns out that there is no right answer. You want to start with whichever restriction eliminates the most records, quickest. If the date range is wide, we want to start with the management company. If the date range is narrow, we want to start with that. So let us simplify our life: SELECT ... FROM lease l JOIN property p ON p.id = l.property_id JOIN management_company c ON c.id = p.management_company_id WHERE l.created >= '2011-02-01' AND l.created < '2011-03-01' AND c.name = ?; There is still no right answer! Let's suppose some company, call it 'AIMCO', has a huge number of properties with a lot of lease volume while other companies generally have 1-5. Then it is better to filter on dates first for 'AIMCO', and better to filter on the company first for anything else. Getting the order wrong gives an order of magnitude performance difference. (This is a real life example. I really did have a bug that basically read, "Make this report run for AIMCO." And this was exactly the problem.) Most databases will pick one query plan and go with it. But Oracle allows you to turn on Adaptive Cursor Sharing. With this it will analyze the data, see that AIMCO should get special treatment, and actually execute a different query plan if you passed in 'AIMCO' than if you passed in something else. So now you have a data dependent query plan.

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.