Copy link to clipboard
Copied
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
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Regarding:
I am cleaning the input before passing it into the query
Even this one?
'#form.field5#'
Copy link to clipboard
Copied
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!
Copy link to clipboard
Copied
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.