giving problem when a formatted string used with cfquery tag

[nikhil20101]

Hi,

I have a search query returning a set of result, of which I am taking the filename. For each of this filename I need to run a SQL query to fetch a set of values from database (SQL Server 2000) . One of these fields can have values like "1,2,3" or "1" or "1,2" and so on. Each number is associated with a name and I need to display those names.

I have two tables as below

Table1

ID	Title	List	Filename
1	title1	1,2,3,4	file1.doc
2	title2	1	file2.doc
3	title3	1,2	file3.doc

Each of the numbers in the list in Table1 correspond to a name in Table2

Table2

ID	Name
1	name1
2	name2
3	name3
4	name4

Below is the code:

<cfoutput query="searchQuery" STARTROW="#startRecord#" MAXROWS="10">   <!--- Output of search query --->

     <cfquery name="getlist" datasource="data">
          SELECT list FROM Table1 WHERE filename like '%#searchQuery.fileName#%'   <!--- list returns a value like 1,2,3 as explained above --->
     </cfquery>

     <!--- to get the names I need to build a query as below  --->

     <!--- SELECT name FROM Table2 where id IN ('1','2','3') --->

     <!--- I am able to build the string  '1', '2', '3' as shown below  --->

     <cfset temp_list = #getlist.list#>
     <cfparam name="idlist" default="">
               
      <cfif len(#temp_list#) GT 1>
           <cfloop list="#temp_list#" index="i" delimiters=",">
                        <cfset idlist= idlist& " ' " &i& " ', ">   <!--- This builds string like '1','2','3' from 1,2,3 as in the table  --->
           </cfloop>
           <cfset idlist= left(idlist, len(idlist)-1)>

     </cfif>

     <cfoutput>SELECT name FROM Table2  where id IN (#idlist#)</cfoutput>

     <!--- The above query is built like SELECT name FROM Table2  where id IN ('1','2','3') , exactly the way I want--->

     <cfquery name="getname" datasource="portal">
          SELECT name FROM Table2  where id IN (#idlist#)
     </cfquery>

     <!--- The above query within cfquery tag is built like SELECT name FROM Table2  where id IN (''1'',''2'',''3'') , its not double quote but two single quotes, which is causing SQL exception --->

Can anyone please let me know about this behaviour within cfquery tag and any solution to fix this.

The error points to line SELECT statement within cfquery tag.

Below are the few lines of the stack trace.

java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '1'.
     at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source)
     at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source)
     at macromedia.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
     at macromedia.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
     at macromedia.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
     at macromedia.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)

Thanks

Nikhil

[ilssac]

Normalize your data design and your job would be much, much easier.

table1

ID	Title	Filename
1	title1	file1.doc
2	title2	file2.doc
3	tile3	file3.doc

table2

ID	Name
1	name1
2	name2
3	name3
4	name4

tableJoin

Table1_id	Table2_id
1	1
1	2
1	3
1	4
2	1
3	1
3	2

Now you could write proper SQL with joins such as

SELECT aField,bField,cField

FROM

table1 INNER JOIN

tableJoin ON table1.id = tableJoin.table1_id INNER JOIN

table2 ON table2.id = tableJoin.table2_id

WHERE

table2.id IN (<cfqueryparam value="1,2,3" list="true">)

But to answer your actual question.

SELECT name FROM Table2  where id IN (#preserveSingleQuotes(idlist)#)

Because ColdFusion automaticaly escapes single quotes inside of a string variable used in a <cfquery...> block.  For strings like "Joe isn't going to the club at 10 o'clock" that would need to be escaped to work properly in a SQL statement.  You use the preserveSingleQuotes() function when you don't want this to happen.

[-__cfSearching__-]

Normalize your data design and your job would be much, much

easier.

+1

Because ColdFusion automaticaly escapes single quotes

inside of a string variable used in a <cfquery...>

block.  For strings like "Joe isn't going to the club at 10

o'clock" that would need to be escaped to work properly in a

SQL statement.  You use hte preserveSingleQuotes() function

when you don't want this to happen.

... which unfortunately re-opens the door to sql injection that CF just tried to close auto-magically

[Dan_Bracuk]

Regarding:

For strings like "Joe isn't going to the club at 10 o'clock" that would  need to be escaped to work properly in a SQL statement.  You use the  preserveSingleQuotes() function when you don't want this to happen.

I don't think that's correct.  For strings like that you either use replace to quote the quotes or cfqueryparam to just make it work.  It's been awhile since I tried something like that, but feel free to test it if you think it will work.

I also think cfsearching is incorrect when he states that the use of preserveSingleQuotes() makes you susceptible for sql injection.  That opinion is based on observations I made when I was attempting to do that very thing while testing our own systems.

Regarding the recommendations for normalizing the database.  It's a good one but, if the OP doesn't know what that means, I've heard good things about the book, Database Design for Mere Mortals.

[-__cfSearching__-]

I also think cfsearching is incorrect when he states that

the use of preserveSingleQuotes() makes you susceptible for

sql injection.  That opinion is based on observations I

made when I was attempting to do that very thing while

testing our own systems.

I do not know what tests were used, but with databases that support multiple statements, it is relatively trivial to inject bad sql when preserveSingleQuotes() is used. Ignoring other factors like security filters of course. Google should turn up several blog entries on the topic if you are interested.

Message was edited by: -==cfSearching==-

[Reed_Powell-ttnmOb]

I would never use preservesinglequotes() around data that I did not have 100% control over.  The most common usages I have found are when fragments of the SQL statement are stored in a database table, or when the SQL statement is being created dynamically within the CF code (which tends to also include the first case).  In those cases I need to put preservesinglequotes around the data being pulled from the table or the string created by the CF code, so that CFQUERY doesn't double-up the single quotes around literal string values.  But I would never be comfortable, even on an internal intranet application, with putting preservesinglequotes around data that came into the script in the form or URL  scope - which is where the injections are going to be coming from.  This would include the seemingly innocous situations where the OPTION values on a SELECT list contain SQL statement fragments (a semi-common thing to do to control data selection and sorting on a generic data reporting page) - the safer solution would be to store those fragments in a table, and query the table based on the index values in the OPTION statements, in which case putting the preserversinglequotes() around the fragments would be safe.  Bottom line is that is is hard to live without the preservesinglequotes() calls if you are doing dynamic SQL statements, but you need to be sure you know what is in the string being passed to it.

-reed 

[nikhil20101]

Thank you all for your responses.

Modifying the query as below worked

SELECT name FROM Table2  where id IN <cfqueryparam value="#getlist.list#" list="true" cfsqltype="cf_sql_varchar">

I did not have to do all the below string forming.

<cfset temp_list = #getlist.list#>
<cfparam name="idlist" default="">
               
<cfif len(#temp_list#) GT 1>
     <cfloop list="#temp_list#" index="i" delimiters=",">
           <cfset idlist= idlist& " ' " &i& " ', ">   <!---   This builds string like '1','2','3' from 1,2,3 as in the table  --->
     </cfloop>
     <cfset idlist= left(idlist, len(idlist)-1)>

</cfif>

During  this course I got to learn a lot about SQL Injection attacks, how and  why should one use cfqueryparam, and also about preserveSingleQuotes().

Thank you all.

[nikhil20101]

  1