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?
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Henry
  • 32,689
  • 19
  • 120
  • 221
  • "Is not including the cfsqltype for cfqueryparam still useful for sql injection protection?" http://stackoverflow.com/questions/6913696/is-not-including-the-cfsqltype-for-cfqueryparam-still-useful-for-sql-injection-pr – Mike Henke Aug 23 '11 at 18:58
  • I read the post @bennadel.com... removing the cfqueryparam seems to be required. A custom cfc/function might be the best approach. If the query is going to be exposed (hence the need for strong escape/encode), I might dump the list into an array, escape & check, then convert it back to a list for the query. hth – nykash Aug 23 '11 at 20:50
  • @nykash yes, I want to know how much I need to do for escaping... thx – Henry Aug 23 '11 at 21:21
  • 1
    Are you really getting 2100+ pieces of user information that need to be escaped\paramed? That must be some form! – Antony Aug 24 '11 at 00:14
  • is it a multi-step wizard? can you break the query up a bit? – Antony Aug 24 '11 at 06:53
  • this is kinda a duplicate question, see "Is not including the cfsqltype for cfqueryparam still useful for sql injection protection?" http://stackoverflow.com/questions/6913696/is-not-including-the-cfsqltype-for-cfqueryparam-still-useful-for-sql-injection-pr – Mike Henke Aug 23 '11 at 18:57
  • no, I'm asking precisely how I can encode/escape a varchar to be more secure without using cfqueryparam. Thanks. – Henry Aug 23 '11 at 19:04
  • Not really.Henry is asking more – Jason Dean Aug 23 '11 at 19:05
  • Change the question title and body from "What does do to the value exactly?" (which is what the other link answers) to what you are asking "How I can encode/escape a varchar to be more secure without using cfqueryparam?" – Mike Henke Aug 23 '11 at 20:05

6 Answers6

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 (.........));
mz_01
  • 495
  • 3
  • 13
  • 1
    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. – Jason Dean Aug 23 '11 at 20:50
  • 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. – Henry Aug 23 '11 at 21:22
  • 1
    But Henry, what about the separate table idea? This answer really had two ideas in one. – Jason Dean Aug 23 '11 at 22:01
  • Take a closer look at the example. Instead of a single 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. – mz_01 Aug 24 '11 at 03:57
  • 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 ( http://download.oracle.com/javase/6/docs/api/java/util/List.html#subList%28int,%20int%29 ) is right there, just waiting to be used. – mz_01 Aug 24 '11 at 16:06
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?

Jason Dean
  • 9,585
  • 27
  • 36
  • "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 ``? – Henry Aug 23 '11 at 18:48
  • 3
    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. – Jason Dean Aug 23 '11 at 18:56
  • it's part of a big SELECT WHERE IN () query... can't break them... easily. – Henry Aug 23 '11 at 18:58
  • 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. http://pastebin.com/C57z7pW8 – Jason Dean Aug 23 '11 at 19:15
  • 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. – Henry Aug 23 '11 at 19:28
  • why not use select * from foo where bar not in () Does that still throw the too many parameters error? – Sean Coyne Aug 23 '11 at 20:23
  • never mind, I see from Ben's blog he was using the list="true" attribute. – Sean Coyne Aug 23 '11 at 20:28
  • And these 2100+ values are from an untrusted source that may contain malicious code or invalid characters? – Jason Dean Aug 23 '11 at 20:34
  • Ya know, I have to wonder, with 10,000+ params in your WHERE NOT IN statement, might it be easier to say WHERE IN? – Jason Dean Aug 23 '11 at 20:37
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.

Lance
  • 3,193
  • 2
  • 32
  • 49
  • 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 – Henry Aug 23 '11 at 18:45
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???

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
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

Community
  • 1
  • 1
FreddyF
  • 41
  • 3
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.

Frank Moorman
  • 1
  • 1