I did an intern for a company becoming PCI compliant and instead of using triggers to prevent SQL injection, I went through 1000s (if not more) lines of web-app code and called functions every time user input was used in a SQL query. At the time I heard talk about using triggers but didn't really know what they are.

Today in a class triggers were taught and if each time a SQL statement is called something happens, wouldn't that be much easier to make a trigger check for injection rather than "parameterizing" every line of code in the web app? I asked the prof and he said a two tired system would be needed for it to work because triggers don't work on the querries.

What does he mean triggers don't work on the queries and why aren't triggers used for securing databases from web-apps?

The web-apps were written in Cold Fusion and didn't have the cfquery param tags added. I'm curious, why can't the Cold Fusion engine add these automatically at run time, I mean there's never a time when one should not use cfquery param tags, right?

I suspect you are confusing triggers with stored procedures (the former are typically implemented using the latter). Stored procedures allow privilege seperation such that the client can only perform certain operations on the data with custom validation. The main relevance of triggers to security is that they make it easier to capture audit information.
–
symcbeanDec 6 '12 at 22:36

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:

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.

Even in cases when a trigger is appropriate, there is no notification when it is dropped or disabled. Thus you might think a trigger is enforcing a foreign key, creating an audit record or validating user input to thwart sql-injection ... but it's not even active. This is a huge security hole that other objects (e.g. stored procs) don't have.

I think what is being talked about are "Stored Procedures". Triggers are fired internally in the database when actions are performed, but stored procedures are direct parameterized functions.

Why are triggers (stored procedures) not often used to secure a database?

Sometimes laziness, sometimes it's just too restrictive. It's also the case that using parameterized SQL queries in your code also prevents SQL injection attacks. Jeff Atwood wrote a decent article about tradeoffs with stored procedures back in 2004.

That said, even with parameterized SQL queries, stored procedures still have a place in my mind, especially with passwords. There is no good reason for the application to ever query the password hash from the database system, only to ask the database system to compare an input value (such as a generated hash to keep the load off the DB server) against the hash. For that scenario, a stored procedure definitely adds security and I really wonder why nobody ever uses that.

Wouldn't that be much easier to make a trigger check for injection

Well, let's consider this: if your trigger can check for SQL injection, then I presume your code could as well. Then you either have a whitelist of values, or a blacklist for detection. Blacklists are poor choices. Even then, if you're doing whitelists the enumeration factor could be immense if you don't trust any of the query input at all, including the structure. Then we're back to either writing a stored procedure for every possible operation or using parameterized queries... and generally, the second method is saner.

@Terry Chia, stored procedures can be used for comparing password hashes, not triggers.
–
MatrixNov 8 '12 at 8:44

@Matrix right thanks for that. got the two confused for a moment.
–
Terry ChiaNov 8 '12 at 9:00

3

It's worth noting that using (1) stored procedures is not the same thing as using parameterized queries (though the former usually implies the latter).(2) Stored procedures also provide a means of privilege seperation, e.g. allowing a user to update a single named row at a time, but not multiple rows
–
symcbeanNov 8 '12 at 9:41

Especially since, with anything short of parameterized query, if you miss something in either validation list (black/white), triggers might not save you anyways; they generally only get the results of the operation against a table, which wouldn't help if the DB allows multiple statements per connection. Oh, and some systems don't automatically turn them back on after a restore, either, so they might not even be on...
–
Clockwork-MuseNov 9 '12 at 0:39