In ColdFusion what do single and double quotes around attribute values do?

Question

For example what do the "" around #Trim(FORM.fromfirstname)# do? I'm adding <cfqueryparam...> tags and am wondering if the quotes are still required?

<CFQUERY NAME="account" DATASOURCE="#APPLICATION.centralDSN#">
    SELECT * 
    FROM users
    WHERE (firstname =<CFQUERYPARAM VALUE="#Trim(FORM.fromfirstname)#">)
        AND (lastname = <CFQUERYPARAM VALUE="#Trim(FORM.fromlastname)#">)
        AND (email = '#Trim(FORM.fromemail)#')
</CFQUERY>

Here's one that doesn't use quotes for the WHERE clause:

<CFIF getUser.RecordCount>
    <CFQUERY NAME="cUser" DATASOURCE="#APPLICATION.centralDSN#">
        UPDATE users
            SET mailing_list = <CFIF IsDefined("FORM.mailing_list")>#FORM.mailing_list#<CFELSE>0</CFIF>
        WHERE user_id = #getUser.user_id#
    </CFQUERY>
</cfif>

EDIT: if they don't do anything there's no harm in keeping them, correct? In another file I found examples like

    to="#ListFirst(EnglishEmailList)#"
    cc="#ListRest(EnglishEmailList)#"

So if they are already there I'll leave them?

Answer 1

SQL needs quotes for strings.

Quotes are part of standard SQL syntax to indicate a string (as indeed they are in almost every language).

If it didn't have quotes then SQL parser would have no idea where the string ended and the SQL continued.

Quotes are not necessary for numbers - where there is no ambiguity about where the value ends.

Remember also that the #hashes# are nothing to do with SQL - they are entirely on the CFML side. When running the cfquery tag, CF evaluates the body (including any hash expressions it contains) to create an SQL string, which is then passed to the database, (along with additional settings/parameters/etc). The SQL server has no knowledge of what parts of that string was hard-coded and what parts might have been evaluated from hashes.

cfqueryparam doesn't need quotes.

When you're fixing the queries to use cfqueryparam, you are creating parameters, and the tag handles everything necessary to indicate strings/etc to the SQL database. (You never need to wrap the cfqueryparam tag itself in quotes.)

Within the cfqueryparam tag, it makes zero difference whether or not you use quotes for the attributes - these three all produce the same result:

<cfqueryparam value="#var#" />
<cfqueryparam value='#var#' />
<cfqueryparam value=#var# />

Answer 2

If you're using <cfqueryparam> then you never need to use quotes.

if you're comparing a string in SQL (and not using <cfqueryparam>) then you need to use quotes. If you're comparing to a number (and not using <cfqueryparam>) then you don't need quotes.