Solved

CFSELECT in Coldfusion

Posted on 2014-02-10
7
576 Views
Last Modified: 2014-02-27
Edit FormDesigner TableCategory TableHi,

  I have CFM page where I populate the data from currently selected record (from prev. cfm page using CFGRID), allow users to edit the contents of data and let them save the changes.
  The question is how I can display what is already in the record for category and designer fields, but at the same time, let the user change the them from the drop down if they wish to choose different category or designer. Same question applies to Status field, but the only difference is that Status does not have a table. It is just a one char field with three possible values - A, I or D.
  If I have not explained myself clear, please let me know.

Thanks.

<CFQUERY NAME="FindItem" DATASOURCE="DSNName">
      Select      *
      FROM      Inventory
    WHERE   ID = #cfgridkey#
</CFQUERY>

 <CFQUERY NAME="GetCategory" datasource="DSNName">
               SELECT   *
               FROM        Category
               WHERE      ID=#FindItem.CategoryID#
</CFQUERY>

<CFQUERY NAME="GetDesigner" datasource="DSNName">
               SELECT   *
               FROM        Designer
               WHERE      ID=#FindItem.DesignerID#
</CFQUERY>

...

<CFSELECT NAME="CategoryID" query="GetCategory" VALUE ="ID" DISPLAY="Category">
</CFSELECT>  
<CFSELECT NAME="DesignerID" query="GetDesigner" VALUE="ID" DISPLAY="DesignerName">
</CFSELECT>      
<CFINPUT TYPE="text" NAME="Status" SIZE=2 MAXLENGTH= 2 VALUE=#FindItem.Status#>
           <SELECT SIZE="1" NAME="Status">
              <OPTION>Select Status</OPTION>
              <OPTION VALUE="A">ACTIVE</OPTION>
              <OPTION VALUE="I">INACTIVE</OPTION>
              <OPTION VALUE="D">DON'T LIST YET</OPTION>
           </SELECT>
0
Comment
Question by:sglee
  • 4
  • 3
7 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 325 total points
ID: 39849105
Since you're populating the cfselect from a query, you can use the "selected" attribute.

selected - One or more option values to preselect in the selection list. To specify multiple values, use a comma-delimited list. This attribute applies only if selection list items are generated from a query.

For example, say #FindItem.CategoryID# contains the value you want to select: Run a query to get ALL of the categories:

<CFQUERY NAME="GetCategory" datasource="DSNName">
       SELECT   *
       FROM        Category
       ORDER BY Category
</CFQUERY>

Then set selected=#FindItem.CategoryID# (Edit Fix Typo)

<CFSELECT NAME="CategoryID"
           query="GetCategory"
           selected="#FindItem.CategoryID#"
           VALUE ="ID" DISPLAY="Category" >
.....

Same with the other select fields.

WHERE   ID = #cfgridkey#
BTW: Never use any raw client values (even cfgrid values) in a query without wrapping them in cfqueryparam to protect the db from sql injection.
0
 

Author Comment

by:sglee
ID: 39849127
"BTW: Never use any raw client values (even cfgrid values) in a query without wrapping them in cfqueryparam to protect the db from sql injection.  "

Obeviously you are referring to the "security issue". I am fairly new to this. Would you educate me as to what kind of damage it can cause if I don't protect the db?
What is SQL injection?
How do you wrap them in cfqueryparam?

Here is my code that displays records in flash grid format:
<cfform name="frmtest" skin="HaloBlue" timeout="600">
    <cfgrid name="gridtest"
        height="400" width="700"
        selectMode="row"
        sort = "yes"
        format="flash"
        rowHeaders="no"
            query = "GetInventory"
        onChange = "getURL('ItemDetails.cfm?cfgridkey='+gridtest.selectedItem.ID)">

        <cfgridcolumn name="ID" header="Rec##" width="5">
        <cfgridcolumn name="ItemNo" header="Item No">
               <CFGRIDCOLUMN NAME="Title" DATAALIGN="LEFT"  HEADER="Title" width="60">
..... more column names ...
     </cfgrid>
</cfform>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39849140
(EDIT) SQL injection is just when someone tries to hack your system by inserting malicious sql into form fields, url parameters, etc... in the hopes that your queries are unprotected - so the bad SQL will be executed in your db without your knowledge.   Unfortunately it's extremely common.


what kind of damage it can cause if I don't protect the db?

Given the right command and db permissions it could do anything from updating your tables w/bad data to dropping (ie deleting) your entire database.  

Using bind variables (which is what cfqueryparam does) protects against the common multi-statement form of sql injection. In it's simplest form, you only need to provide the "value" and "cfsqltype" (ie data type of the db column you're matching). So say the Inventory.ID column is type "INTEGER". Your cfqueryparam statement would be:

     WHERE ID = <cfqueryparam value="#cfgridkey#" cfsqltype="cf_sql_integer">

You can find a complete list of cfsqltypes in the docs:
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:sglee
ID: 39849148
did I change it right?
<CFQUERY NAME="FindItem" DATASOURCE="DSNName">
      Select      *
      FROM      Inventory
    WHERE   ID = <cfqueryparam value="#cfgridkey#" cfsqltype="cf_sql_integer">
</CFQUERY>

URL in the address bar still shows ID for cfgridkey. Is that ok?

http://www.DomainName.com/..../AdminItemDetails.cfm?cfgridkey=10
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39849179
IF the data type of your "ID" column is INTEGER, then yes, it's correct. Otherwise, lookup the correct data type for your db in cfqueryparam matrix I posted above.

Though it'll work without either of these tips... 1) for best practices, you should also scope the cfgridkey variable ie  URL.cfgridKey or FORM.cfgridkey.  2) I'd also throw a val() around it. Val() converts non-numeric values to zero. That way the query won't throw an error if passed an empty or invalid value for some reason

WHERE ID = <cfqueryparam value="#val(URL.cfgridkey)#" cfsqltype="cf_sql_integer">

URL in the address bar still shows ID for cfgridkey. Is that ok?

If you're using method=get, that's fine. You just don't want to use it in the query without cfqueryparam.
0
 

Author Comment

by:sglee
ID: 39849196
Thank you for your help.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39849214
You're welcome :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

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…
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 …
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

20 Experts available now in Live!

Get 1:1 Help Now