How can I prevent SQL injection attacks?
SQL injection attacks occur when a client manipulates a web page to pass invalid data to a query. This can be down to force errors, bypass security, or even delete data. The <cfqueryparam> tag prevents SQL injection by binding values into the query; the bound values cannot be interpreted as SQL. It also results in faster queries.
SELECT *
FROM USERS
WHERE username = '#FORM.username#'
AND password = '#FORM.password#'
</cfquery>
becomes
SELECT *
FROM USERS
WHERE username = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.username#">
AND password = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.password#">
</cfquery>
In general, <cfqueryparam> should be used whenever a dynamic attribute is specified in a query.
This question was written by James Holmes.
It was last updated on January 19, 2006 at 4:14:46 PM EST.
CFML Referenced
Categories
Comments
Comment made by darron on January 26, 2006 at 8:12 AM
There are some database drivers that do not support the cfqueryparam tag. Additionally, cfqueryparam cannot be used with cachedafter and cachedwithin.
In these situations, use the val() function if the dynamic data is numeric:
<cfquery name="QCheckUser" datasource="blahblah"> SELECT * FROM users WHERE user_id = #val(user_id)# </cfquery>
When the type is not numeric, try replacing all of the semicolons to avoid injection:
<cfquery name="QCheckUser" datasource="blahblah"> SELECT * FROM users WHERE username = '#replace(username, ';' , '', 'all')#' </cfquery>
Comment made by Raymond Camden on January 26, 2006 at 9:09 AM
Darron, you are not correct. The tag will work in any db - it just won't support binding if the back end system does not. It will still do validation though.
You are correct about caching not working. I typically do my own caching though.
Comment made by darron on January 26, 2006 at 9:43 AM
Are you sure I was incorrect about the drivers Ray? Trying to use cfqueryparam with an ODBC driver will result in a runtime error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Optional feature not implemented
In these situations, one of the alternatives I provided will work.
Comment made by Raymond Camden on January 26, 2006 at 9:59 AM
It may be the TYPE you used. I believe for ODBC, if you use DATE, it won't work, but TIMESTAMP will. (Or reverse that.) So it isn't the TAG, but the TYPE, if that makes sense.
Comment made by Aaron Rouse on February 13, 2006 at 3:57 PM
Would the Val() always work though? I know if your number is rather large that CF will output it in scientfic format. Just guessing that the database or database driver handles it correctly. I just tested it with Oracle and ran without error:
<cfset myNum = "1231237612934761023476120346120364123046" /> <cfquery name="test" datasource="#App.DataSource#"> SELECT * FROM RESERVATIONS WHERE ID = #Val(Variables.myNum)# </cfquery> <cfquery name="test2" datasource="#App.DataSource#"> SELECT * FROM RESERVATIONS WHERE ID = #Val(Variables.myNum)# </cfquery>
Which gives in the debug information: test (Datasource=RRS, Time=16ms, Records=0) SELECT * FROM RESERVATIONS WHERE ID = 1.23123761293E+039 test2 (Datasource=RRS, Time=0ms, Records=0) SELECT * FROM RESERVATIONS WHERE ID = 1231237612934761023476120346120364123046
Comment made by Michael Pumo on November 2, 2006 at 8:30 AM
There is a really good UDF out there on the web for creating safe strings for cfquery - although this amazingly isn't on the cflib site.
I didn't write it but I've lost the details who have. I'm sure they won't mind me passing it on though.
<cfscript> function sqlSafe(string) { var sqlList = "-- ,'"; var replacementList = " #chr(38)##chr(35)##chr(52)##chr(53)##chr(59)##chr(38)##chr(35)##chr(52)##chr(53)##chr(59)##chr(38)##chr(35)##chr(51)##chr(57)##chr(59)#"; return trim(replaceList( string , sqlList , replacementList )); } </cfscript>
Cheers.
Comment made by Raymond Camden on November 2, 2006 at 9:46 AM
Michael - but why use it when you can use cfqueryparam? This seems like overkill.
Comment made by Michael Pumo on December 29, 2006 at 10:43 PM
Hi again Ray...
Well, I guess I'd use this function just because it feels nicer to me to have certain characters replaced with their Unicode equivalents. I've had no problems with this so far and it works a treat for my needs.
There were some other issues as to why I chose this method at the time (which has stuck since) but I can't remember right now.
Then again I'm pretty much a newbie at all this but I hope it can be of help for someone.
Thanks
Comment made by Richard on March 23, 2007 at 8:59 AM
I got an issue with this. I also have this method in my queries, but when i tested it, it didn't work. This is because my 'order by' and type (DESC) is variable and also triggered by the url variable.
The statement which is now executed is:
Select * From tbl_calls Where tbl_statusid = ? AND servicemanagerid = ? Order by id desc;DELETE tbl_calls Query Parameter Value(s) - Parameter #1(CF_SQL_INTEGER) = 1 Parameter #2(CF_SQL_INTEGER) = 28
So you see he still adds the DELETE tbl_calls to the query.
How can i do this? If i hardcode add "order by ID desc" then it works fine, but if these two are also variables within the URL you still get to execute SQL DELETE...
Thanks for you help.
Comment made by Germann on March 30, 2007 at 8:51 AM
This is where you can use the function mentioned above: select * from tbl where fld1 = <cfqueryparam value="#val#" cfsqltype="cf_type..." /> order by #sqlSafe(url.orderBy)#
Comment made by gavy on December 12, 2007 at 5:53 AM
Try this:
<CFSET SQL_Words="[ ;](insert +into.+values|drop +table|create +table)"> <CFLOOP COLLECTION="#url#" ITEM="var"> <CFIF IsSimpleValue(Evaluate(var)) AND REFindNoCase(SQL_Words, Evaluate(var)) NEQ 0> <CFTHROW TYPE="SQLAttack" MESSAGE="Invalid URL value passed."> </CFIF> </CFLOOP> <CFLOOP COLLECTION="#form#" ITEM="var"> <CFIF IsSimpleValue(Evaluate(var)) AND REFindNoCase(SQL_Words, Evaluate(var)) NEQ 0> <CFTHROW TYPE="SQLAttack" MESSAGE="Invalid Form value passed."> </CFIF> </CFLOOP>
Comment made by nguyen vu on June 11, 2008 at 4:34 AM
why connect database of coldfusion? I can't add database