Injection attacks occur when data is sent to an interpreter which contain unintended commands with the data that are run by the interpreter. The most common injection flaw in web applications are SQL, but it is also possible to have injection flaws effect LDAP queries, XPath queries, and OS commands. We are going to cover SQL injections, but the techniques used to validate and control the input to the SQL interpreter are applicable to the other types of injections.
In the earlier Database chapter you saw the use of the cfqueryparam tag. It is one of the simplest steps you can take to help prevent SQL injection attacks on your web application, but it can only be used in the WHERE clause, INSERT values, and UPDATE values of an SQL statement. Other parts of an SQL statement require more work to protect against it. The example below is using cfqueryparam, but it is still susceptible to SQL injection attack through the ORDER BY clause.
<cfparam name="url.state" default="" /> <cfparam name="url.orderby" default="LASTNAME" /><cfquery name="request.listing" datasource="cfartgallery"> SELECT ARTISTID, FIRSTNAME, LASTNAME, EMAIL, THEPASSWORD, ADDRESS, CITY, STATE, POSTALCODE, PHONE, FAX FROM ARTISTS WHERE 1=1 <cfif Len(url.state)> AND STATE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#url.state#" /> </cfif> ORDER BY #url.orderby# </cfquery>
By validating the URL parameters against a list of values we know to be good while changing all references from the URL scoped variables to variables we explicitly set, the SQL injection vulnerability is removed. The cfparam's were also removed because they are now redundant from the use of StructKeyExists() in the if statements.
<cfscript>
// list of valid values
variables.validStates = "CA,CO,DC,FL,GA,NM,NY,OK,SD";
variables.validOrderBys = "LASTNAME,CITY,STATE";
// check what was passed against list of valid values
if ( StructKeyExists(url, "state") AND ListFind(variables.validStates, url.state) ) {
variables.state = url.state;
} else {
variables.state = "";
}
if ( StructKeyExists(url, "orderby") AND ListFind(variables.validOrderBys, url.orderby) ) {
variables.orderby = url.orderby;
} else {
variables.orderby = "LASTNAME";
}
</cfscript>
<cfquery name="request.listing" datasource="cfartgallery">
SELECT ARTISTID, FIRSTNAME, LASTNAME, EMAIL, THEPASSWORD, ADDRESS, CITY, STATE, POSTALCODE, PHONE, FAX
FROM ARTISTS
WHERE 1=1
<cfif Len(variables.state)>
AND STATE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.state#" />
</cfif>
ORDER BY #variables.orderby#
</cfquery>
While the above code removes the SQL injection, it can be made better. First, remove the hard coded list of States and reuse the query used to generate the list of States for the filter in the validation. Next, change the ‘Order By’ values from columns used in the database to a structure that references them. While this example has some pretty common and guessable columns names, the main purpose is to make it more difficult for an attacker to get knowledge of the database structure the web application is using.
<cfscript>
// valid values
variables.validStates = ValueList(request.uniqueStates.STATE);
variables.validOrderBys = {'name': {column: 'LASTNAME', display: 'Name'}
,'city': {column: 'CITY', display: 'City'}
,'state': {column: 'STATE', display: 'State'}};
// check what was passed against valid values
if ( StructKeyExists(url, "state") AND ListFind(variables.validStates, url.state) ) {
variables.state = url.state;
} else {
variables.state = "";
}
if ( StructKeyExists(url, "orderby") AND StructKeyExists(variables.validOrderBys, url.orderby) ) {
variables.orderby = url.orderby;
} else {
variables.orderby = "name";
}
</cfscript>
<cfquery name="request.listing" datasource="cfartgallery">
SELECT ARTISTID, FIRSTNAME, LASTNAME, EMAIL, THEPASSWORD, ADDRESS, CITY, STATE, POSTALCODE, PHONE, FAX
FROM ARTISTS
WHERE 1=1
<cfif Len(variables.state)>
AND STATE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.state#" />
</cfif>
ORDER BY #variables.validOrderBys[variables.orderby]["column"]#
</cfquery>
The technique shown above works in other parts of SQL statements as well.
It is generally noted that using stored procedures also resolves SQL injection, but that is not always the case. It is still possible to have a SQL injection occur inside the stored procedure depending upon how it is written, particularly if there is string concatenation using the input variables. It is recommended to use cfstoredproc, cfprocparam, and cfprocresult instead of calling a stored procedure through SQL.
<!--- bad ---> <cfquery name="request.SalesReport" datasource="Sales"> exec sp_SalesReport '#url.division#', '#url.startDate#', '#url.endDate#' </cfquery><!--- better ---> <!--- similar validation of parameters done before execution ---> <cfstoredproc procedure="sp_SalesReport" datasource="Sales"> <cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#variables.division#" /> <cfprocparam type="in" cfsqltype="cf_sql_date" value="#variables.startDate#" /> <cfprocparam type="in" cfsqltype="cf_sql_date" value="#variables.endDate#" /> <cfprocresult name="request.SalesReport" result="1" /> </cfstoredproc>
ORM makes it easier to interact with the database without having to deal with SQL; however, it is still possible for an SQL injection to occur if you do not validate values as was done when working with cfquery and cfstoredproc. When using ormExecuteQuery(), use the Params attribute, which will bind the value like cfqueryparam does for cfquery.
<!--- bad---> <cfset artists = ormExecuteQuery( "FROM artist WHERE firstname like '#url.firstName#%' ORDER BY lastname") /><!--- better ---> <!--- similar validation of parameters done before execution ---> <cfset artists = ormExecuteQuery( "FROM artist WHERE firstname like :firstname ORDER BY lastname", { firstname: "#variables.firstName#%"}) />
Additional Resources: