Solved

Help with simple report

Posted on 2012-03-22
4
237 Views
Last Modified: 2012-06-21
I am trying to build a simple report and for the life of me I cannot figure out what the issue is. I want to pull the user name from the url (in this case it is "MrCool") and then display a total number of records that user created... with a list of them. Below is the error and my code.

In the URL I am adding ?CreatedBy=MrCool after the page name.

Thanks.


Error:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'MrCool'.  
 
The error occurred in mycfc.cfc: line 727
Called from reports.cfm: line 10
Called from mycfc.cfc: line 727
Called from reports.cfm: line 10
 
725 : SELECT CreatedBy, Created
726 : FROM   dbo.Tbl_Record_Master
727 : WHERE CreatedBy=#ARGUMENTS.CreatedBy#
728 :
729 : </cfquery>

My code:

CFC
<cffunction name="CreatedByReport" access="public" returnType="query" output="false" hint="Get created by details">
      <cfargument name="CreatedBy" type="string" required="true" hint="Created by">
      <cfset var createdbyreport="">
      
      <!--- Get a created by report from database --->
      
<cfquery name="createdbyreport"
            datasource="#ds#"
            result="result">

SELECT CreatedBy, Created
FROM   dbo.Tbl_Record_Master
WHERE CreatedBy=#ARGUMENTS.CreatedBy#

</cfquery>

<cfreturn createdbyreport>

</cffunction>

Report Code
<cfinvoke component="mycfc"
          method="CreatedByReport"
          returnvariable="createdbyreport"
          CreatedBy="#URL.CreatedBy#">
            
<cfoutput>
<cfloop query="createdbyreport">
  <tr>
      <td>#CreatedBy#</td>
    <td>#Created#</td>
  </tr>
   </cfloop>
</cfoutput>

<cfoutput><p><b>Total Records for #CreatedBy#: (#createdbyreport.RecordCount# )</b></p></cfoutput>
0
Comment
Question by:earwig75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 37754473
You just have to put quotes around it...

WHERE CreatedBy=#ARGUMENTS.CreatedBy#

Should be...

WHERE CreatedBy='#ARGUMENTS.CreatedBy#'

But you really should use cfqueryparam to prevent SQL injection attacks, particularly since the value is coming from the URL !
0
 

Author Comment

by:earwig75
ID: 37754539
Thanks, can you tell me where I need to add the cfqueryparam to that query? I thought it was more important to use them when inserting/updating records... I didn't realize I needed them on this type.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 37754606
[ no points ]

Every query that has the possibility of user input/manipulation needs cfqueryparam

url vars are no exception

myurul.com.index.cfm?CreatedBy=MrCool UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES--

could expose the names of all your tables

http://www.securiteam.com/securityreviews/5DP0N1P76E.html
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 37754610
You need to use it where your variable is..

WHERE CreatedBy=  <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.createdBy#" null="#NOT len(arguments.createdBy)#")>


It doesn't really matter if it's an insert or update or select, the important factor is where the variable is coming from.

For example, if you're using a request or application global variable, then it's unlikely to be hacked so it's not as necessary.

But cfqueryparam is useful for speeding up queries that are run again and again and it protects against injections so when in doubt,  you may want to include it
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
RETURN INFORMATION FROM COLDFSION CFC 1 83
On Submit return to same spot 7 105
Coldfusion Complex Structure Reference 4 53
REGEX HELP 11 62
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question