Professional Web Applications Themes

Bug: Escaping of single-quotes in cfQuery ! - Coldfusion - Advanced Techniques

Attached code for better layout: CF automatically escapes single-quotes when outputtting values in queries. (Reason: Preventing of sql-injection) Up to CF5.0, this worked rather well, but since CF6.0 (maybe 6.1), this escapment has flaws: In CF6.1 (and above), single-quotes are only escaped (doubled), when they are not followed by another single-quote! One single-quote: <cfset tempValue = "d'accord"> within a cfquery in CF5.0: '#tempValue#' = 'd''accord' within a cfquery in CF6.1: '#tempValue#' = 'd''accord' Two single-quotes: <cfset tempValue = "d''accord"> within a cfquery in CF5.0: '#tempValue#' = 'd''''accord' within a cfquery in CF6.1: '#tempValue#' = 'd''accord' NOTE: in CF6.1, only 1 ...

  1. #1

    Default Bug: Escaping of single-quotes in cfQuery !

    Attached code for better layout:


    CF automatically escapes single-quotes when outputtting values in queries.
    (Reason: Preventing of sql-injection)

    Up to CF5.0, this worked rather well, but since CF6.0 (maybe 6.1), this
    escapment has flaws:
    In CF6.1 (and above), single-quotes are only escaped (doubled), when they are
    not followed by another single-quote!

    One single-quote:
    <cfset tempValue = "d'accord">
    within a cfquery in CF5.0: '#tempValue#' = 'd''accord'
    within a cfquery in CF6.1: '#tempValue#' = 'd''accord'

    Two single-quotes:
    <cfset tempValue = "d''accord">
    within a cfquery in CF5.0: '#tempValue#' = 'd''''accord'
    within a cfquery in CF6.1: '#tempValue#' = 'd''accord'
    NOTE: in CF6.1, only 1 single-quote is inserted!

    Using sp_ExecuteSQL:

    Classic CF5.0 syntax:
    <cfquery name="qsTest" datasource="#datasource#">
    <cfsavecontent variable="sqlTemp">
    SELECT test = '#tempValue#'
    </cfsavecontent>
    EXECUTE sp_ExecuteSQL N' #sqlTemp# '
    </cfquery>

    If tempValue contains single-quotes, the above will crash with CF6.1:
    resulting SQL:
    "EXECUTE sp_ExecuteSQL N' SELECT test = ''d''accord'' '"
    NOTE: "d''accord" (2 single-quotes) instead of "d''''accord" (4 single-quotes)

    New syntax, valid for CF5.0 and CF6.1 and above:
    <cfquery name="qsTest" datasource="#datasource#">
    <cfsavecontent variable="sqlTemp">
    SELECT test = '#tempValue#'
    </cfsavecontent>
    EXECUTE sp_ExecuteSQL N'
    #Replace(PreserveSingleQuotes(sqlTemp), "'", "''", "All")#
    '
    </cfquery>
    NOTE: The automatic 2nd doubling of single-quotes is prevented with
    PreserveSingleQuotes() and all the single-quotes are doubled using Replace().

    Remaining problem in CF6.1 and above:
    If a value really contains 2 consequent single-quotes, they will always end up
    as 1 single-quote in the DB.
    One workaround would be a PreserveSinglequotes() plus Replace() around each
    sting-value-output in a cfquery.
    Another would be to use the CFQueryParam-tag, which handles single-quotes
    correctly, but disables the use of cfSavecontent within a cfQuery.

    Personal suggestion:
    Strict usage of cfQueryParam.
    Disallow usage of sp_ExecuteSQL and force using cfTransaction and only one
    DB-operation per cfQuery.
    (Note: Several DB-operations within one cfQuery without using sp_ExecuteSQL
    raises another problem: Errors from the DB are only recognized if they occur in
    the first DB-operations, otherwise they are ignored!)

    Stefan K. Guest

  2. #2

    Default Re: Bug: Escaping of single-quotes in cfQuery !

    I'm sorry, I didn't read your whole post, just thought of similar situation I encountered and utilized the following CF function:

    PreserveSingleQutoes()

    hope this helps
    -dimitri
    ufitzi Guest

Similar Threads

  1. cfquery not escaping single quotes correctly
    By Gregd66 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 17th, 10:18 PM
  2. MS Access driver not escaping single quotes?
    By DGH in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 16th, 09:06 PM
  3. Escaping nasty quotes
    By Roy W in forum PHP Development
    Replies: 5
    Last Post: August 1st, 10:06 AM
  4. [PHP] Escaping nasty quotes
    By Jay Blanchard in forum PHP Development
    Replies: 2
    Last Post: July 31st, 06:57 PM
  5. Escaping single quotes
    By in forum PHP Development
    Replies: 0
    Last Post: July 30th, 11:28 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139