Solved

Help with simple report

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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