<thecfguy blog="sharing thought">

Hi All,

SQL Injection attach is biggest problem for web developer. I will always suggest to user <cfqueryparam> in condition which give better performance and top of that it will stop sql injection. While buiding sql query runtime it is not really possible to use <cfqueryparam> tag in such case we require to escape special sql characters.

Single quotes ( ' ) is most dangerous character from where sql Injection normally start and solution is really simple, just replace single quotes with two times single quotes ( '' ) and you are preety safe. I used to create one function (let's say sqlSafe) in Application.cfc file or any class file which is extended to Application.cfc so it is easily accessible to everywhere.

<cffunction name="sqlSafe" access="public" returntype="string" output="false">
    <cfargument name="strVal" required="true">
    <cfscript>
        var sqlList = "',%";
        var replacementList = "'',\%";
        return trim(replaceList( strVal , sqlList , replacementList ));
    </cfscript>
    <cfreturn retStr>
</cffunction>

We just need to call this function before attaching any user input string in sql query. I really appreciate any suggestion which make this function much stronger.