CODEWITHSUNDEEP
sqlcoldfusioncfqueryparam
Alistair Knock
Alistair Knock

Reputation: 1836

Use of CFQUERYPARAM to specify table/column names in SQL

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.

Upvotes: 2

Views: 1665

Answers (2)

hofo
hofo

Reputation: 522

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

Upvotes: 1

Peter Boughton
Peter Boughton

Reputation: 112150

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.

Upvotes: 5

Related Questions