5 ms·
You are advocating a semi-reasonable solution to a common mistake, possibly because you can't conceive of not making the mistake. The better solution is to fol
by btilly 16y ago
You are advocating a semi-reasonable solution to a common mistake, possibly because you can't conceive of not making the mistake. The better solution is to follow the advice in http://cr.yp.to/qmail/guarantee.html http://cr.yp.to/qmail/guarantee.html of using APIs that avoid parsing user input. Then no possibility of problems exists at all.
This is explained in item #5 of http://cr.yp.to/qmail/guarantee.html http://cr.yp.to/qmail/guarantee.html - Don't parse.
Note that this is definitely true in this case. MongoDB's API is based on the passing of data structures where you have well-defined places to put user input directly. The problem emerges because PHP decides to attempt to parse that user input in a way that it had no real need to, with bad results. Without that parsing, there would have been no need to sanitize data because there would be no possibility of ever having had bad data in the first place.
- bmm6o 16y agoA light bulb went off for me when I saw SQL injection attacks described as "subverting the structure of the query". The problem isn't sneaking bad data into your query, the problem is that the data injected into the string alters the parse tree in the dbms. This distinction isn't immediately obvious if you've moved to a parametrized query solution, because there's still a lot left to parse - it's just that the part that gets parsed is now a compile-time constant.
- tptacek 16y agoFor whatever it's worth, even in parameterized SQL queries, the query structure isn't always entirely fixed. There are injectable parameterized query constructs, and some of them are fairly common.
- bmm6o 16y agoI haven't heard of this before. Can you link to an example?
- tptacek 16y agoOn-the-fly table selection is a simple example, but there are more subtle examples that happen even more often.
- bmm6o 16y agoUnless 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 agoThe 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.