building dynamic queries

Report · Oct 21, 2008

I am on a slavage mission with a short time frame. I have a database w/50 tables all with the same structure, I need to display the contents of the tables base on a user click on an image map. My problem is that I am trying to dynamically set the table name to be used in the query and the name is being passed but I get an error saying "Syntax error in query. Incomplete query clause. " but the debug info clearly shows the table name.


select *

from #trim(url.state)#

order by branch

SQL select * from ''me'' order by branch
DATASOURCE [removed by author]
VENDORERRORCODE -3003
SQLSTATE HY000

Any suggestions?

tia
J.

Report · Oct 21, 2008

> Any suggestions?

ColdFusion automatically escapes variables with string data rendered
inside a <cfquery...>block with single quote characters. Because 90% of
the time - this is the proper behavior.

You can see that in the debug SQL. The table name should not have the
quotes around it.

SQL select * from ''me'' order by branch

In cases, such as yours, where you do not want this behavior, ColdFusion
provides the preserveSingleQuotes() function.

SELECT *
FROM #preserveSingleQuotes(trim(url.state))#
ORDER BY month

Just realize you are writing very dangerous code there. Any user who
notices what you are doing can provide ANY sql code they want in the
url.state variable and do just about anything they want to your database.