[thelist] Double and Single Quotes are ruining my life....

[jeff]

steven,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: Steven Wood
:
: Using CFQUERYPARAM has made things a lot
: easier for us in this regard.  If you're entering
: data from a textarea into a database, try binding
: the variable using CFQUERYPARAM... as in the
: following:
:
: <cfquery name="InsertReport" datasource="mysource">
: 	INSERT INTO
: 		my.reportstable (
: 		Subject,
: 		Content)
: 	VALUES (
: 		#Subject#,
: 		<cfqueryparam value='#Content#'
: CFSQLTYPE=CF_SQL_LONGVARCHAR>)
: </cfquery>
:
: This has solved a lot of various quoting problems
: when inserting large blocks of text that may or
: may not contain quotes of different kinds.  You
: can enter text with all sorts of quotes into your
: database, and then read and display that text with
: no extra validation work.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

unless you're having problems with cold fusion not accurately inserting into
the database when using a particular datatype (ie, long in an oracle
database), then i see no point in using the <cfqueryparam> tag as the cold
fusion engine takes care of escaping the troublesome characters
(single-quotes).

your example above should work just fine like this:

<cfquery name="InsertReport" datasource="mysource">
  INSERT INTO my.reportstable (Subject, Content)
  VALUES ('#Subject#', '#Content#')
</cfquery>

if your content variable contains single-quotes the cf server will
automatically escape those for you - no extra work necessary.  this has
worked flawlessly for me on countless applications running cf4.0/4.5.1 and
sybase asa 5.0, 6.0, and ase 12.0 (which is the same database engine that ms
licenses as the engine in sql server since at least v6.0 and probably much
earlier).

of course, all of this is moot if you're using oracle and the long datatype.

to address the question about textareas though, i have to say that i don't
see where single or double-quotes could be causing problems with the display
of data in a textarea.  it might be a problem if it were a standard input,
but not with a textarea.  the only problem i've run into is where you have
html character entities representing high-ascii characters and they are
rendered as the actual characters within the textarea instead of the string
that represents them (ie, the source of the page shows &#0153;, but in the
textarea you see the trademark symbol).  of course, as you can probably
imagine, this results in the raw character being saved to the database which
is not the desired effect.  the solution is to do a quick Replace() on the
data you're putting in the textarea replacing all occurrences of the
ampersand (&) with it's html character entity (&amp;).

<cfset myvar = Replace(myvar, '&', '&amp;', 'ALL')>
<cfoutput>
<textarea>#myvar#</textarea>
</cfoutput>

<tip type="ColdFusion" author=".jeff">

anticipating your clients to use your administrative to do all sorts of
evil?  expecting to see all sorts of copy-n-pasted text from ms word
containing things like the ellipse (&#0133;), left single-quote (&#0145;),
right single-quote (&#0146;), and smart quotes (left double-quote - &#0147;
and right double-quote - &#0148;) along with all sorts of naughty high-ascii
characters?  then do the right thing and convert them to their numeric
character entity.  using cold fusion this is as simple as constructing two
lists (one containing all the actual characters which you'll use to search
for and the other containing the appropriate numeric character entities
which you'll replace them with).

<!--- initialize the variable - bad_chars --->
<cfparam name="bad_chars" default="">
<!--- initialize the variable - good_chars --->
<cfparam name="good_chars" default="">
<!--- loop through the range of high-ascii --->
<cfloop index="i" from="127" to="255">
  <!--- append each high-ascii character to a list
         contained in the variable bad_chars --->
  <cfset bad_chars = ListAppend(bad_chars, Chr(i))>
  <!--- append each numeric character entity
         representation of the high-ascii character to
         a list contained in the variable good_chars  --->
  <cfset good_chars = ListAppend(good_chars, "&##"
                                 & NumberFormat(i, '0000') & ";")>
</cfloop>

<cfquery name="InsertReport" datasource="mysource">
  INSERT INTO reportstable (
    Subject,
    Content
  )
  VALUES (
    '#Trim(ReplaceList(Subject, bad_chars, good_chars))#',
    '#Trim(ReplaceList(Content, bad_chars, good_chars))#'
  )
</cfquery>

</tip>


good luck,

.jeff

name://jeff.howden
game://web.development
http://www.evolt.org/
mailto:jeff at members.evolt.org