DataFaucet ORM

Dynamic Filter

This feature should be used carefully because it introduces the possibility of SQL injection attacks on your site if it is used improperly.

A standard filter creates a comparison between the content of a column in your database and a string value provided by the user. When you use a standard filter, the faucet performs some scrubbing, by ensuring that the column exists in the database, checking its type and ensuring that the string provided by the user is valid for that type (this is particularly important for preventing SQL injection with numeric columns). If the column doesn't exist, the faucet throws an error that you can easily catch and handle with your site-wide error handler.

The dynamic filter instead creates a comparison between the contents of one database column and a literal value you provide, which can be either another column in your database or it could be a formula, etc. This allows you to perform some tasks that would otherwise create errors and not execute the query if you used a standard filter.

Dynamic filters are created by setting the "dynamic" property of the filter object to true or by adding dynamic=true to the arguments of the statement's filter() method. There is no convenience function for creating dynamic filters. Under no circumstances should you ever allow user-supplied data to enter the content of a dynamic filter. If you need to allow users to supply information that will become part of a dynamic filter you must first ensure that the value the user supplied is part of a known list of acceptable values for your dynamic filter.

Example:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // build a select statement on the product table 
  stmt = ds.getSelect("*","tblProduct prod"); 
  
  // join the category table 
  stmt.join("tblProductCategory cat"); 
  
  // only select products where 
  // the product shipping method is the same as 
  // the default shipping method for the category 
  stmt.filter(column="shippingMethodID",
    content="cat.shippingMethodID",
    dynamic=true); 
</cfscript>