Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Help with simple report

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
earwig75
Asked:
earwig75
  • 2
1 Solution
 
gdemariaCommented:
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
 
earwig75Author Commented:
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
 
SidFishesCommented:
[ 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
 
gdemariaCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now