2

I need to dynamically construct a set of JOIN statements where the table and column names are passed in from another ColdFusion query. When passing the string values to into the statement, CFQUERYPARAM adds single quotes around it - that's part of the point of CFQUERYPARAM. Given that this breaks the SQL statement, is it acceptable not to use CFQUERYPARAM in this case and instead ensure that the incoming query is cleansed, or is there a way round which allows CFQUERYPARAM to be used? (I can lock down these pieces of code using circuit/fuse permissions in Fusebox.)

Thanks.

Alistair Knock
  • 1,806
  • 2
  • 16
  • 25
  • I'm not sure I understand why single quotes break the sql, it only puts them in for CF_SQL_VARCHAR, and you need them then. How do they break the query? – kevink Mar 24 '09 at 12:50
  • The use of the tag was a safety check for sanitisation rather than performance, where the variable being used is varchar; because it is being passed as something other than a value in the SQL (a column or table name), the single quotes prevent it from finding the column/table. – Alistair Knock Mar 24 '09 at 14:27

2 Answers2

5

cfqueryparam does not add single quotes - it uses bind variables.

I am instantly suspicious of the statement "dynamically construct a set of JOIN statements" - it doesn't sound like you're necessarily doing things properly if you're dynamically joining.

However, for table/column names, once you are definitely sanitizing fully - if cfqueryparam doesn't work and you need cf variables - then yes, you can use CF variables directly.

Note: To sanitize safely, you can use rereplacenocase(table_name,'[^a-z_]','','all') to remove everything other than a-z and underscore.

Peter Boughton
  • 110,170
  • 32
  • 120
  • 176
  • Thanks Peter. It is related to #673805 in that the main table structure and lookup tables can change as new data is added, so a static set of joins will not be sufficient. I should point out this is a back-end/local application for data manipulation, not public use. – Alistair Knock Mar 24 '09 at 14:25
  • I suggest that the regex be [^\w]+ which will be letters, numbers and underscores, plus is shorter. – Nathan Strutz Mar 26 '09 at 20:02
  • \w is word characters - it is not always just [a-z0-9_] and I deliberately used the more general purpose one here. However, if you were happy with the word characters route, \W is even simpler than [^\w] and is identical in meaning. – Peter Boughton Mar 27 '09 at 01:18
  • I know this is old but .. \w is in fact [a-zA-Z0-9_] and Nathan is right in using that as opposed to [^a-z_]. \W is a word boundary such as spaces, tabs, etc. so that's no good. – dwkd Mar 02 '15 at 12:46
  • `\W` is *not* a word boundary. It is the inverse of `\w` (i.e. identical to `[^\w]` as I stated) and `\w` is word characters, which has different meaning in different regex engines. – Peter Boughton Mar 02 '15 at 20:56
  • It is often `[0-9A-Za-z_]` -- which is "alphanumeric plus underscore" (where alphanumeric has a specific meaning of 62 characters), not "letters, numbers and underscores", since `á` is a letter and `½` is a number, yet neither characters are included in Apache ORO's `\w` -- but it is not always that limited, and when the code is a security-related task like sanitizing table names in SQL, you want to be paranoid. Hence the `[^a-z_]` in a case-insensitive regex. – Peter Boughton Mar 02 '15 at 20:56
1

You can escape the single quotes by using two of them. You can also use the preserveSingleQuotes function.

hofo
  • 522
  • 1
  • 4
  • 16