Link to home
Start Free TrialLog in
Avatar of SidFishes
SidFishesFlag for Canada

asked on

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).. "https://www.experts-exchange.com/questions/22473349/Syntax-error-in-UPDATE-statement.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
(http://www.unixwiz.net/techtips/sql-injection.html)

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




SOLUTION
Avatar of Raynard7
Raynard7

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zomega42
zomega42

Here is a successful CF injection attack:

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

<cfscript>
function doesnothing(str) {
  return str;
}
</script>

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

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

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:

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

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

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


 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 ?

Avatar of SidFishes

ASKER

zomega...

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">
</form>


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

<cfoutput query="injection">
      #theName#<br>
</cfoutput>

</cfif>


can't get it to do anything...


I also tried the attack using url variables

http://localhost/test/test.cfm?theid=1;insert%20into%20victim(theName)values(haxor)

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

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...
Sid,

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

<cfscript>
function fixCapitalization(str) {
  .... do stuff...
  return newstr;
}
</cfscript>

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:

 www.mysite.com/profile.cfm?userID=23

 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
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

haxor<script>alert('gotcha')</script>

 when combined with the code

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

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

<cfquery datasource="testing" name="injection">
      select * from victim
</cfquery>
<cfoutput query="injection">
      #theName#<br>
</cfoutput>
 </cfif>

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








sb any public form submissions with input that's viewable to the world (comments sections, blogs, ratings etc)
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#">)
</cfquery>
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)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start 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.
fyi

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
https://www.sans.org/webcasts/show.php?webcastid=90791
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

thx





Thanks for the insight Sid
hey dg et al ... a .net elitist is slagging CF ...want to jump in?

https://www.experts-exchange.com/questions/22505336/coldfusion-vs-php-asp-net.html
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" >
</form>

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