Professional Web Applications Themes

Single & Double Quote Problem in Database Insert/Update - Coldfusion Database Access

Hello, I have a textarea on a form where users are able to enter text wrapped in 'single' and "double" quotes. However, when I insert or update the data in my MySQL database I am having problems. I realise that this is because the variable containing the data is wrapped in either single or double quotes in the SQL expression. I have tried replacing the double quotes with the correct HTML character ('), but as the text occasionally contains HTML (i.e. <a href="link">link</a>), this is not working as the browser is not rendering the HTML link correctly (i.e. the browser ...

  1. #1

    Default Single & Double Quote Problem in Database Insert/Update

    Hello,

    I have a textarea on a form where users are able to enter text wrapped in
    'single' and "double" quotes. However, when I insert or update the data in my
    MySQL database I am having problems. I realise that this is because the
    variable containing the data is wrapped in either single or double quotes in
    the SQL expression.

    I have tried replacing the double quotes with the correct HTML character ('),
    but as the text occasionally contains HTML (i.e. <a href="link">link</a>), this
    is not working as the browser is not rendering the HTML link correctly (i.e.
    the browser doesn't like <a href='link'>link</a>).

    How can I overcome this problem?

    Thanks in advance.

    Simon

    smnbin Guest

  2. #2

    Default Re: Single & Double Quote Problem in DatabaseInsert/Update

    In days of old, you'd use PreserveSingleQuotes() to escape single quotes in
    text that was being entered into a SQL query.

    However, now you should just use <cfqueryparam> in order to handle quotes
    inside a SQL query.


    INSERT INTO someTable
    (
    someText
    )
    VALUES
    (
    <cfqueryparam value="#form.myTextArea#" cfsqltype="CF_SQL_VARCHAR" />
    )

    cf_menace Guest

  3. #3

    Default Re: Single & Double Quote Problem in DatabaseInsert/Update

    Since not every database supports cfqueryparam, you need other options
    available as well. In addition to what cf_menace mentioned, you can also quote
    the quotes. Replace every single quotes with two single quotes.

    Dan Bracuk Guest

  4. #4

    Default Re: Single & Double Quote Problem in DatabaseInsert/Update

    Thanks for your help guys. I have now got it working...
    smnbin Guest

Similar Threads

  1. CF7 query update with single quote
    By achab23 in forum Coldfusion Database Access
    Replies: 5
    Last Post: April 13th, 05:37 PM
  2. SQL insert/update problem if coldfusion field value has single quote
    By strutsng@gmail.com in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 25th, 04:45 PM
  3. double single quote problem in cfquery
    By Bigio in forum Coldfusion Database Access
    Replies: 7
    Last Post: April 25th, 09:46 PM
  4. Single versus Double quote marks as string delimiters
    By Joshua Beall in forum PHP Development
    Replies: 12
    Last Post: January 30th, 12:14 PM
  5. INSERT with double quote?
    By Xyerp in forum IBM DB2
    Replies: 2
    Last Post: September 24th, 08:58 AM

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