Re: SOT: the magic of RANK()
Happy to not be the only one. 18 years in and I just learned about it today (IIRC). :-) On Aug 12, 2014 8:49 PM, "Rick Faircloth" wrote: > > Nice! > > I've never heard of "Rank()"... > > > On 8/12/2014 4:21 PM, John M Bliss wrote: > > I hope this saves someone some time and I hope I'm not way late to the > > "RANK()" party: > > > > Let's say you have this table: > > > > column1 - column2 > > A - 1 > > A - 2 > > A - 2 > > B - 3 > > B - 4 > > B - 4 > > > > ...and you need to write SQL to return the most frequently occurring > > column2 for each distinct column1: > > > > A - 2 > > B - 4 > > > > Enter RANK(): > > > > SELECT x.column1, x.column2 > > FROM ( > > SELECT z.column1, z.column2, COUNT(*) as thecount, RANK() OVER (PARTITION > > BY z.column1 ORDER BY COUNT(*) DESC) AS therank > > FROM z > > GROUP BY z.column1, z.column2 > > ) AS x > > WHERE x.therank = 1 > > > > Done and done. :-) > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359114 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SOT: the magic of RANK()
Nice! I've never heard of "Rank()"... On 8/12/2014 4:21 PM, John M Bliss wrote: > I hope this saves someone some time and I hope I'm not way late to the > "RANK()" party: > > Let's say you have this table: > > column1 - column2 > A - 1 > A - 2 > A - 2 > B - 3 > B - 4 > B - 4 > > ...and you need to write SQL to return the most frequently occurring > column2 for each distinct column1: > > A - 2 > B - 4 > > Enter RANK(): > > SELECT x.column1, x.column2 > FROM ( > SELECT z.column1, z.column2, COUNT(*) as thecount, RANK() OVER (PARTITION > BY z.column1 ORDER BY COUNT(*) DESC) AS therank > FROM z > GROUP BY z.column1, z.column2 > ) AS x > WHERE x.therank = 1 > > Done and done. :-) > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359113 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: ColdFusion BBCode routines
>> trying to download the last version of CF4em
>> check out the bbmlparser there.
I decided to spend an evening fully investigating the subject and looked at
all the various BBML code routines in CF, PHP and Perl to see what I could
glean. It looks like just about all the ones I could find have the same
approach and the same basic vulnerabilities as the one in the forum I am
fixing.For basic formatting, like bold, italicize, underline...etc the
process is straight forward and doesn't involve using any user input as part
of the tag. Just replace the entire [] tag with entire <> tag.
However the with URL, image and font type tags where the construction of the
tag uses the user input as part of the of the HTML tag just about all the
routines I saw fail to sanitize the user input content used in the
construction of the tag, allowing for the injection of JavaScript code.
Example: in an image tag like [img]image.gif[/img] almost all routines I
found look for the start tag and end tag then extract the middle re-format
the tags to HTML tags and re-insert the middle portion without checking it.
Same for URL and Font tags. None of the routines I saw even validate or
remove invalid characters for URI construction. So it is easy then for
someone to add scripting by doing this:
[img]img.gif"onMouseOver="alert('gotcha');[/img]
For image and URL tags I test for and remove all quote tags and non-URI
valid tags. The font tags were more difficult since they may contain other
tags between beginning and end so since hardly anyone was using the [font]
tags and apparently most forums do not support them, I just removed them
entirely.
Want to find holes in your code? Just dare 13-16 year olds to break it. :)
Dennis Powers
UXB Internet - A website Design and Hosting Company
P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844
W: http://www.uxbinternet.com
W: http://www.ctbusinesslist.com
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359112
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Emails MIA
>>Multi-recipient email will not generate a bounce or undeliverable message in CF if the CF spooler can deliver to at least one recipient. I think the failto address is not used by CF to bounce messages, only to provide a Return-Path: in the message header, and the bounce is generated by the destinee server, not CF. I use this in my mailing system to detect all bad addresses. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359111 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: ColdFusion BBCode routines
>> trying to download the last version of CF4em
>> check out the bbmlparser there.
I decided to spend an evening fully investigating the subject and looked at
all the various BBML code routines in CF, PHP and Perl to see what I could
glean. It looks like just about all the ones I could find have the same
approach and the same basic vulnerabilities as the one in the forum I am
fixing.For basic formatting, like bold, italicize, underline...etc the
process is straight forward and doesn't involve using any user input as part
of the tag. Just replace the entire [] tag with entire <> tag.
However the with URL, image and font type tags where the construction of the
tag uses the user input as part of the of the HTML tag just about all the
routines I saw fail to sanitize the user input content used in the
construction of the tag, allowing for the injection of JavaScript code.
Example: in an image tag like [img]image.gif[/img] almost all routines I
found look for the start tag and end tag then extract the middle re-format
the tags to HTML tags and re-insert the middle portion without checking it.
Same for URL and Font tags. None of the routines I saw even validate or
remove invalid characters for URI construction. So it is easy then for
someone to add scripting by doing this:
[img]img.gif"onMouseOver="alert('gotcha');[/img]
For image and URL tags I test for and remove all quote tags and non-URI
valid tags. The font tags were more difficult since they may contain other
tags between beginning and end so since hardly anyone was using the [font]
tags and apparently most forums do not support them, I just removed them
entirely.
Want to find holes in your code? Just dare 13-16 year olds to break it. :)
Dennis Powers
UXB Internet - A website Design and Hosting Company
P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844
W: http://www.uxbinternet.com
W: http://www.ctbusinesslist.com
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359110
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SOT: the magic of RANK()
I hope this saves someone some time and I hope I'm not way late to the "RANK()" party: Let's say you have this table: column1 - column2 A - 1 A - 2 A - 2 B - 3 B - 4 B - 4 ...and you need to write SQL to return the most frequently occurring column2 for each distinct column1: A - 2 B - 4 Enter RANK(): SELECT x.column1, x.column2 FROM ( SELECT z.column1, z.column2, COUNT(*) as thecount, RANK() OVER (PARTITION BY z.column1 ORDER BY COUNT(*) DESC) AS therank FROM z GROUP BY z.column1, z.column2 ) AS x WHERE x.therank = 1 Done and done. :-) -- John Bliss - http://www.linkedin.com/in/jbliss ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359108 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Emails MIA
On Aug 5, 2014 3:35 PM, "Byron Mann" wrote: > Use a failto in cfmail as it may provide you some detail. If the mail isn't > making it off the server, it should bounce to the failto. Multi-recipient email will not generate a bounce or undeliverable message in CF if the CF spooler can deliver to at least one recipient. Jochem ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359107 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Issue using CF to set Java server variables
The last few days, my production webserver has had an issue with Java unable to create cache files. This is triggered when firing a tag, pointing at a graphic file in the filesystem, the purpose of which is to retrieve the width/height values. As best I can tell (as this is generally outside my area of expertise), the issue is the default directory Java uses for this purpose has a) vanished or b) had its permissions reset to disallow access. The trick is, I don't know how to determine what this default directory is, to then check the filesystem to see what's what. I am able (using CF and createObject) tap into the Java method getCacheDirectory(), but the issue there is if this value isn't specifically set (i.e. the default is used), it returns a null value, and not the actual directory path being used. And I'm getting said null value, so that's of no help. I'm trying to use another method, setCacheDirectory(), to specifically set that value to a given location - but I cannot puzzle out the CF syntax to make this work. Below is the code I'm using to tap the getCacheDirectory() method, and what's not working on setCacheDirectory(). I'm creating an object to the "File" class first, to use for the "get" call, and then (as best I can tell according to some Java docs at Oracle) I need to reference that class in the "set" call. free space: #objFile.getFreeSpace()# (returns free space on server drive) cache directory: #objImageIO.getCacheDirectory()#(returns null value) usecache: #objImageIO.getUseCache()#(returns true - true means file caching, false means memory caching) This call fails with "The setCacheDirectory method was not found." Basically I'm stumped on the syntax of the last call. I'm not even sure, should I get this worked out and the call is successful, that it will solve the cache-failing issue, but it's my best lead so far. Any assistance on this syntax problem would be greatly appreciated! --Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359109 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: ColdFusion BBCode routines
Without specifics, I can just suggest trying to download the last version of CF4em (which is still pretty old) and check out the bbmlparser there. http://cf4em.com/downloads/ /cf4em/inc/parsebbml.cfm Unless CF4em is what you inherited... In which case, I¹d suggest NOT doing that that :-) On 8/10/14, 8:31 PM, "UXB" wrote: > >I was wondering if anyone has some battle tested BBCode routines to >convert >BBcode to html and one to covert the HTML created back to BBcode? I have >inherited a forum that has rolled its own BBcode <> Html routines and the >kiddies are finding ways to imbed scripts into the various BBCode >routines. >I would prefer not to have to re-create the wheel. I have tried Google but >can't seem to find much of anything coldfusion related on the subject. > >Any assistance is appreciated. > >Dennis Powers >UXB Internet - A website Design and Hosting Company >P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844 >W: http://www.uxbinternet.com >W: http://www.ctbusinesslist.com > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359106 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
