SQL query and CFOUTPUT question

[pkonshak]

Hi,

I'm trying to compare two tables in a database.  I need to output a few fields from one table, compare one field between the two tables.  Users are selecting the tables.  I have written an SQL query which does what I need:

<cfquery name="getitems" datasource="snapshot">
select #endtable#.field9, #endtable#.field12, #endtable#.field4, #endtable#.field13, #endtable#.field2, #endtable#.field3, #starttable#.field3
from #endtable#, #starttable#
where #endtable#.field5='#form.field5#'
and #starttable#.field2=#endtable#.field2
</cfquery>

Problem is on output.  You can see I am selecting field3 from two tables.  I also want to output both field3 values.

<cfoutput query="getitems">

#field3#

</cfoutput>

This gives me the value which was selected as #endtable#.field3.

How can I output the other -- #starttable#.field3?

As noted, the table names are variables being passed into the query...  Thanks!

Peter

[-__cfSearching__- · Accepted solution]

How can I output the other -- #starttable#.field3?

Use an alias to give the fields different names ie table.column AS SomeName. Otherwise, cfquery may not know which field you mean when you say "field3".

select #endtable#.field9, #endtable#.field12,

Be careful using this type of sql. Depending on the source of your variables, this can pose a sql injection risk.

[pkonshak]

Thank you, that worked beautifully!  Another trick learned..

Also, thanks for the sql injection warning -- I am cleaning the input before passing it into the query.  Much appreciated!

Peter

[Dan_Bracuk]

Regarding:

I am cleaning the input before passing it into the query

Even this one?

'#form.field5#'

[pkonshak]

Hi,

The #form.field5# comes via a select list....no user input of data.

I suppose it is still possible to be compromised.  I'll fix that.  Thanks!

[-__cfSearching__-]

The #form.field5# comes via a select list....no user input

of data....

Someone could easily construct a fake http post, with malicious sql in the "form" fields, and submit it to your action page. That is why it is vulnerable.