Protection from SQL injection in ColdFusion

Question

I am trying to improve my application's security. Whenever I receive data from the user (whether through POST or GET) that is supposed to be an integer, I validate that appropriately. But often the data is VARCHAR, and sometimes can contain HTML.

How do I protect my DB from SQL injection in that case?

Does <cfqueryparam value="#form.textInput#" cfsqltype="cf_sql_varchar"> protect the query from sending a malicious SQL statement inside a VARCHAR value?

Gunstock

Answer 1

The short answer to your question is 'yes'.

I block hacking attempts using three methods.

1. I use cfqueryparam in all my database queries. I will use cfparam at the top of the template/cfm files for url scope variables.

2. I have used Portcullis or variants of it. You can get it from http://portcullis.riaforge.org/. Portcullis will also defend against some cross site scripting attacks.

3. I use Windows IIS 7.5 (Windows Server 2008 R2). I use the URL Rewrite feature to block the bulk of URL based attacks. You can do similar things with Apache and the rewrite that it supports. Here are my IIS URL Rewrite rules:

   <?xml version="1.0" encoding="UTF-8"?>
   <appcmd>
       <CONFIG CONFIG.SECTION="system.webServer/rewrite/globalRules" path="MACHINE/WEBROOT/APPHOST" overrideMode="Inherit" locked="false">
           <system.webServer-rewrite-globalRules>
               <rule name="SQL Injection - EXEC - SCRIPT_NAME" stopProcessing="true">
                   <match url="^.*EXEC\s*[\(|%28].*$" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - EXEC - QS" stopProcessing="true">
                   <match url=".*" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                       <add input="{QUERY_STRING}" pattern="^.*EXEC\s*[\(|%28].*$" />
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - CAST - SCRIPT_NAME" stopProcessing="true">
                   <match url="^.*CAST\s*[\(|%28].*$" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - CAST - QS" stopProcessing="true">
                   <match url=".*" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                       <add input="{QUERY_STRING}" pattern="^.*CAST\s*[\(|%28].*$" />
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - DECLARE - SCRIPT_NAME" stopProcessing="true">
                   <match url="^.*DECLARE.*$" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - DECLARE - QS" stopProcessing="true">
                   <match url=".*" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                       <add input="{QUERY_STRING}" pattern="^.*DECLARE.*$" />
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - NVARCHAR - SCRIPT_NAME" stopProcessing="true">
                   <match url="^.*CHAR\s*[\(|%28].*$" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - NVARCHAR - QS" stopProcessing="true">
                   <match url=".*" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                       <add input="{QUERY_STRING}" pattern="^.*CHAR\s*[\(|%28].*$" />
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - sp_password - SCRIPT_NAME" stopProcessing="true">
                   <match url="^.*sp_password.*$" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - sp_password - QS" stopProcessing="true">
                   <match url=".*" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                       <add input="{QUERY_STRING}" pattern="^.*sp_password.*$" />
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - xp - SCRIPT_NAME" stopProcessing="true">
                   <match url="^.*%20xp_.*$" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
               <rule name="SQL Injection - xp - QS" stopProcessing="true">
                   <match url=".*" />
                   <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
                       <add input="{QUERY_STRING}" pattern="^.*%20xp_.*$" />
                   </conditions>
                   <serverVariables>
                   </serverVariables>
                   <action type="CustomResponse" statusCode="403" statusReason="Forbidden" statusDescription="Forbidden" />
               </rule>
           </system.webServer-rewrite-globalRules>
       </CONFIG>
   </appcmd>
   

These rules are added to the C:\Windows\System32\inetsrv\config\applicationHost.config file for IIS. However I do ****NOT**** recommend that you directly edit this file. One mistake and IIS will not load. Instead copy & paste the rules above and save them as "iis-global-rewrite.xml". Then run the following batch file to add the rules to your IIS server:

C:\Windows\System32\inetsrv\appcmd.exe set config -in < iis-global-rewrite.xml

The IIS rewrite rules should work with IIS 7.0 (Windows Server 2008) but I have not tested it.

These rules could also be applied to a single site using the web.config file if you do not have access to the server.

Why do I use three different methods for protection? Because none of them cover all the bases. The IIS rewrite rules only protect against URL based attacks. Hackers can also use form submission attacks that do the same thing. I prefer the IIS rules as a first line of protection because it will work with all sites on the server including PHP, ASP, etc. Portcullis is a good second line of defense for ColdFusion because it will catch form based attacks and some cross site scripting attacks. The last line of defense is the cfqueryparam/cfparam code which protects against URL/form based SQL injection attacks.

If all three of these methods are used the server/site should be very secure. I would still advise reviewing server logs from time to time as attacks do evolve and improve.

Softhearted

Wow, that is just perfect. I am actually running CF on IIS, so I will definitely look into securing my web app with some more advanced rewrite rules. Thanks! – Gunstock

IIS URL Rewrite and Apache mod_rewrite are very useful tools for defense and SEO. blogs.iis.net/ruslany/archive/2009/04/08/… has some useful examples for IIS URL Rewrite. – Softhearted

Answer 2

The short answer is yes.

cfqueryparam will stop some sql injection attacks from occuring.

There are other attack variables that can be used, so be careful, but well written coldfusion can be very safe.

Be wary of Cross site scripting attacks if you are storing and later displaying input html, be especially careful of javascript tags.

Amu

The only time that cfqueryparam will not stop a SQL injection attack will have nothing to do with the nature of the attack, but the nature of code on the database. For instance, if you were to call some database procedure that took a varchar argument and ran it dynamically as SQL, no amount of parametized querying will help you. CfQueryParam will always prevent SQL injection attacks when using plain SQL (no database procedure calls, etc). – Ube

Don't forget to use maxlength also on variable-length text fields where suitable. If you know a text field will only have strings of up to 16 characters long, for example, there's no need to allow longer strings in as they should be flagged as an error. – Heigho