Solved

Help with simple report

Posted on 2012-03-22
4
238 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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