Solved

Help with simple report

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now