3

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?
Improve this question
9
  • this is kinda a duplicate question, see "Is not including the cfsqltype for cfqueryparam still useful for sql injection protection?" stackoverflow.com/questions/6913696/… Commented Aug 23, 2011 at 18:57
  • "Is not including the cfsqltype for cfqueryparam still useful for sql injection protection?" stackoverflow.com/questions/6913696/… Commented Aug 23, 2011 at 18:58
  • no, I'm asking precisely how I can encode/escape a varchar to be more secure without using cfqueryparam. Thanks. Commented Aug 23, 2011 at 19:04
  • Not really.Henry is asking more Commented Aug 23, 2011 at 19:05
  • 1
    Are you really getting 2100+ pieces of user information that need to be escaped\paramed? That must be some form! Commented Aug 24, 2011 at 0:14

6 Answers 6

Reset to default
6

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 (.........));
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I like the idea of the temporary table (or if it is a constant set of values then a permanent table or view) that contains all the values from your list. Then do WHERE NOT IN (SELECT * FROM bad_params) or some such.
This doesn't solve the problem since the total number of cfqueryparam will be the same. List="True" for list of 2000 means 2000 param's.
But Henry, what about the separate table idea? This answer really had two ideas in one.
Take a closer look at the example. Instead of a single <cfqueryparam ... list="1"> break the list into several lists and you won't run into this "where foo in (...)" limit either. A temporary table would be faster during execution time, but first you have to get those values into the database, somewhat negating that speed gain again.
For those who want to break it into several lists, don't forget that most CFML engines are based on Java and allow easy access to the underlying Java functions. No need to re-invent the wheel when Java's subList method ( download.oracle.com/javase/6/docs/api/java/util/… ) is right there, just waiting to be used.
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?

Improve this answer

9 Comments

"Anything passed as a param cannot be executed."... So I guess I can't code that, but what's the strongest encode/escape I can do to a varchar inside <cfquery>?
There is an ESAPI encoder for SQL. Right now it only has support for MySQL and Oracle, but it looks like it might not be hard to write a codec to support MS SQL. But frankly, I think you are making this much harder than you need to. Use fewer queries. If you don't to do tham one at a time then do them 500 at a time.
it's part of a big SELECT WHERE IN () query... can't break them... easily.
I think it would be quite simple to break it up. I have not tested this, but here is an idea of what I think might work based on your other post. pastebin.com/C57z7pW8
Thx, but this time around the query looks like like SELECT * FROM Foo WHERE bar NOT IN ('a','b',...2100+) Can't break them easily.
|
1

It is a security issue. Stops SQL injections

Adobe recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. For more information, see Security Bulletin ASB99-04, "Multiple SQL Statements in Dynamic Queries," at www.adobe.com/devnet/security/security_zone/asb99-04.html, and "Accessing and Retrieving Data" in the ColdFusion Developer's Guide.

Improve this answer

1 Comment

I understand what it does. I want to implement a function (or call a function if already exists) that can encode / escape or whatever it does to the Value. I cannot use cfqueryparam 'cause of the upper limit of "2100" binded params
1

The first thing I'd be asking myself is "how the heck did I end up with more than 2100 params in a single query?". Because that in itself should be a very very big red flag to you.

However if you're stuck with that (either due to it being outwith your control, or outwith your motivation levels to address ;-), then I'd consider:

  • the temporary table idea mentioned earlier
  • for values over a certain length just chop 'em in half and join 'em back together with a string concatenator, eg:

*

SELECT *
FROM tbl
WHERE col IN ('a', ';DROP DATABAS'+'E all_my_data', 'good', 'etc' [...])

That's a bit grim, but then again your entire query sounds grim, so that might not be such a concern.

  • param values that are over a certain length or have stop words in them or something. This is also quite a grim suggestion.

  • SERIOUSLY go back over your requirement and see if there's a way to not need 2100+ params. What is it you're actually needing to do that requires all this???

Improve this answer

Comments

1

The problem does not reside with cfqueryparam, but with MsSQL itself :

Every SQL batch has to fit in the Batch Size Limit: 65,536 * Network Packet Size.

Maximum size for a SQL Server Query? IN clause? Is there a Better Approach

And

http://msdn.microsoft.com/en-us/library/ms143432.aspx

Improve this answer

1 Comment

I know you were not, just trying to shed some light on the why this is happening :-)
0

The few times that I have come across this problem I have been able to rewrite the query using subselects and/or table joins. I suggest trying to rewrite the query like this in order to avoid the parameter max.

If it is impossible to rewrite (e.g. all of the multiple parameters are coming from an external source) you will need to validate the data yourself. I have used the following regex in order to perform a safe validation:

<cfif ReFindNoCase("[^a-z0-9_\ \,\.]",arguments.InputText) IS NOT 0>
    <cfthrow type="Application" message="Invalid characters detected">
</cfif>

The code will force an error if any special character other than a comma, underscore, or period is found in a text string. (You may want to handle the situation cleaner than just throwing an error.) I suggest you modify this as necessary based on the expected or allowed values in the fields you are validating. If you are validating a string of comma separated integers you may switch to use a more limiting regex like "[^0-9\ \,]" which will only allow numbers, commas, and spaces.

This answer will not escape the characters, it will not allow them in the first place. It should be used on any data that you will not use with <cfqueryparam>. Personally, I have only found a need for this when I use a dynamic sort field; not all databases will allow you to use bind variables with the ORDER BY clause.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.