Skip to main content
B
brds
Participant
August 13, 2008
Question

concatenation messing up SQL query

  • August 13, 2008
  • 3 replies
  • 833 views
I have an array of SQL commands that need to get run. To run them, i'm doing this:

quote:

<cfloop from="1" to="#arraylen(queries)#" index="i">
<cfquery name="runquery" datasource="mydatasourcehere">
<cfoutput>#queries #</cfoutput>
</cfquery>
</cfloop>


That should work, but for some reason, when the query is ran, all of the ' marks are changed to " marks. I did a dump on the array itself and, in the array, they are all ' marks.

is there a way to either: 1) make sql run the command with " marks, or 2) make the output stop having " marks instead of ' marks?
    This topic has been closed for replies.

    3 replies

    N
    Newsgroup_User
    Inspiring
    August 13, 2008
    -==cfSearching==- wrote:

    > ColdFusion automatically escapes single quotes to prevent a common sql
    > injection attack.

    That, and to handle the possibility of string data that contains the
    single quote.

    <cfset string="I'll be home late, said Mr. O'Mally.">

    <cfquery datasource = "myStory">
    INSERT INTO Chapters
    (Sentance)
    ('#string#')
    </cfquery>

    B
    brdsAuthor
    Participant
    August 13, 2008
    quote:
    Originally posted by: Newsgroup User
    -==cfSearching==- wrote:

    > ColdFusion automatically escapes single quotes to prevent a common sql
    > injection attack.

    That, and to handle the possibility of string data that contains the
    single quote.

    <cfset string="I'll be home late, said Mr. O'Mally.">

    <cfquery datasource = "myStory">
    INSERT INTO Chapters
    (Sentance)
    ('#string#')
    </cfquery>



    Well how would i do this if my sql query is in the form of:
    (Where column1 is an auto_increment PK)
    INSERT INTO TABLE (col2, col3, col4) VALUES ('something', integer, 'something else');

    I have a sql command like the one shown above stored in an array along with N number of other queries... all in the same form.
    D
    Dan_Bracuk
    Inspiring
    August 13, 2008
    quote:
    Originally posted by: brds

    Well how would i do this if my sql query is in the form of:
    (Where column1 is an auto_increment PK)
    INSERT INTO TABLE (col2, col3, col4) VALUES ('something', integer, 'something else');

    I have a sql command like the one shown above stored in an array along with N number of other queries... all in the same form.

    If you actually have an array of insert queries to the same table, there is another way you might consider. It will take less code, probably run faster, handle all apostrophes, octotorps, and other troublesome characters, and be more secure.

    Instead of this:
    <cfloop>
    <cfquery>
    closing tags.

    try this
    <cfquery>
    insert into mytable
    (field1, field2, etc)
    <cfloop>
    select distinct <cfqueryparam cfsqltype="something" value="#value1#">
    , <cfqueryparam cfsqltype="something" value="#value2#">
    etc
    from some_small_table
    <cfif loop not finished>
    union
    closing tags
    -
    -__cfSearching__-
    Inspiring
    August 13, 2008
    brds wrote:
    > for some reason, when the query is ran, all of the ' marks are changed to " marks.

    ColdFusion automatically escapes single quotes to prevent a common sql injection attack. The PreserveSingle quotes function can be used to disable this behavior. However, it use may expose your database to sql injection. Dynamic sql also lacks the performance advantages offered by using cfqueryparam.

    http://livedocs.adobe.com/coldfusion/7/htmldocs/00000596.htm
    P
    paross1
    Participating Frequently
    August 13, 2008
    Did you try using the PreserveSingleQuotes() function?

    Phil
    B
    brdsAuthor
    Participant
    August 13, 2008
    quote:
    Originally posted by: paross1
    Did you try using the PreserveSingleQuotes() function?

    Phil


    yep... my string of will cfoutput to the screen with single quotes, but (even after using preservesinglequotes) the sql command has quotation marks around the VALUES items.
    B
    brdsAuthor
    Participant
    August 13, 2008
    quote:
    Originally posted by: brds
    quote:
    Originally posted by: paross1
    Did you try using the PreserveSingleQuotes() function?

    Phil


    yep... my string of will cfoutput to the screen with single quotes, but (even after using preservesinglequotes) the sql command has quotation marks around the VALUES items.


    Never mind... got it working... it raises another question though... if i'm trying to insert something via the sql command, and it has a ' in it (i.e. Somebody's Name) how do i handle that so that SQL knows it's part of the name?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices