The problem is I wanted to select only products that were round and red. However, the problem is you can’t use a where statement like:

“select distinct product_id from product join product_to_category using(product_id)  where category_id = 1 and category_id = 3”

This would return 0 results.

The best way to do this is to use a Group By Product_ID along with a Having clause as such:

“select distinct product_id from product join product_to_category using(product_id)  group by product_id having sum(if(category_id in (1,3),1,0)) = 2”

This will return only products that are both red and round.

How does the having clause work?

Having is similar to where except for it takes place after all rows have been collected. Generally it filters the results. Now let’s look at the statement:

“having sum(if(category_id in (1,3),1,0)) = 2”

What this code does is pretty simple. It checks each category_id in product_to_category for a match. If there is a match a 1 is returned, if no matches it returns a 0. Because they are grouped we add up the number of matches. In this case there are 2 categories we are looking for so this value must “= 2”.

That’s it! Fast and clean!

If you run executescalar it returns the first column/result. The problem is if you don’t have a result it returns nothing.

Keyword: nothing

Yes you’re working with a database so you would assume it’s DBNull, but if there is an empty table SQL is not going to return DBNull, but will return nothing.

Solution:

Dim sqlResult as object = sqlCMD.executescalar()

if sqlResult is nothing then
‘ No result found
else
sqlResult.tostring   ‘result found
end if

Simple!

Long time no update! I’m shocked to see I’m still getting over 100 posts a day considering I haven’t updated in months.

Well I wrote a little script to help everyone out who is using the HTMLeditor that ships with asp.net’s AJAX Control Toolkit. Hope you enjoy!

Function HTMLStream(ByVal InputValue As String, Optional ByVal WhiteList As String = "p|span|ol|li|ul|hr|div|i|b|h1|h2|h3|h4|a|br|img|font") As String

Dim ReturnValue As String

ReturnValue = Regex.Replace(InputValue, "<(?!(" & WhiteList & ")\b)[^>]+>([^.]|[.])*(<(?!/?(" & WhiteList & ")\b)[^>]+>)", "", RegexOptions.IgnoreCase)

While (Regex.IsMatch(ReturnValue, "(<[\s\S]*?) on.*?\=(['""])[\s\S]*?\2([\s\S]*?>)", RegexOptions.Compiled Or RegexOptions.IgnoreCase))

ReturnValue = Regex.Replace(ReturnValue, "(<[\s\S]*?) on.*?\=(['""])[\s\S]*?\2([\s\S]*?>)", _

Function(match As Match) [String].Concat(match.Groups(1).Value, match.Groups(3).Value), RegexOptions.Compiled Or RegexOptions.IgnoreCase)

End While

ReturnValue = Regex.Replace(ReturnValue, "(?<=<.*)href=""(?!http://|www\.)[^""]*""", "", RegexOptions.IgnoreCase)

Return ReturnValue
End Function

Now if you want to know how this script works you can continue reading. As a warning I will be assuming that you know regex and intermediate VB.Net code (If you want C# there are a lot of conversion applications online.)

Part 1
The function starts off with two variables. InputValue, which is self described, and the optional WhiteList. WhiteList is a list of HTML characters which will be accepted. By default it’s pretty generous.

Part 2
ReturnValue = Regex.Replace(InputValue, “<(?!(” & WhiteList & “)\b)[^>]+>([^.]|[.])*(<(?!/?(” & WhiteList & “)\b)[^>]+>)”, “”, RegexOptions.IgnoreCase)

This line searches every HTML tag and checks to see if it matches any of the values in the WhiteList group. If it doesn’t it clears out the tag and ALL of it’s contents. This is setup to be greedy! Why greedy? Because it’s for security! I don’t want to remove just the tag, I want to remove EVERYTHING inside of the tag. So be WARNED, altering the WhiteList tags may result in lost of user input.

Part 3

While (Regex.IsMatch(ReturnValue, "(<[\s\S]*?) on.*?\=(['""])[\s\S]*?\2([\s\S]*?>)", RegexOptions.Compiled Or RegexOptions.IgnoreCase))

ReturnValue = Regex.Replace(ReturnValue, "(<[\s\S]*?) on.*?\=(['""])[\s\S]*?\2([\s\S]*?>)", _

Function(match As Match) [String].Concat(match.Groups(1).Value, match.Groups(3).Value), RegexOptions.Compiled Or RegexOptions.IgnoreCase)

End While

This next part is a bit confusing. Generally this goes the extra step most scripts don’t bother to do. Which is a shame since it fails to remove those pesky JavaScript event handlers.

Part 4
ReturnValue = Regex.Replace(ReturnValue, “(?<=<.*)href=””(?!http://|www\.)[^””]*”””, “”, RegexOptions.IgnoreCase)

The final part is to go through and remove all javascript injections using the href objection for anchor tags. This will only allow links starting with “www.” or “http://&#8221;. You can modify this if you want to allow others such as ftp etc. Obviously this is to prevent against those href=”javascript:…..” injections.

So now that you got the basics you can go through and figure out the nitty gritty! Remember as one developer wrote in a blurb, DO NOT ever let the attacker no if they failed or passed. Otherwise you’re basically inviting them to try to figure out your code. You don’t want to do that!

Please read:
While I put a great deal of effort into this script, I did not write everything from scratch. A lot of people around the web have helped write the code you see above. I simply tweaked what they had and combined it into a far more secure function. So thanks to everyone who posted the original code that helped me write this. Sadly there are too many to know off hand.

Now, I’m always anal when it comes to security, double checking everything sever side and never assuming the page generated on the clientside will be the way I intended it on the way back.

For those who have ever thought of doing this as a quick solution, I’m sorry, but you’re wrong. All disabling controls do is create a friendly user interface where a user knows ahead of time they can’t edit it.

In any browser, such as firefox, with firebug, you can just back the code and reenable it and then alter the text and submit the form. if you failed to double check for changed and access rights, the person will have just hacked your site. Something any kid can do! So don’t do it! Use it only to make your site more friendly AND NOT for security reasons.

I wanted to slap them after seeing how so many people couldn’t find such a simple answer! So here it is incase you need to know how to do it in only 1 line of code!

convert.toint32(Boolean)

I couldn’t believe it when I saw people telling the person to use if statements and use a variable to hold the value. Granted it was a small forum, but still it shocks me how little people know about the convert method in .net.

If you want to see what I’m talking about look here:
http://www.daniweb.com/forums/showthread.php?t=104189&highlight=asp.net+convernt+boolean+to+integer

It makes me sort of wonder if these people do this for a living… I hope not… or else I so need a better job.

So why does this pesky error appear at times? There are a few reasons in my case, because I had a clientside script being run from a server control. Which caused some messy conflicts. To sold this error it is actually really easy. Just include the following code in your script:

        Protected Overrides Sub Render(ByVal writer As System.Web.UI.HtmlTextWriter)
            Page.ClientScript.RegisterForEventValidation(tbxSearch.uniqueid)
            MyBase.Render(writer)
        End Sub

And that’s it! Ok, maybe I should explain it. Generally you have to edit the script on the render phase. If you don’t know the page lifecycle…. learn it! You’ll need it! Just google it and you’ll find a great description on msdn.

So generally we override the render phase. Thus the keyword overrides in the code. The MyBase.Render(writer) line of code is basically…. what normally happens. In this case right before the render phase goes through we inject a line of code to say, “Hey, ignore this control! It’s safe!” and then the clientscriptmanager will process that control and let it slide pass eventvalidation. Now to do this you have to do it through page.clientscript and use the registerforeventvalidation method to find the specific control you need to allow.

After that test out the page and there shouldn’t be any problems! oh btw I do typically disable ValidateRequest myself. This generally prevents users from submitting information like “<script…..” into forums and such. Which if you are using proper coding methods you shouldn’t need to run these training wheels. Eventvalidation is a whole other story since it’s a lot more work to enforce it strictly behind code.

Dim mytrigger as new AsyncPostBackTrigger
Mytrigger.ContorlID = “tab1”

Well this works if you have a set amount of triggers. You just do this each time for each trigger and it works out really well. But what if you don’t know how many controls there are. An example of when? Custom server controls, which I have been learning vigorously these last few months.

In this case we have a property which holds several button inputs, or tabs in my case. Well the problem is the developer can add multiple instances of these tab controls, so exactly how do we link them all to the updatepanel?

The answer is in the UpdatePanelTriggerCollection! This wonderful class is used to hold all the triggers within the updatepanel. So to do this we first create the updatepanel followed by the updatepaneltriggercollection.

Dim upnlContent as new updatepanel
upnlContent.UpdateMode = UpdatePanelUpdateMode.Conditional

Dim triggerCollectionContent as new updatepaneltriggercollection(upnlContent)

If you notice you must declare an owner to the triggercollection. In this case being upnlContent.

After you setup the triggerCollect you have to create your triggers with the following code:

triggerCollectionContent.Insert(0, New AsyncPostBackTrigger)
CType(triggerCollectionContent.Item(0), AsyncPostBackTrigger).ControlID = "tab1"

The first line creates a new entry into the collection as a new trigger. The second line sets the controlID for the trigger. If you notice we didn’t have to assign it a variable name! Meaning it’s an array of triggers. So once you have finished inserting all of your triggers using a for loop you then go ahead and go back through that collection and add them to the updatepanel one by one as follows:

upnlContent.triggers.add(triggerCollectionContent.Item(0))

You use the same loop to add them to the updatepanel. Hope that helps you all out!

NOTE: When using this method with a for loop using control.controls.item(i), make sure to use an if statement to check the control type using control.controls.item(i).gettype is gettype(controltype)!!! And make sure to use i instead of 0 if you’re looping.

In order for the script to work you need to determine if a value already exists. This is where the problem lies with FF. To get the value, the only way to do it that I know works is…

if (document.body.getAttribute("onmousemove")) { }

Once you have that going you can simply do the same but… but it requires a bit more work sadly.

As with the previous example in the last post, with onscroll, you always need to have it referenced as

function() { /* your code */ }

In IE and most other browseres, besides FF, the returned code will always be treated as an anyomous function. Meaning when you call back document.body.onmousemove it is treated as a function which requires you to call…. document.body.onmousemove(), in FF it is returned as document.body.onmousemove without the () so the next part of this puzzle is to determine the browser type. In this case I tested for FF by using the following…

if (navigator.userAgent.indexOf("Firefox")!=-1) {
 //FF code
} else {
 //Non-FF code
}

The final result of combing these methods is:

if (document.body.getAttribute("onmousemove")) {
   var MouseMoveCode = eval(document.body.onmousemove);
   if (navigator.userAgent.indexOf("Firefox")!=-1) {
      var MouseMoveEvent = function(event) { 
         dragDiv(event,CustomControls.OverlayMenu.MouseMoveCode)};
         document.body.onmousemove = MouseMoveEvent;
   } else {                
      MouseMoveEvent = function(event) { 
      dragDiv(event,MouseMoveCode())};
      document.body.onmousemove = MouseMoveEvent;
   }
} else {
   document.body.onmousemove = function(event) { dragDiv(event) };
}

To get around this, you can simply use the following code with a scriptmanager control:

If Not Page.IsPostBack Then 
   ScriptManager.RegisterStartupScript(Page, Page.GetType(), _
   "ScriptManager_myFunction", "document.getElementById('" & _
   myControl.ClientID & "').value = 'default value';", True)
End If

You can also clear the form control value, but I typically do not recommend this action since it defeats the advantages associated with firefox maintaining form data on refresh.

In this example we already have assigned window.onscroll as the following:

window.onscroll = function() {  
     setTimeout("document.getElementById('myDiv').style.color='red';",200);
     setTimeout("document.getElementById('myDiv').style.color='green';",400);  
};

This causes the text color to blink within our div as we scroll. Of course this is only for example and serves no practical use… normally at least. Anyway, now we want to combine this with some other code.

So we now want to impose the new onscroll code:

function() { moveDiv() }

moveDiv in this case moves the div so it is always on screen while you move. (fyi, this is how I personally get around IE5’s lack of support for position:fixed)

So how do we impose our old code onto the new code? Well it’s kind of strange, but we have to be a bit sneaky about it and take advantage of JavaScript’s JSON logic. To do this, we simply add the old code within the declaration of the new code, such as:

var ScrollCode = eval(window.onscroll);
window.onscroll = function() { moveDiv(ScrollCode()) };

Yes I am using eval, and yes most people hate using eval, but if used correct it is actually very useful! So there is no harm in this case since the eval code does not involve any sort of user input.

So what happens in this code is simple. We call the new function moveDiv which references the variable ScrollCode which calls the function for the current onscroll code. As a result both codes are ran at the same time. This makes adding new code easy, and you can continue to add code in this same manor. It just continues to impose the code onto each other.

Anyway hope this helps, and please let me know if you have any issues with it. Good luck.

PS: Note that this method is meant to be used with window events. click events do not always require function usage. (i.e. window.onscroll = function() {};) Most window events do require this on modern browsers.