Databases
StackOverflow2013
Postsdbo
POHow to override SQL sanitization in ColdFusion
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to override SQL sanitization in ColdFusion
    text
    copied!<p>I have the unfortunate task of cleaning up a bunch of old ColdFusion code. Queries are all over the place, I am working on moving them all to common CFCs for easier maintenance.</p> <p>I am running into a problem because <code>cfquery</code> is automatically converting the single quotes to double-single-quotes. How can I override that behavior?</p> <p>More specific information is below.</p> <hr> <p>So here is the query I started with:</p> <pre><code>&lt;cfquery name="getObjectInfo" datasource="#BaseDS#"&gt; SELECT groupName AS lastname, '[Group]' AS firstname FROM groups WHERE groups.group_id = #objectreference_id# &lt;/cfquery&gt; </code></pre> <p>The weird thing here is that a literal is being "selected", because of the way we want it displayed (again, I didn't write this, I'm just trying to clean it up a little). So in the common function, there is an optional parameter for the select clause:</p> <pre><code> &lt;cffunction name="fSelGroup" access="public" returntype="query" hint="Returns query selecting given group."&gt; &lt;cfargument name="intGroupID" type="numeric" required="true" hint="ID of group to be returned." /&gt; &lt;cfargument name="strSelectAttributes" type="string" required="false" hint="Attributes to be selected in query" default="*" /&gt; &lt;cfquery name="getObjectInfo" datasource="#Application.DataSource#"&gt; SELECT #Arguments.strSelectAttributes# FROM Groups WHERE Group_ID = #Arguments.intGroupID# &lt;/cfquery&gt; &lt;cfreturn getObjectInfo /&gt; &lt;/cffunction&gt; </code></pre> <p><strong>Here is the problem</strong>: When I pass in <code>"GroupName AS LastName, '[Group]' AS FirstName"</code> for the strSelectAttributes parameter, the query that is sent to the database is:</p> <pre><code>SELECT GroupName AS LastName, ''[Group]'' AS FirstName FROM Groups WHERE Group_ID = 4 </code></pre> <p>You see, my quotes got "sanitized" into an invalid query.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload