forms - When and where to encode user input? -

i storing data submitted users database encoded such:

<cfquery> insert dbo.mytable (userid, comment) values ( <cfqueryparam value="#form.userid#" cfsqltype="cf_sql_integer"/>, <cfqueryparam value="#encodeforhtml(form.comment)#" cfsqltype="cf_sql_nvarchar"/> ) </cfquery> 

evidently not right way because have escaped characters in db table useful html output , difficult perform searches on within sql server.

so how ensure apply encodeforhtml() on input before hits server , canonicalize() data received stored in db?

mitigate potentially db-harmful text when heads towards db: pass parameter, not hard-coded sql statement, have kinda done in example. still exposing not parameterising id value. rule, sql should go in <cfquery>'s sql string; data values should passed parameters.

similarly, mitigate risk user-provided data might expose when use data. not when goes storage, when use it. encodeforhtml() appropriate stuff being written html. it's no if it's being passed on url, or used in javascript, etc. there different mitigation approaches (urlencodedformat() , encodeforjavascript() respectively). point being handle mitigation on use-by-use basis, not generically.

and how ensure done (you ask this)? well... write code diligently , have rigorous code review , qa process (with qa doing pen. tests).