why aren't triggers used for securing databases from web-apps?
Start with a very contrived example:
If the page code had something like
query = "SELECT * FROM some_table WHERE this_column='" + user_supplied_value + "';"
And an attacker entered this value:
user_supplied_value = "';DROP TABLE some_table;'SELECT DATE()"
The query sent to the database is
query = "SELECT * FROM some_table WHERE this_column=''; DROP TABLE some_table; 'SELECT DATE()';"
A trigger function would have to safely parse and reject this string:
"';DROP TABLE some_table;'SELECT DATE()"
But. A trigger is usually bound to an insert, update, or delete operation. The above SQL containing the attack string has three queries in it, none of which performs an insert, update, or delete. So the trigger would never fire, validation would never occur, and "some_table" would be gone.
But say there was an INSERT with a trigger that fires before the INSERT is committed. It's now up to you to do the validation that the engine developers already did for the driver code. There's plenty of room for error, very little benefit, and really no point since parameterized queries do all this automatically and much more safely.
Using parameterized queries ensures the SQL looks something like this before it's sent to the database engine:
query = "SELECT * FROM some_table WHERE this_column='\'\; DROP TABLE some_table\;\'SELECT DATE()';"
You use triggers for authorization, auditing, version control, enforcing business constraints, but not for input validation.
What does he mean triggers don't work on the queries
That depends on what he means by "work", but generally it's because what I described above:
- A trigger doesn't act on the query itself. It is fired once the
parser determines what operation the query is attempting to perform.
So no matter what the trigger does, the malicious query is in the
system and could potentially be executed.
- In the example above,
no trigger would ever fire. So a trigger doesn't "work" if you're
dealing with "SELECT" queries.
wouldn't that be much easier to make a trigger check for injection
rather than "parameterizing" every line of code in the web app?
No, it's much, much harder.
Doing validation after the query is submitted means malicious code will make it into your database engine. Your validation functions have to be perfect. Every single function in your database has to use those validation functions before executing a query. You have two levels of perfection to meet. If not, that malicious code could be executed.
The web app programmers would also have to keep track of all the string concatenation operations used to build to queries, and for longer strings it could get messy.
Parameterized queries, on the other hand, ensure the input is sanitized before it ever makes it to your database engine. So your custom functions, and any built-in functions, will be safe from exploitation.
I mean there's never a time when one should not use cfquery param
tags, right?
One could get away with it if the query being executed does not take any input. Generally though, if you're dealing with inputs anywhere in the application, for consistency all your database calls should use the safest mechanism available, which is the parameterization function provided by the driver library or application framework.
