Solved

sql injection, quotes and cfqueryparam

Posted on 2007-03-28
27
2,316 Views
Last Modified: 2013-12-24
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
(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




0
Comment
Question by:SidFishes
  • 7
  • 7
  • 5
  • +5
27 Comments
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 75 total points
Comment Utility
hi,

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.
0
 
LVL 29

Assisted Solution

by:QPR
QPR earned 75 total points
Comment Utility
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
http://www.sitepoint.com/print/sql-injection-attacks-safe
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 75 total points
Comment Utility
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.
0
 
LVL 1

Assisted Solution

by:jduawa
jduawa earned 75 total points
Comment Utility
I tested this...
<cfif isDefined("form.SubBtn")>
<cfquery name="getAll" datasource="ap">
      select * from temp_hr_employees where employee_firstname='#form.qVar#'
</cfquery>
<cfelse>
<form name="x" method="post" action="<cfoutput>#cgi.script_name#</cfoutput>">
      <input type="text" name="qVar">
      <input type="submit" name="SubBtn" value="submit">
</form>
</cfif>
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 '
0
 
LVL 24

Assisted Solution

by:dgrafx
dgrafx earned 75 total points
Comment Utility
Sidney,
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.
0
 
LVL 2

Expert Comment

by:zomega42
Comment Utility
Here is a successful CF injection attack:

<cfscript>
function doesnothing(str) {
0
 
LVL 2

Expert Comment

by:zomega42
Comment Utility
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>

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

 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 ?

0
 
LVL 36

Author Comment

by:SidFishes
Comment Utility
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...


0
 
LVL 1

Expert Comment

by:jduawa
Comment Utility
i couldn't get a SQL injection attack to work either...
0
 
LVL 2

Expert Comment

by:zomega42
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
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 ...
0
 
LVL 2

Expert Comment

by:zomega42
Comment Utility
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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
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 ...
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

 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;


0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
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
0
 
LVL 36

Author Comment

by:SidFishes
Comment Utility
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.








0
 
LVL 36

Author Comment

by:SidFishes
Comment Utility
sb any public form submissions with input that's viewable to the world (comments sections, blogs, ratings etc)
0
 
LVL 36

Author Comment

by:SidFishes
Comment Utility
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>
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
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)
0
 
LVL 2

Accepted Solution

by:
zomega42 earned 125 total points
Comment Utility
The numeric version is vulnerable.  I just tried this:

<cfquery>
update employees
set age = #url.age#
where employeeid = 6
</cfquery>

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)#
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
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.
0
 
LVL 36

Author Comment

by:SidFishes
Comment Utility
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
0
 
LVL 36

Author Comment

by:SidFishes
Comment Utility
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





0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
Thanks for the insight Sid
0
 
LVL 36

Author Comment

by:SidFishes
Comment Utility
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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>



0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now