Thomas’ Developer Blog

January 31, 2012

Filtering many to many queries in mySQL using “having.”

Filed under: sql — sanzon @ 6:21 am

I rarely update this blog anymore except when I run into something that really bugs me. I started with 3 tables.

Product_ID Name
1 Orange
2 Apple
3 Banana
Category_ID Name
1 Red
2 Orange
3 Round
4 Long
Product_ID Category_ID
1 1
1 3
2 2
3 4

 

 

 

 

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!

Advertisements

May 29, 2011

Checking if executescalar returns a result in VB.Net

Filed under: .Net Framework 3.5, Asp.Net Framework 2.0, sql, vb.net — sanzon @ 6:01 pm

Yea, it’s been over a year since I updated this blog, but spending 15-20 minutes finding a solution to something so simple got a bit annoying so here’s the solution for future searchers!

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!

January 17, 2010

Whitelist HTML Tags (Advance Methods for Prevention against Javascript Injections)

NOTE: This method is no longer preferred. Please see Microsoft Anti-Cross Site Scripting Library V4.2
http://www.microsoft.com/download/en/details.aspx?id=28589 

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.

February 5, 2009

Enable/Disable .Net controls (Security Tip)

Filed under: .Net Framework 3.5, Asp.Net Framework 2.0, security — sanzon @ 5:53 am

I was just browing around online and found a thread on asp.net’s forum talking about disabling a textbox for security reasons.

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.

January 29, 2009

Boolean to Int32 for SQL database. Yes it’s that easy!

Filed under: Uncategorized — sanzon @ 11:11 pm

I came across a thread on the web browsing around for a solution to another problem when I came across a forum thread that seemed to go on forever about converting a boolean, true/false, to an int, 1/0.

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.

November 15, 2008

RegisterForEventValidation and enableEventValidation

Ok first off… NEVER disable eventvalidation! This is for your own good. I know it’s very tempting to just type in enableeventvalidation=”false” and have the script work just fine. It’s a major security issue though and so it’s just best to learn it right the first time and fix it right!

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.

November 12, 2008

Adding multiple AsyncPostBackTriggers dynamically

This one took be a bit of time to figure out, and really can be extremely useful in some cases. This is the situation, you want to set an unknown amount of controls as a triggers to your updatepanel. The problem is you typically have to do this the following way…

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.

October 16, 2008

Imposing functions onto document.body.onmousemove

My last post went over how to handle onscroll with window.onscroll and adding an additional function to an existing function. Well with document.body.onmousemove there is a bit of a problem with Firefox.

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) };
}

Handling form data issues with FireFox on page refresh

Filed under: Asp.Net Framework 2.0, Browsers, javascript, vb.net — Tags: , , — sanzon @ 9:08 am

If you have ever noticed how FireFox maintains form data on a page refresh, it can causes some issues at times when handling .net code that uses these inputs to render javascript code after the page loads.

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.

Combining/Imposing Event Code in JavaScript

Filed under: javascript — Tags: , , , , , — sanzon @ 7:35 am

In JavaScript there may be times you want to combine code onto an existing window event. In this case we will use window.onscroll.

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.

Older Posts »

Create a free website or blog at WordPress.com.