SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    cfqueryparam for security

    hi there

    I am now looking to secure my application by using cfqueryparam in my sql queries..

    I was wondering...do I need to include the <cfqueryparam> tag in every sql query I have in my application? Or should you be selective with the queries you are securing?

    Thanks
    discover song meanings and more http://www.music-explained.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you can be selective

    for example, let's say that you wanted to return only gizmos from your product table

    SELECT name, price, supplier FROM products WHERE type='gizmo'

    no need for CFQUERYPARAM there

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks

    Am just trying to think where the cfqueryparam would need to be used..

    So if the 'where' clause is determined by yourself, you dont really need to use it, but lets say it was determined by the url you would?

    i.e SELECT name, price, supplier FROM products WHERE type= ''#url.gizmo#

    And also, would cfqueryparam be necessary for the queries that add and update the database? I suppose you could sql inject using a form field as well

    Thanks
    discover song meanings and more http://www.music-explained.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, any input which comes from outside should be screened, and of course form fields are the major weakness

    however, CFQUERYPARAM isn't the only way to do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its the best way to do it for coldfusion though? i thought
    discover song meanings and more http://www.music-explained.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what if you wanted to accept user input through a form field which is intended to be a string of characters?

    CFQUERYPARAM lets you specify CF_SQL_CHAR

    but how do you prevent little bobby tables???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, i was just thinking this actually....if you use

    SELECT id
    FROM artists
    WHERE name = <cfqueryparam value = '#arguments.nameArt#' cfsqltype='CF_SQL_VARCHAR'>

    You could still have ARGUMENTS.nameArt = 2;DELETE FROM ARTISTS, and the cfqueryparam tag would allow it through.

    Do you have any good links on sanitizing database inputs?, would be helpful..

    Thanks
    discover song meanings and more http://www.music-explained.com

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Another method that I've used is to match the incoming data against a list of acceptable inputs.

    I match the form value using a listfindnocase(). If the item is present in the list, use it. If not, use a default value.

  9. #9
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    using a cfswitch?
    discover song meanings and more http://www.music-explained.com

  10. #10
    SitePoint Evangelist
    Join Date
    Mar 2007
    Posts
    584
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do I need to include the <cfqueryparam> tag in every sql query I have in my application
    IMO yes. The main purpose of cfqueryparam is to increase performance and enforce type checking through the usage of bind variables. So it should be used in all queries.

    Quote Originally Posted by namtax View Post
    You could still have ARGUMENTS.nameArt = 2;DELETE FROM ARTISTS, and the cfqueryparam tag would allow it through.
    True, because security is not really the purpose of cfqueryparam. That is just a side benefit.

    By using bind variables cfqueryparam prevents certain malicious strings from being _executed_. Either by rejecting values as invalid for the given data type, or by forcing the database to treat the values as a string, rather than executing them as a sql command. So any protection is a really a side effect of bind variables.

    With cf_sql_varchar, cf_sql_char (etc..) values, cfqueryparam will in no way prevent those stings from being sent to your database. To do that, you must set up a function to examine the incoming data (form and url variables) and search for suspected bad values. If one is found, cancel the current request. The advantage of that technique is bad requests never even make it to the database. The disadvantages are: a) it is only as good as your filter and b) your filter may end up rejecting valid requests.

    http://www.adobe.com/devnet/coldfusi...ection_05.html

    HTH

  11. #11
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by namtax View Post
    using a cfswitch?
    I think this was directed at me, and no, I didn't use a cfswitch.

    I needed to be able to sort by whatever column name was passed from the form, and I couldn't use cfqueryparam because it doesn't seem to work with an ORDER BY clause.

    So I made a list of valid column values and assigned it to a variable, then did this:

    Code CFM:
    ORDER BY <cfif ListFindNoCase(column_list, URL.orderby)>#URL.orderby#<cfelse>event_year, state</cfif>

    If the value being passed in the URL string has a match in the list, then it orders by that. If not, it defaults to year, state.

  12. #12
    SitePoint Evangelist
    Join Date
    Feb 2007
    Posts
    402
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok yes, a sfswitch is not so necessary there....a cfif is more streamlined codewise
    discover song meanings and more http://www.music-explained.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •