Request For Comments on Database Security

[ice78991]

I am using the following method to query a database


This code is in a .cfm page
<!--- GET MEMBER DETAILS --->
<CFINVOKE COMPONENT="components/manageAdvertiser"
METHOD="InitDetails"
MemberInfo = #Trim(mid)#
RETURNVARIABLE="Details">
</CFINVOKE>


It accesses a function stored in a .cfc

<cffunction name="InitDetails_admcomp" access="public" returntype="query" output="no">
<cfargument name="MemberInfo" type="string" required="yes">

<cfset var Details = "">

<CFQUERY NAME="Details" DATASOURCE="#APPLICATION.DB#" USERNAME="#APPLICATION.UN#" PASSWORD="#APPLICATION.PW#">
SELECT *
FROM Profile
WHERE MemberID_mpe = #Trim(ARGUMENTS.MemberInfo)#
ORDER BY CreatedDate_mpe
</CFQUERY>

<cfreturn Details>
</cffunction>

Is this secure or should I be implementing cfqueryparam
( which will take a lot of rewriting) I am using SQL Server 2005 so databinding is not an issue

 

[Sarky78]

As a rule of thumb you should always use cfqueryparam for embedded queries. Not only will the execute quicker, they also offer more security, by ensuring the data is of the correct type.

If you are looking for more security then you have got to make this and all of your other queries stored procedures and then call them via cfstoredproc. this will also execute quicker as the stored procedure will be compiled on the database server, and it also makes sense to have the query there as that is what the db server is for!

Also, on a side note, the query will execute quicker if you specify the fields that you want, and not just put select *. I've had problems in the past where cf has cached a query when I've used select *, then when i add a new field to the table it doesn;t get picked up due to the caching

Hope this helps!

Tony

 

[ice78991]

Thanks for your help. As far as the data checking goes, would not my code offer the same degree of security ?

<cfargument name="MemberInfo" type="string" required="yes">

On the point of cfqueryparam executing quicker, I read somewhere that this is only true on Oracle systems because they make use of data binding, whereas SQL Server does not. Maybe someone could verify this?

 

[htower]

here is a good link to read on cfqueryparam:

http://www.adobe.com/devnet/coldfusion/articles/ben_forta_faster.html