How I can encode/escape a varchar to be more secure without using cfqueryparam?

Question

How I can encode/escape a varchar to be more secure without using cfqueryparam? I want to implement the same behaviour without using <cfqueryparam> to get around "Too many parameters were provided in this RPC request. The maximum is 2100" problem. See: http://www.bennadel.com/blog/1112-Incoming-Tabular-Data-Stream-Remote-Procedure-Call-Is-Incorrect.htm

Update:

• I want the validation / security part, without generating a prepared-statement.
• What's the strongest encode/escape I can do to a varchar inside <cfquery>?
• Something similar to mysql_real_escape_string() maybe?

Answer 1

As others have said, that length-related error originates at a deeper level, not within the queryparam tag. And it offers some valuable protection and therefore exists for a reason.

You could always either insert those values into a temporary table and join against that one or use the list functions to split that huge list into several smaller lists which are then used separately.

SELECT name , 
       ..... , 
       createDate
FROM somewhere
WHERE (someColumn IN (a,b,c,d,e)
       OR someColumn IN (f,g,h,i,j)
       OR someColumn IN (.........));

Answer 2

cfqueryparam performs multiple functions.

1. It verifies the datatype. If you say integer, it makes sure there is an integrer, and if not, it does nto allow it to pass

2. It separates the data of a SQL script from the executable code (this is where you get protection from SQL injection). Anything passed as a param cannot be executed.

3. It creates bind variables at the DB engine level to help improve performance.

That is how I understand cfqueryparam to work. Did you look into the option of making several small calls vs one large one?