sql injection, quotes and cfqueryparam

gdemaria and myself have had an ongoing difference of opinion on sql injection vuln...

this is a huge question and I'd like to get feedback from many POV's

I believe cfquerparam is the only way to go, gd believes "placing a string in single quotes will help that because the injection will simply get saved into the database (if it fits).. "http://www.experts-exchange.com/Web_Development/WebApplications/Q_22473349.html

(for the benefit of the mssql and mysql folks, cfqueryparam "Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables ColdFusion to use bind variables in the SQL statement. " It basically forces the db to take the input as typed and prevents the reading of multiple sql commands.

It's my (perhaps flawed) understanding that single quotes do not protect you

since if i do this as the example shows...

select * from mytable where somename= '#form.name#'

and pass this to the query

x'; DROP TABLE members; --

the query runs as

select * from mytable where somename= 'x'; DROP TABLE members; --'

isn't it true that by adding the x' you defeat the "value" of the quotes as the db will see that as end of first statement, start the next bit???

comments? ... happy to be proven wrong...

btw... gd... this is not meant to call you out, it's just that I want a good discussion about this as you've brought some doubts to my mind with what I thought was a "best practice"

x-posting this to mssql and mysql

LVL 36
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


normally the best way to do this is to escape all characters that need to be escaped ie
\ goes to \\
' goes to \'
" goes to \"

in php you would do this with mysql_real_escape_string,

doing this through html forms if you went and converted everything to its html entity ie

& goes to &

is another way to avoid it.  - both will display correctly when you select them out.
There is a nice function here (asp) that strips offending chars from the submitted sql.
Article also mentions different attack types and dealing with the individual types
cfqueryparam binds its variables based on the datatype specified.  Since the values are binded to variables, its ineffective to include injections in ther values because a bind cannot execute a sql statement.

  cfqueryparam also performs strict datatype verification in order to perform the bind.  So you declare the data type of the value and if it does not match, cfqueryparam throws an error.  This also helps prevent injections (at least in datatypes other than varchar/char)  because any string text such as "Delete from users" would case an error.  For example,  CF_SQL_INTEGER type with a value of 45;delete from users; would fail the data validity test.

 The other benefit of cfqueryparam is also involving the binding of  variables.  Because it uses binds, the database can cache its execution plan for reuse with each call, even though the values may vary, the plan doesn't change.  So repeat queries will run faster as it doesn't have to recompute the execution plan.  This is more beneficial with larger more complex queries.

  The downsides of cfqueryparam are miminal and it is recommended that you use it.  Here are a few shortcomings or inconveniences that can be worked around.
   cfqueryparam  cannot be used with cached queries.  So if you want to cache your query, you cannot use cfqueryparam.

  cfqueryparam also forces you to really know and manage your data type and NULL values.  This isn't necessarily a bad thing but it will force you to do more thinking and work.   for example, if you have an integer value that is not populated you need to explicitly tell cfqueryparam to place a NULL there, if it is populated and you tell it to use a NULL it will ignore the value.    Sometimes its difficult to determine causes of errors and when processing many data values, you may get unexpected errors because the datatype doesn't fit exactly.

  Because data binding causes the JDBC driver to prepare the statement before sending it to the db server, you cannot rely on SQL's implicit conversion of data types.

  cfqueryparam will throw an error rather than managing the problem, so you need to be prepared to trap errors by  cfqueryparam  and figure out the cause to present a friendly error to the user.  Otherwise you need to test everything in advance to match the explicit datatype, which kinda makes more work for you when using cfqueryparam.

  The format of the debug output is ugly and unfriendly.  My personal pet peeve.

  Preventing SQL Injections is important for security.  You can mimic the two steps of cfqueryparam by testing the value of your variables using  isNumber() or  cfparam name="form.theValue" type="INTEGER".   Wrapping a number variable in val() will strip out anything that is not a number.   This method will allow you to trap the error and throw a friendly message to inform the user of the problem with their entry.

   Note: I have tried many ways but have yet been able to force an injection in a string variable enclosed in single quotes (such as  where firstName = '#variables.first#'

   I am not saying you shouldn't use it, you probably should.  Its not a necessity if you validate your data first.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

I tested this...
<cfif isDefined("form.SubBtn")>
<cfquery name="getAll" datasource="ap">
      select * from temp_hr_employees where employee_firstname='#form.qVar#'
<form name="x" method="post" action="<cfoutput>#cgi.script_name#</cfoutput>">
      <input type="text" name="qVar">
      <input type="submit" name="SubBtn" value="submit">
this is how CF sees the query
select * from temp_hr_employees where employee_firstname='JAMES'';delete from tmp_hr_employees where employee_state is null;'
no records were deleted, so i dont think you can do sql injection by adding MIKE';delete from tmp_hr_employees where employee_state is null; because it sees that whole statement as variable to place between the ' and '
I have wondered about this issue a few times and from what I've read it is very important to protect against this type of attack.
But - the few times I've checked into testing my apps against injection code I've lost all my tables and data ...

I'm just kidding ...
Could you post another example that you feel would be a successfull attack.
I've never been able to code a successfull attack in the handfull of times I've investigated this.
So possibly I'm doing it all wrong.
Here is a successful CF injection attack:

function doesnothing(str) {
Sorry submitted accidentally.  Here's a successful attack:

function doesnothing(str) {
  return str;

<cfset newname = "ted', salary='10000000">

update employees
set name = '#doesnothing(newname)#'
where employeeid = 12

If the "newname" variable is submitting in a form, then Ted just successfully got a very nice raise.  CF will try to escape strings inside single quotes, but NOT when you apply a function to the string.  In this case the function doesn't do anything, but it does disable CF's attempt to clean the string from SQL injection.  

Certain built-in functions (trim) are safe, but I don't know if all of them are, and user-defined functions are definitely a danger (as above).  

With cfqueryparam, you are perfectly safe using UDFs, so this is okay:

update employees
set name = <cfqueryparam value="#doesnothing(newname)#">
where employeeid = 12

If you really don't like cfqueryparam, or need to cache a query, use this:

<cfset tempvar = doesnothing(newname)>
update employees
set name = '#tempvar#'
where employeeid = 12


 Thanks zomega, that was very interesting!  

  I didn't know that about escaping strings.    It makes sense that CF wouldn't escape strings passed to a function but one would think that CF would escape only at the end of all processing.  

 So that means you can't do this..

 update myTable
    set  bigText =  '#myCFC.lookupTextbyID(12)#'

 If the "big Text" returned by the CFC contains quotes, the update statement would fail ?

SidFishesAuthor Commented:

i'm not sure how that's a practical attack...or how it's one at all...

 how would an attacker "create" the udf which creates the vuln in the first place...

I've played around for a few minutes and as far as I can tell, I can't get an attack to work...just by enclosing the form var in ' ' - which is gd's method

i tried things like submitting

Sid;insert into victim(theName)values('haxor')

<form  method="post" action="test.cfm">
    <input type="text" name="theName" size="120">
    <input type="submit">

<cfif isdefined('form.theName')>
   <cfquery datasource="testing" name="injection">
         select * from victim where theName='#form.thename#'

<cfoutput query="injection">


can't get it to do anything...

I also tried the attack using url variables


<cfquery datasource="testing" name="injection">
     select * from victim where theid=#url.theid#

and even with theID not having single quotes it still fails

this is all with MySQL...any one care to try on mssql??

I'll play around some more tonight...

i couldn't get a SQL injection attack to work either...

The user doesn't create the UDF, you do.  Okay I was being quick, more realistically, you might have a function like this:

function fixCapitalization(str) {
  .... do stuff...
  return newstr;

Now, when you do your query, if you include

update employees
set name = '#fixCapitalization(name)#'

then you have an injection vulnerability.  The point is, if you use a UDF inside  your cfquery, then you are vulnerable, no matter what the UDF actually does (which is why in my original example, all it did was return the string).  If you don't use a UDF, you're fine.  Using a UDF turns off CFs injection protection.
in this example

select *
from users
where uname = '#form.uname#'
and pword = '#form.pword#'

could someone post how an injection attack could alter or delete or update or whatever to a table.
I thought I understood (in a general basic sense) sql injection attacks.
But now when testing I cannot do any damage - and I know the db structure.
Please help - I want to start vandalizing ...
In your example dgrafx, I think it's impossible. Your code is perfectly safe because CF will escape any quotes in those variables.  

Still, I wouldn't use it. What if another developer comes along next year, notices that a lot of people accidentally enter spaces in their username, and decides to fix that by putting #form.uname# inside a UDF that strips out spaces.
OK - sooooooo ...
What I'm getting from all this is that there isn't such a thing as a sql injection attack (when using ColdFusion anyway) unless there is a UDF that the vandal knows about.
Am I picking this up correctly?
If so - what is all this hoopla about sql injection attacks?
Please clarify ...

 If the SQL statement involved a number no quotes would be involved.  The best example is:


 select * from users
 where userID = #url.userID#

 now add...
 www.mysite.com/profile.cfm?userID=23;drop users;

SQL becomes...

 select * from users
 where userID = 23;drop users;

It just doesn't fly...
I've tried all that with char inputs - numeric inputs - url - form - you name it

Now that I'm actually looking at this - I just can't see how this is a threat if it doesn't do anything.

Is this kinda like the Y2K scare thing?

I'm using cf 7 & sql server 2000
SidFishesAuthor Commented:
in my post up there ^ i'd tried the same thing as dg with the integer value and had same results - haxor-proof(?)

I'm using cf7 and mysql

very interesting findings... practically everywhere you look there's big red warning signs and yet doesn't seem to be an issue with CF... even tho it's listed as best practice...

one note...there's is a vuln which neither cfqueryparam nor '' quotes protect against and that's XSS

a form submission of


 when combined with the code

<form  method="post" action="test.cfm">
      <input type="text" name="theName" size="120">
      <input type="submit">

<cfif isdefined('form.theName')>
<cfquery datasource="testing" name="injection">
      insert into victim (theName)values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.thename#">)

<cfquery datasource="testing" name="injection">
      select * from victim
<cfoutput query="injection">

runs the JS everytime your query displays the haxor record... some thing to be serious about if you have any public form submissions.

SidFishesAuthor Commented:
sb any public form submissions with input that's viewable to the world (comments sections, blogs, ratings etc)
SidFishesAuthor Commented:
and for the benefit of the paq db the solution to the XSS vuln is simple

<cfset vTheName=replace(form.thename,"<", "&lt;", "All")>
<cfset vTheName=replace(form.thename,">", "&gt;", "All")>

<cfquery datasource="testing" name="injection">
      insert into victim (theName)values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#vthename#">)
big red warning signs are fun ...
took me 3 weeks to hike back down from the mountains Jan 2 year 2000 though ...

and again in 2001 (root cellar that time)
The numeric version is vulnerable.  I just tried this:

update employees
set age = #url.age#
where employeeid = 6

Now if you pass in the url variable age="5,salary=10000000", the attack works.  It only works when the second field in question (salary in this case) is also numeric.  Otherwise you need quotes, and they get escaped.

I don't think you will be able to drop a table because CF won't allow the semicolon that you are using to start a new statement.  

So I think the moral is, you have to use cfqueryparam, or use #val(url.age)#

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I see this last example does pose an issue.
So is it an Update or Insert only thats vulnerable?
I couldn't cause any damage with a select statement.

btw - you don't need a ;(semi-colon) to start a new query
select *
from table where x = w
update table
set x = y
where x = z

The moral of the story?
The ant worked hard all summer saving food for the cold winter ahead.
The grasshopper partied instead.
When winter came the grasshopper killed the ant, ate all his food and mooched off his girlfriend.
SidFishesAuthor Commented:

Internet Storm Center Webcast:  "SQL Injection:  The Hacker's Gold Mine"
Wednesday, April 11, 2007 at 1:00 PM EDT (1700 UTC/GMT)
Featuring:  Johannes Ullrich and Dennis Hurst
SidFishesAuthor Commented:
well that was underwhelming

hilights for those that don't have a free hour

first half of webcast...

paycard systems hack (the 40 milllion CC numbers stolen last year) was sql injection

sql injection is easy when using asp, java and php unless protected by parametized code ...
that's why CF developers are just so much smarter than the rest) Of course cfqueryparam is parametizing code as well...just gives us a second level of defense beyond cf's apparently reasonably super-duper sql injection defenses (excepting of course the above examples)

second half was mostly on the ani exploit of last week however couple things of interest

ani exploit was released by the same group (in china) responsible for the dolphin stadium defacement...which was sql injection

ani exploit was most likely developed to steal WoW passwords to steal in game stuff and sell to unbelievably lame people...

people who use IE and outlook are still insane

IDS, AV and firewalls did little to prevent the issue

however my personal fav and IPS of choice at work.. Blink from eEye prevented it from day one...and speaking of eEye I just found out they are offering one free year of personal IPS and AV...highly recommended... all my home machines will get it tonight.... http://www.eeye.com/html/products/blink/personal/download/ 

That's about it...

good discussion all and it has become as clear as thin mud but i'll continue to use cfqueryparam


Thanks for the insight Sid
SidFishesAuthor Commented:
Interesting thread.  

This example worked with ms sql 2000 and mysql (if the mysql datasource uses allowMultiQueries=true).  I think"allowMultiQueries" may be turned off by default. But if you have enabled this setting its something to watch out for.
<form  method="post" action="test.cfm">
    <input type="text" name="userid" value="23; drop table importantStuff; " size="100">
    <input type="submit" >

<cfif isdefined("form.userid")>
   <cfquery datasource="MSSQL" name="testing">
            select *
            from       users
            where       userId = #form.userid#
  <cfdump var="#testing#">
   <cfquery datasource="MSSQL" name="checkForTable">
            select *
            from       importantStuff
   <cfdump var="#checkForTable#">

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.