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

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

CFSELECT in Coldfusion

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
sglee
Asked:
sglee
  • 4
  • 3
1 Solution
 
_agx_Commented:
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
 
sgleeAuthor Commented:
"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
 
_agx_Commented:
(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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
sgleeAuthor Commented:
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
 
_agx_Commented:
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
 
sgleeAuthor Commented:
Thank you for your help.
0
 
_agx_Commented:
You're welcome :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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