Solved

Extra single quote (apostrophe) added around variable in SQL query

Posted on 2006-06-23
1
320 Views
Last Modified: 2013-12-24
I’ve come across something odd that I’d like some help with or at least some explanation of why it happens.  If I run this query, it runs just fine:

<cfquery name="GetUserInfo" datasource="myDataBase">
SELECT ID, name, surname
FROM demographics
WHERE ID IN ('007','008')
</cfquery>

But if I try to use a variable to store what ID’s I want to search over, like so:
 
<cfset teststring = "'007','008'">
 
<cfquery name="GetUserInfo" datasource="myDataBase">
SELECT ID, name, surname
FROM demographics
WHERE ID IN (#teststring#)
</cfquery>
 
 
... I get the following SQL error:

   Incorrect syntax near '007'
 
   SELECT ID, name, surname FROM demographics WHERE ID IN (''007'',''008'')

Note that the two ID numbers have double single quotes around them!  Why did that happen?  Can anyone else replicate this issue, or is it something specific to my server?  How does SQL even know that I inserted a variable, since the ColdFusion variable is resolved before the SQL code is executed?  Is this a setting in the SQL Server to prevent code injection?  Thanks!
0
Comment
Question by:tihetal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 10

Accepted Solution

by:
rob_lorentz earned 125 total points
ID: 16970281
<cfquery name="GetUserInfo" datasource="myDataBase">
    SELECT ID, name, surname
    FROM demographics
    WHERE ID IN (#preservesinglequotes(testString)#)
</cfquery>

0

Featured Post

Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
URL redirect 4 72
Soundcloud.com 4 115
How can i point a subdomain to directory? 5 44
Testing site for http code 200 or curl equiv for Windows 2 100
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
What You Need to Know when Searching for a Webhost Provider
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question