SQL injections on my cf pages -- What To Do?

[newcow]

Hi,
I have read on the internet about SQL Interjections and I have gone about as far as I can in checking my cf web application.

I have 3 public files, that the public can access with out being logged it. The other backend files all have the following at the beginning of the file.

<CFIF isDefined("Session.Auth") is NOT TRUE>
<CFLOCATION url="login.cfm?id=#URL.ID#">
<CFELSE>

If you are have not logged in had the login.cfm file set the following <CFSET Session.Auth ='Yes'> you will be passed back to the login.cfm to login.

I am wondering if there is any way to do sql interjections on the following three files?

http://www.carsinlondon.com/inventory.cfm

http://www.carsinlondon.com/p.cfm

http://www.carsinlondon.com/login.cfm

newcow.

 

[webmigit]

What sort of url variables are you passing? Just that ID? You might try

<cfif not isNumeric(url.ID)>
...log them out or something...
</cfif>

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[newcow]

Hi webmigit;
Well the code I posted has nothing to do with the sql interjection at all. I was just noteing that I only have 3 files that the public can access, if they access other files and don't have Session.Auth set to YES then it will cflocation them to the login.cfm file.

I think listed my 3 public accessable files and asked how I protect them from sql injections.

Or perhaps in general how do we protect our cf apps from sql injections.

newcow.

http://www.solutionswithservice.com

 

[imstillatwork]

cfqueryparam

 

[newcow]

Hi iamstillatwork

I am useing cfqueryparam on all my SQL statements around my variables.

I am guessing this is the same as putting single quotes around them i.e. '#url.or1#' .

Will this protect me completely from SQL injection?

Also I am having a bit of a problem with the below ORDER BY statement below. It will no longer sort the columns at

http://www.carsinlondon.com/inventory.cfm

when I have the variables cfqueryparam'ed or if I put single quotes around them.

ORDER BY <cfqueryparam value="#url.or1#" cfsqltype = "cf_sql_varchar"> #url.for#, 
<cfqueryparam value="#url.or2#" cfsqltype = "cf_sql_varchar"> ASC, 
<cfqueryparam value="#url.or3#" cfsqltype = "cf_sql_varchar"> ASC
</cfquery>

Here is the complete SQL statement:

<cfquery name="CarResult" datasource="#application.datasource#">
SELECT  tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName 
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
<cfif val(url.vn)> 
WHERE tblCars.VN = <cfqueryparam value="#val(url.vn)#" cfsqltype = "cf_sql_integer"></cfif>
ORDER BY <cfqueryparam value="#url.or1#" cfsqltype = "cf_sql_varchar"> #url.for#, 
<cfqueryparam value="#url.or2#" cfsqltype = "cf_sql_varchar"> ASC, 
<cfqueryparam value="#url.or3#" cfsqltype = "cf_sql_varchar"> ASC
</cfquery>

When variables are not cfqueryparam'ed the sorting of columns works fine. See inventory1.cfm at

http://www.carsinlondon.com

where the ORDER BY statement is cfqueryparam'ed. I tried single quotes and got the same thing.

You can see that the column selected to sort shows up in the URL and also it will switch from DESC to ASC.

You may notice that I also don't have #url.for# cfqueryparam'ed. When I cfqueryparam'ed it I got the following error ( see file inventory2.cfm at

http://www.carsinlondon.com

).
Syntax error (missing operator) in query expression 'Pa_RaM000 Pa_RaM001'.
This is the sql statement:

SELECT tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers ON tblCars.Dealer=tblDealers.Dealer ORDER BY (param 1) (param 2) , (param 3) ASC, (param 4) ASC

And this is the code:

<cfquery name="CarResult" datasource="#application.datasource#">
SELECT  tblCars.*, tblDealers.DealerURL, tblDealers.DealerFullName, tblVNs.VName 
FROM (tblCars INNER JOIN tblVNs ON tblCars.VN=tblVNs.VN) INNER JOIN tblDealers 
ON tblCars.Dealer=tblDealers.Dealer
<cfif val(url.vn)> 
WHERE tblCars.VN = <cfqueryparam value="#val(url.vn)#" cfsqltype = "cf_sql_integer"></cfif>
ORDER BY <cfqueryparam value="#url.or1#" cfsqltype = "cf_sql_varchar"> <cfqueryparam value="#url.for#" cfsqltype = "cf_sql_varchar">, 
<cfqueryparam value="#url.or2#" cfsqltype = "cf_sql_varchar"> ASC, 
<cfqueryparam value="#url.or3#" cfsqltype = "cf_sql_varchar"> ASC
</cfquery>

I am using an Access db but plan to all get the app working with MS SQL and mySQL.

I have looked at quite a few documents in the last 2 weeks (off and on) I have been looking into this issue.

Any URL's or direction you can offer would be great.

newcow

http://www.solutionswithserice.com

http://www.carsinlondon.com

 

[imstillatwork]

no, cfqueryparam actually validates the data type that you specify, so it gives a good extra layer of protection.

 

[xtendscott]

I am not the expert, but doesn't <cfqueryparam> protect you from executing any SQL within the variable? My understanding is that it verifies the variable for the Type of and then places it in the database, without execution of any SQL that is in the variable.

Question: Is there not a cfsqltype="CF_SQL_TEXT"? How would you validate a TEXT datatype for MS SQL2000.

 

[imstillatwork]

LONGVARCHAR

 

[imstillatwork]

CF_SQL_CHAR is a fixed length text string under 255 characters (best used when the string to be entered is the same length as the db field is set to.)

CF_SQL_VARCHAR is a variable length text string under 255 characters

CF_SQL_LONGVARCHAR is for text over 255 charecters.

 

[newcow]

what I ended up doing was the following:
1. use <cfqueryparam cfsqltype = "cf_sql_integer" value="#variable#">
This allows only 0-9 in the value. This stops injections.
2. use <cfqueryparam cfsqltype = "cf_sql_varchar" value="#variable#">
This allows only a-zA-Z0-9. This stops injections.
3. Further I added maxlength="5" to the cfqeryparam to limit the length of the variable and if some how sql injections got by the cfqueryparam cf_sql_interger or cf_sql_varchar that the maxlength="" would cut the length of the injection off.
4. Seeing that you can't use cfqueryparam in the ORDER BY statement I used cfswitch cfcase. What this does is it takes the variable being passed by the URL and compares it to a cfcase value to see if the passed value matches any of the cfcase values. If there is a match it places not the passed value but the value following the cfcase. In this way what is put in the URL.o is not put into the sql statement at all and there can be no injections. If what is passed doesn't match any of the cfcase then cfdefaultcase sets a default.
What is put in the URL.f can only be passed if it is ASC or DESC, if there is some thing injected it is set to a default value of ASC.

<!--set defaults for vars-->
<cfparam name="URL.o" default="Make" type="string"> 
<cfparam name="URL.f" default="ASC" type="string"> 
<!--check URL.f and if it is not ASC or DESC set it to ASC-->
<cfif not ((URL.f EQ "ASC") or (URL.f EQ "DESC"))> 
   <cfset URL.f = "ASC"> 
</cfif>
<cfquery name="CarResult" datasource="#application.datasource#">
SELECT  * 
FROM table
<!--check to see if there is a url.vn if not leave out the WHERE -->
<cfif val(url.vn)> 
<!--allow only interger's in the VN var and only 5 char in length-->
WHERE tblCars.VN = <cfqueryparam cfsqltype = "cf_sql_integer" maxlength="5" value="#val(url.vn)#">
</cfif>
ORDER BY
<!--check my ORDER BY vars so there is no injection-->
<cfswitch expression="#URL.o#"> 
<!--check for var URL.o and compare with cfcase values-->   <cfcase value="Y"> 
   Year #URL.f#, Make ASC, Model ASC
   </cfcase> 
   <cfcase value="MA"> 
   Make #URL.f#, Model ASC, Year ASC 
   </cfcase> 
   <cfcase value="MO"> 
   Model #URL.f#, Year ASC, Price ASC
   </cfcase> 
   <cfcase value="V"> 
   tblCars.VN #URL.f#, Make ASC, Model ASC 
   </cfcase> 
   <cfcase value="S"> 
   Style #URL.f#, Make ASC, Model ASC 
   </cfcase> 
   <cfcase value="O"> 
   Mileage #URL.f# 
   </cfcase> 	 	 	
   <cfcase value="P"> 
   Price #URL.f# 
   </cfcase>	 
<!--if no cfcase matches use this as default--> 
   <cfdefaultcase> 
   Make #URL.f#, Model ASC, Year ASC 
   </cfdefaultcase> 
</cfswitch>

You can take a look and see how this works at

http://www.carsinlondon.com/inventory.cfm

newcow
bob@bobmutch.com

http://www.solutionswithservice.com

http://www.carsinlondon.com

http://www.inventoryapp.com