Creating IN Queries With Linq To Sql

Creating IN Queries With Linq To Sql

 

I am working on a ecommerce website these days using MVC; although the point I am going to discuss here is not related to MVC it is about LINQ; I am liking LINQ these days as it saves a lot of time and effort in creating large and complicated queries and that too specially when you are not a SQL expert or SQL Freak. I am one of those programmers who can write few simple queries in SQL and avoid to code in SQL. So I am Liking LINQ more.

 

Coming to the point Today while writing a function I need to extract few records from the product table where the product can be from the given range of product id's and I was using LINQ so here comes the concern how can I use IN operator feature of SQL which was so easy to use in SQL. And then I got the answer after going through few links only. Believe me it's not a great trick or something magical we need to do LINQ has handled it.

 

We can have two types of case and both the cases are handled in different ways but on same concept. Let me tell you what these two cases can be.

 

1.       Values for comparison can be already with you in form or any array or list

2.       Value for comparison can be evaluated by the query itself.  

 

Here I am taking an example that I have a collection of productid and I need to fetch the names of those products from the product table. If I have to write a sql query for this then it would look like :

 

Select productname from products where productid in(1,5,7,9,11)

 

It's a simple SQL Qery which will return the result as the names of the products whose id will be either 1, 5, 7, 9 or 11.

 

Now if I need to do this using our LINQ  we can consider the above two cases taking case 1 first where I have already the list of items stored in an array;

 

            int[] productIDs = new int[] { 1, 2, 3, 4 };

 

            var ProductsNames = from p in _db.Products

                             where productIDs.Contains(p.ProductID)

                             select p.Name;

 

 

Generated SQL for this will be

 

SELECT [t0].[Name

FROM [DatabaseName].[Product] AS [t0]

WHERE [t0].[ProductID] IN (@p0, @p1, @p2, @p3)

 

will discuss this code a bit later in this article. As before discussing this code I would like to share the code for other case also. And that goes like this

 

Considering that the productids will be selected from the other table i.e. tblcart which contains the products which are currently in the cart.

 

So first we need to select the productids form that cart table code goes like this:

var ProdIdsQuery = from Items in _db.tblCart

                            where Items.UserID == "meetuchoudhary"

                            select Items.ProductID;

 

now in ProdIdsQuery we have all the productids which user meetuchoudhary has added now selecting the names of these products we need to write another LINQ

 

 

var ProductsNames = from p in _db.Products

                             where ProdIdsQuery.Contains(p.ProductID)

                             select p.Name;

 

here I have just changed the variable name which was being tested. J not a trick. And the sql generated behind this will be

 

SELECT [t0].[Name]

FROM [DatabaseName].[Product] AS [t0]

WHERE EXISTS(

    SELECT NULL AS [EMPTY]

    FROM [Sales].[tblcart] AS [t1]

    WHERE ([t1].[ProductID] = [t0].[ProductID]) AND ([t1].[ UserID] = @p0)

)

 

Here you can notice that we get where exists this is just a synonym for IN in SQL.

 

Well now this may seems bit weird or a wacky syntax to you but this is how we can achieve the IN of SQL in LINQ. This is the top down approach which LINQ follows this weirdness of these may be because  Linq To Sql only constructs the query when the Enumerator is tripped. So that's why we need those ProdIdsQuery or array of productIDs

 

Hope it helps you. Thanks for spending your time on this page and reading it.

 

Regards, May Lord Shiva Bless all

Miss Meetu Choudhary

Click jacking

Hello Friends.

Today when I was thinking about what to write today; I received an email from one of my friend "Ravi" he wants to know about few terms related to Web development and I thought to make those terms as my blog post. Thanks to Ravi he has given me topic for three days. I need not to think what to write today. Well one of those terms is Click jacking. Here I will start  with explaining what is Click jacking; how can the hacker may take advantage of Click jacking and what measures we can take to prevent Click jacking.

Definition and Background:

Sometimes also  known as UI Redressing or Cross Site Scripting. As in this technique the UI is redesigned to carry some script code along the original code without any information to the user and can change the behavior how the button or link or the html element to which the undesired (by the programmer but desirable by the hacker who is trying to steal information)script should work.

A clickjacked page tricks a user into performing undesired actions by clicking on a concealed link. Most often on a clickjacked page, the attackers show a set of dummy buttons, followed by the loading of another page over it as a transparent layer. The users believe that they are clicking the visible buttons on the clickjacked page, however  they are not rather they are actually performing actions on the hidden page which is loaded on the transparent layer.  The attackers with the help of this  hidden page (it can be authentic page also and if so then it)  can trick users into performing actions which the users never intended. There is no way of tracing such actions later, as the user was genuinely authenticated on the other page.

For Instance:

v  A Person receives an email with a link to a video about a news item, but another valid page, May be a product page on any ecommerce  site, can be "hidden" on top or underneath the "PLAY" button of the news video. The person when tries to "play" the video he is not actually playing the video rather s/he is "buys" the product from that ecommerce website.

v  Other known exploits have been:

o   Tricking users to enable their webcam and microphone through Flash (which has since been corrected by Adobe);

o   Tricking users to make their social networking profile information public;

o   Making users follow someone on Twitter or other known social networks

o   Share links on Facebook (this is quiet common these days)

Prevention

Measures can be taking to save your site form these attackers and prevent clickjacking of the pages. We can divide the measures in following categories.

1.       Client Side Measures,

2.       Server Side Measures

3.       Server Side Measures needing client support

Client-side Measures

These measures can be taken by the user at their end

NoScript

Protection against clickjacking can be added to Mozilla Firefox desktop and mobile versions by installing the NoScript add-on: its ClearClick feature, released on 8 October 2008, prevents users from clicking on invisible or "redressed" page elements of embedded documents or applets. According to Google's "Browser Security Handbook", NoScript's ClearClick is "the only freely available product that offers a reasonable degree of protection" against Clickjacking.

Gazelle

Gazelle is a Microsoft Research project secure web browser based on IE, that uses an Operating System-like security model,  has its own limited defenses against clickjacking. In Gazelle, a window of different origin may only draw dynamic content over another window's screen space if the content it draws is opaque.

Server-side

Framekiller

Web site owners (Developers) can protect their users against UI redressing (frame based clickjacking) on the server side by including a framekiller JavaScript snippet in those pages they do not want to be included inside frames from different sources.

But these JavaScript-based protection, unfortunately, are not always reliable. This is especially true in case of Internet Explorer, where this kind of countermeasure can be circumvented "by design" by including the targeted page inside an <IFRAME SECURITY=restricted> element. So we need the update

Some websites are under the impression this very old frame busting code can prevent click jacking attacks:

1              try {

2                if (top.location.hostname != self.location.hostname) throw 1;

3              } catch (e) {

4                top.location.href = self.location.href;

5              }

But NO this is not enough as we have to take care of other browsers also Here's a very simple way around this which works in both FF and IE7: (update, a way to work around this prevention here)

1              var prevent_bust = 0

2              window.onbeforeunload = function() { prevent_bust++ }

3              setInterval(function() {

4                if (prevent_bust > 0) {

5                  prevent_bust -= 2

6                  window.top.location = 'http://server-which-responds-with-204.com'

7                }

8              }, 1)

The server only needs to respond with:

                HTTP/1.1 204 No Content

On most browsers a 204 (No Content) HTTP response will do nothing, i.e. you will be left on the current page. But the request attempt will override the previous frame busting attempt, rendering it useless. If the server responds quickly this will be almost invisible to the user.

Update: If the frame busting code is at the beginning of the page, before any content loads, then even though the frame busting will be prevented, so will the loading of the remainder of the page. This means that your content would be hidden and un-clickjackable (only in FF, see below for IE).

So what can we do to protect our  website from clickjacking?  Well, I'm not a security expert but this seems to cover almost all the cases:

First, we can have  page load with all hidden content using a CSS. Like:

1              <body style="display:none" ...>

Followed by some variant of the frame busting code, but instead of busting, use it to determine whether or not to display your content:

1              try {

2                if (top.location.hostname != self.location.hostname)

3                  throw 1;

4             

5                document.body.style.display = 'block';

6              } catch (e) {

7                // possible clickjack attack, leave content hidden

8              }

This covers most of the cases. It covers IE's SECURITY=RESTRICTED which allows you to turn off scripting for an iframe. If your site is loaded like this, your script will not run and your content will remain hidden (as mentioned above). And it covers a standard clickjack attack by not displaying your content if it detects that it has been framed. What it doesn't cover is a user who comes to your site with javascript disabled (who will see nothing). We  of course can  present them with a message saying javascript is required (using <noscript>). Irritates, but it seems at this point that is the price we have to pay for clickjacking protection.

Note: NoScript can protect you from clicking on invisible elements.

Server-side needing client support

X-Frame-Options

On 26 January 2009 Microsoft released RC1 of Internet Explorer 8, which includes a new partial clickjacking prevention option. Web site developers will be able to add a tag in a page header to help detect and prevent frame-based UI redressing. IE 8, according to Microsoft, "will detect sites that insert the tag and give users a new error screen indicating that the content host has chosen not to allow their content to be framed, while giving users the option to open the content in a new window."

Microsoft's suggested solution, which has since also been implemented in Apple's Safari, Firefox, and Google's Chrome Web browsers, is to check for a new HTTP header, X-Frame-Options. This header can have two values, deny and same origin, which will block any framing or framing by external sites, respectively.

If you have or know of a better solution please let me know.

Regards,

Miss Meetu Choudhary

Help Needed in Designing a Stored Procedure

I am working on a MLM Project which is following the Binary System that is each user have to create 2 child users. Now I want to create a stored procedure which will insert  the user in the database. i have two tables:

userdetails: Master Table Which contains the records of all the employes structure:

ID (Auto-increment Primary key) username password and other details

UserRelation Table (Child Table) Structure
ID (Auto-increment Primary key) UserID(FK from Userdetals table stores the id of the current user being inserted) ParentUserID(FK from userdetails stores the ID of the user under which the current user is being inserted) position (L or R indicates where the new or current user is being inserted) referaluserid (null if the parentid is provided, and if the refralid is provided then the record needs to inserted like we have to find the parentid. )

for e.g.

A has Childs B(left) C(Right)
B has Childs D(left) E(Right)
C has Childs F(left)

Now if the new user G gives parent id as C then it will get inserted at C's right position but if G gives refral id of A then i have to find the parentid as i have to go through all the n-level (till 12th level) of records for A and find where i  need to insert the new user G First the left side will be precessed if the legs are balanced else the dis-balanced leg will get this entry.
 

Sorry to bother you but i need it urgently my boss want me to create this sp by today evening :( no clues where i am going :(

Handling Checkboxes at Controllers in MVC Part II

Hi

In continuation to my previous post here I am extending how on the basis of the selected checkboxes  we can remove the records form the session variable.

A small piece of code from the view to show how the names of the checkboxes are being given

foreach (Controllers.ProductsToBuy p in Model)

            {

                string chkname = "chk" + i.ToString();

                i++;

            <tr>

                <td align="center">

                    <input type="checkbox" value="" class="checkbox" runat="server" id="@chkname" />

                </td>

Code Discussion:

Using the foreach loop inside a table the checkboxes are also being rendered along with the product. And in the above example I have am setting the id property of the check box to chk and concatenating the rowno with this chk I would have concatenate the product id as well but it might possible that the user can buy the same product multiple times as I am not considering it for my validation for this scope so to avoid the confusion I am using the row number the logic can differ according to your need.

Following is the code snippet from the controller which is handling the post method of the view (form)

        [HttpPost]

        [ValidateInput(false)]

        public ActionResult ProductAddtoCart(FormCollection collection)

        {

            int i = 0;

            List<ProductsToBuy> p = new List<ProductsToBuy>();

            List<ProductsToBuy> newp = new List<ProductsToBuy>();

            p = ((List<ProductsToBuy>)Session["ProductCart"]);

            foreach (ProductsToBuy pb in p)

            {

                string chkname = "chk" + i.ToString();

                if (collection[chkname] == null)

                {

                    newp.Add(pb);

                }

                i++;

            }

            Session["ProductCart"] = newp;

            return View(newp);

        }

    }

Code Discussion:

The idea which I am following behind this is I will cast the list from the session variable which is of type list to the desired list<class>; I have also created a newp object of same type in which I will store the records which are need to be purchased by the customer you can also do the other way round u can remove the items which user don't want (or want to delete) and then using foreach loop I am checking whether the check box is checked or not as I know that I have given the id's to the checkboxes like chk0, chk1… so on as per row number.  So here I am initializing my string variable chkname so that I can create a string for the checkbox names by concatenating chk and the row number then I am checking the  chkname in the collection (which is passed by the view as a formcollection at time of postback) if the collection is null that means the checkbox is still unchecked and I am adding that item in my newp object and finally setting the session variable again to the newp object which now only contains the probuct which are not cecked at the front end and returning the same object to the view to display.

Thanks and Regards

Meetu Choudhary

Handling CheckBox at Controller in MVC

I am a NewBie for MVC3 and trying to create a Project in MVC 3 Razor…

So Facing Lot Many Problems and also solving them with the help of Google and few of my Expert Friends and MVP’s. So Thinking to make some Blog Posts for the people like me.

Today I was trying to access a html checkbox from the view at the controller as I have to make the decision on the bases of this checkbox if it is checked I need to delete the record else I have to process the record in different way.  So my First concern was how can I get the checkbox at the controller.

The Answer Goes like:

I find Two Methods to solve this issue although I am not very much sure right now which one is better and why.

Points to keep in mind. We need to post back the form from the view (obviously because without post back we would not get the changes made by the user at the backend  [Contoller]).

So in view I added the following line:

<form method="post" action="ControllerName">

This will post the form to the controller and now we need to create a  controller which can handle this

Here the controller goes like

        [HttpPost]

        [ValidateInput(false)]

        public ActionResult ControllerName(FormCollection collection)

        {

            if ( collection["chk"]!= null)

            {

           //delete from session here

            }

            return View(p);

        }

I have used HttpPost to mark this controller that it will handle the post back as far as I have googled I found that  [AcceptVerbs(HttpVerbs.Post)] and [HttpPost] are same make (me correct if I am wrong) and now here we can access the checkbox here “chk” is the name of the checkbox I have created in the view. If the condition is false  that means if the collection[“chk”] is null that means the checkbox is not selected and if the condition is false that is collection[“chk”] is not null that means the checkbox is selected. I have used  [ValidateInput(false)] because I don’t want to validate the form inputs at the time of postback you can skip this line it won’t make much effect.

In the second way we need to change the controller a bit in the above example we are passing the formcollection here we will use the request. Just peep into the code

        [HttpPost]

        [ValidateInput(false)]

        public ActionResult ControllerName()

        {

            if ( Request["chk"]!= null)

            {

           //delete from session here

            }

            return View(p);

        }

The only change I made is that instead of using the formcolletion I am using the request which already contain the formcollection.

Thanks For Reading this Article. Will try to post more articles on MVC 3.