Tuesday, April 17, 2012

User Information within InfoPath Forms

 User Information within InfoPath Forms
There is a quick and easy way that information about the logged in user can be obtained from within an InfoPath form. You may have read my blog and other blogs before, with the user profile service web service instructions. In this post, I'm not going to be using the user profile service. This means that what I'm about to show you can be utilized with any version of SharePoint, whether you have a user profile service (enterprise) or not. This can be done with SharePoint 2010 Foundation, SharePoint 2010 enterprise and standard, and also WSS 3.0 and MOSS.
The User Information List is a list that exists at the site collection level, and contains a list of every user who has actually visited the SharePoint site. If they are a new user, they will not show in this list until they've actually visited a site in that site collection.
With that said, here are some instructions for getting some information about users, more specifically, the currently logged in user.
  1. Open up your InfoPath form, click on the Data tab, and click the Data Connections button.
  2. Click the Add… button.
  3. Choose Receive Data, and click Next.
  4. Choose SharePoint List or Library, and click Next.
  5. Paste the URL of your SharePoint site, click Next. (No need for any "default.aspx" at the end, or any other .aspx)
  6. Scroll down towards the bottom of the list of lists and libraries, and select User Information List. Click Next.
    (note that even though the user information list physically exists at the site collection level, you will still see this list on every sub-site also)
  7. Put check boxes next to the following fields, especially User_name. Click Next.
  8. Do not check the box to store a copy of the data. Click Next.
  9. This is very important. UNCHECK the box to automatically retrieve the data. Click Finish.
  10. On the Data Connections screen, click Close.
  11. Still on the Data tab in InfoPath, click the Form Load button, which will bring up the rules pane on the right side of the screen.
  12. Click the New button to create a new Action rule.
  13. Click the Add button and choose Set a Field's Value. On the Rule Details screen, click the button next to Field.
  14. In the Fields drop-down box, choose User Information List, expand the queryFields, and select User name.
    (be sure that you're selecting from the query fields, not the data fields.) Click OK.
  15. Back on the Rule Details screen, click the function (fx) button next to the Value box.
  16. Click the Insert Function button, and choose the userName function. Click OK two times.
  17. This is what your rule looks like. Click OK.
  18. Back over in the rules pane on the right, click the Add button, and choose Query for Data.
  19. Choose the User Information List and click OK.
  20. Now, each time the form is opened, the current user's information will automatically be queried, and that one record will be retrieved. There are many ways that this information can be used within your form. The data exists in the data fields of the user information list. In the Fields pane on the right side of the screen, choose the User Information List from the drop-down box.
  21. Select the folder directly under dataFields, the one with the blue icon called d:SharePointListItem_RW. Drag it onto an empty area of your form, and when you let go, choose Repeating Section with Controls.
    I'm really just having you put all this on the form so that you can see exactly what's being returned.
  22. Go ahead and publish the form, and open a new form. You'll see all of the data about you will be displayed, something like this:
  23. One more little trick. Go back to InfoPath Designer. Delete the "Picture" field. Add a Picture control to the form (not a picture button, just a picture), and choose As a link. Click OK.
  24. You will be prompted to bind to a field. Select the Picture field under the User Information List data fields. Click OK.
  25. Filling out the form again, now there's me:

    You can see how this data could potentially be used in many ways in your form. You can set some field defaults the first time the form is filled out, you could display the currently logged in user's picture… You can even use formatting rules to show, hide, or disable certain fields depending on who the current user is or what department they're in. I don't usually drop the fields on the form like I showed you, that was for demonstration purposes. I usually just refer to that data via rules, etc. Keep in mind that the user information list does not have the 60 or so fields that the user profile service contains, but it is very useful nonetheless. There are many other blog posts out there that show how to tap into the user profile service using the web service in InfoPath, it's just that if all you need is one of these basic fields, you might not need to deal with all of the complexity that it entails.
     

Thursday, March 8, 2012

Displaying Data through Repeater control Updating and Insertion through AJAX ModalPopUp control



Repeater.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Repeater.aspx.cs" Inherits="Repeater" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <center>
     
        <asp:Button ID="btnAddRecord" runat="server" Text="Add New Record" Width="600px"
            Height="40px" Style="font-weight: 700; font-size: large;" />
        <asp:UpdatePanel ID="UpdatePanel1" runat="server" >
            <ContentTemplate>
                <asp:Repeater runat="server" ID="Repeater1" OnItemCommand="OnItemCommand" OnItemDataBound="OnItemDataBound">
                    <HeaderTemplate>
                        <asp:Table runat="server" ID="tblHead" BorderWidth="1" Width="600px" CellPadding="0"
                            CellSpacing="0" BackColor ="Blue" ForeColor="White">
                            <asp:TableRow runat="server" Font-Bold="true">
                                <asp:TableCell Width="150px" runat="server" BorderWidth="1"></asp:TableCell>
                                <asp:TableCell Width="150px" runat="server" BorderWidth="1">Dept ID</asp:TableCell>
                                <asp:TableCell Width="150px" runat="server" BorderWidth="1">Department</asp:TableCell>
                                <asp:TableCell Width="150px" runat="server" BorderWidth="1">Location</asp:TableCell>
                            </asp:TableRow>
                        </asp:Table>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Table runat="server" ID="tblContent" BorderWidth="1" Width="600px" CellPadding="0" CellSpacing="0">
                            <asp:TableRow runat="server" ID="row" BorderWidth="1">
                                <asp:TableCell Width="150px" runat="server" BorderWidth="1">
                                    <asp:LinkButton ID="Edit" runat="server" CommandName="edit">Edit</asp:LinkButton>&nbsp;&nbsp;
                                    <asp:LinkButton ID="Delete" runat="server" CommandName="delete">Delete</asp:LinkButton>
                                </asp:TableCell>
                                <asp:TableCell Width="150px" runat="server" ID="tcID" BorderWidth="1">
                                    <asp:Label ID="lblID" Text='<%# Bind("DeptId")%>' runat="server"></asp:Label>
                                    <asp:PlaceHolder runat="server" ID="DIDEditPlaceholder" />
                                    <input type="hidden" runat="server" id="DIDHidden" />
                                </asp:TableCell>
                                <asp:TableCell Width="150px" runat="server" ID="tcName" BorderWidth="1">
                                    <asp:Label ID="lblDept" Text='<%# Bind("DName")%>' runat="server"></asp:Label>
                                    <asp:PlaceHolder runat="server" ID="DNameEditPlaceholder" />
                                    <input type="hidden" runat="server" id="DNameHidden" />
                                </asp:TableCell>
                                <asp:TableCell Width="150px" runat="server" ID="tcLoc" BorderWidth="1">
                                    <asp:Label ID="lblLoc" Text='<%# Bind("DLocation")%>' runat="server"></asp:Label>
                                    <asp:PlaceHolder runat="server" ID="DLocEditPlaceHolder" />
                                    <input type="hidden" runat="server" id="DLocHidden" />
                                </asp:TableCell>
                            </asp:TableRow>
                        </asp:Table>
                    </ItemTemplate>
                </asp:Repeater>
            </ContentTemplate>
        </asp:UpdatePanel>
        <asp:ModalPopupExtender ID="ModalPopupExtender1" runat="server" TargetControlID="btnAddRecord"
            PopupControlID="pnlPopUp">
        </asp:ModalPopupExtender>
        <asp:Panel ID="pnlPopUp" runat="server" Width="250px" BorderStyle="Groove" BackColor="AntiqueWhite">
            <center>
                <asp:Label ID="Label1" runat="server" Text="ID: "></asp:Label>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:TextBox ID="txtID" runat="server"></asp:TextBox><br />
                <asp:Label ID="Label2" runat="server" Text="Department: "></asp:Label>
                <asp:TextBox ID="txtDepartment" runat="server"></asp:TextBox><br />
                <asp:Label ID="Label3" runat="server" Text="Location: "></asp:Label>
                &nbsp;&nbsp;&nbsp;&nbsp;
                <asp:TextBox ID="txtLocation" runat="server">
                </asp:TextBox><br />
                <asp:Button ID="btnAdd" runat="server" Text="Insert" OnClick="btnAdd_Click" />
                <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
            </center>
        </asp:Panel>
    </center>
    </form>
</body>
</html>
Repeater.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Threading;

public partial class D2 : System.Web.UI.Page
{
    SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=OrgDB;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
    {
        Repeater1.DataSource = Bind();
        Repeater1 .DataBind ();
        //rotate.Visible = true;

    }
    public DataTable Bind()
    {
        SqlCommand cmd = new SqlCommand("Select DeptId, DName, DLocation from [tblDepartment]", conn);
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        return ds.Tables[0];
        //Repeater1.DataSource = ds.Tables[0];
        //Repeater1.DataBind();
    }
    protected void OnItemCommand(object source, RepeaterCommandEventArgs e)
    {
        if (e.CommandName == "delete")
        {
            conn.Open ();
            SqlCommand cmd = new SqlCommand("Delete from tblDepartment where DeptId=@DID", conn);
            cmd.Parameters.Add(new SqlParameter("@DID",((Label)e.Item.FindControl("lblID")).Text));
            cmd.ExecuteNonQuery ();
            conn.Close();
            Repeater1.DataSource = Bind();
            Repeater1.DataBind();
            
            
        }
        else if (e.CommandName == "edit")
        {
            //    EditIndex = e.Item.ItemIndex;
            //LinkButton btn = e.CommandSource as LinkButton;  // Identify the link button
            //RepeaterItem item = btn.NamingContainer as RepeaterItem;
            //TableRow tr = e.Item.FindControl("row") as TableRow; // Identify the row and change its color
            //tr.BackColor = System.Drawing.Color.ForestGreen;
            ModalPopupExtender1.Show();
            Label lblID = (Label)e.Item.FindControl("lblID");
            Label lblDName = (Label)e.Item.FindControl("lblDept");
            Label lblDLoc = (Label)e.Item.FindControl("lblLoc");
            txtID.Text = lblID.Text;
            txtDepartment.Text = lblDName.Text;
            txtLocation.Text = lblDLoc.Text;
        }
        Repeater1.DataSource = Bind();
        Repeater1.DataBind();
    }
    protected void OnItemDataBound(object sender, RepeaterItemEventArgs e)
    {

    }
   
    protected void btnAdd_Click(object sender, EventArgs e)
    {
      // Thread.Sleep(2000); //rotate.Visible = true;
      //  Thread.SpinWait(6);
        string DeptName = txtDepartment.Text;
        //string DName = ((TextBox)Repeater1.FindControl("NewDName")).Text;
        string DLoc = txtLocation.Text;
       // string DeptLoc = ((TextBox)Repeater1.FindControl("NewDLoc")).Text;
        conn.Open();
        SqlCommand cmd = new SqlCommand("Insert Into [tblDepartment](DName, DLocation) values(@DeptName, @DLoc)", conn);
        cmd.Parameters.Add(new SqlParameter("@DeptName", DeptName));
        cmd.Parameters.Add(new SqlParameter("@DLoc", DLoc));
        cmd.ExecuteNonQuery();
        conn.Close();
        Repeater1.DataSource = Bind();
        Repeater1.DataBind();
    }
   
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("Update tblDepartment set DName=@DeptName, DLocation=@DLoc where DeptId=@DID", conn);
        cmd.Parameters.Add(new SqlParameter("@DID", txtID.Text));
        cmd.Parameters.Add(new SqlParameter("@DeptName", txtDepartment.Text));
        cmd.Parameters.Add(new SqlParameter("@DLoc", txtLocation.Text));
        cmd.ExecuteNonQuery();
        conn.Close();
        Repeater1.DataSource = Bind();
        Repeater1.DataBind();
    }
}


Friday, February 17, 2012

Globalization in ASP.Net 4.0

The Program below is a demo for using local and global resource files to implement globalization : dont be confused with the drop down list items :)use the following link to study more about globalization http://www.ezzylearning.com/tutorial.aspx?tid=3477182

WorkingwithEmail.aspx
<%@ Page Language="C#" AutoEventWireup="true" Theme="themeDark" EnableViewState ="true" ViewStateMode ="Enabled" CodeFile="WorkingwithEmail .aspx.cs"
    Inherits="WorkingwithEmail_" culture="auto" meta:resourcekey="PageResource1" uiculture="auto" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        #showiftrue
        {
            width: 109px;
        }
        #showiffalse
        {
            width: 112px;
        }
    </style>
</head>
<body bgcolor="Black">
    <form id="form1" runat="server">
    <center>
        <fieldset class="fieldsetStyle">
            <legend class="legendStyle">SignUp</legend>
            <br />
            <asp:DropDownList ID="ddlCulture" runat="server" AutoPostBack="True"
                meta:resourcekey="ddlCultureResource1">
            <asp:ListItem Value="ur-PK" Text ="Burushaski" meta:resourcekey="ListItemResource1"></asp:ListItem>
            <asp:ListItem Value ="en" Text ="English" meta:resourcekey="ListItemResource2"></asp:ListItem>
            </asp:DropDownList>
            <br />
            <br />
            <asp:Localize ID="Localize1" runat="server" Mode="PassThrough"
                meta:resourcekey="Localize1Resource1" Text="Saadiya Dad"></asp:Localize>
            <br />
            <br />
            <br />
            <asp:Label ID="lblEntermail" runat ="server"
                meta:resourcekey="lblEntermailResource1"></asp:Label>
            <asp:TextBox runat="server" AutoPostBack="True" ID="txtEmailID"
                OnTextChanged="txtEmailID_TextChanged" meta:resourcekey="txtEmailIDResource1"></asp:TextBox>
            <table>
                <tr>
                    <td>
                        <div runat="server" id="showiftrue" visible="false">
                            <asp:Image ID="Image2" runat="server" ImageUrl="avail.jpg" Width="106px"
                                meta:resourcekey="Image2Resource1" />
                        </div>
                    </td>
                    <td>
                        <div runat="server" id="showiffalse" visible="false">
                            <asp:Image ID="Image1" runat="server" ImageUrl="notavailable.jpg" Width="109px"
                                meta:resourcekey="Image1Resource1" /></div>
                    </td>
                </tr>
            </table>
            <asp:Label ID="lblStatus" runat="server" meta:resourcekey="lblStatusResource1"
                EnableViewState="False" ViewStateMode="Disabled"></asp:Label>
            <br />
            <br />
            <br />
            <br />
            <table>
                <tr>
                    <td>
                        <asp:Label ID="lblName" runat="server" Text="Name: "
                            meta:resourcekey="lblNameResource1"></asp:Label><br />
                        <asp:Label ID="lblFName" runat="server" Text="Father's Name: "
                            meta:resourcekey="lblFNameResource1"></asp:Label><br />
                        <asp:Label ID="lblAge" runat="server" Text="Age: "
                            meta:resourcekey="lblAgeResource1"></asp:Label><br />
                        <asp:Label ID="lblCountry" runat="server" Text="Country: "
                            meta:resourcekey="lblCountryResource1"></asp:Label><br />
                        <asp:Label ID="lblEmail" runat="server" Text="Email: "
                            meta:resourcekey="lblEmailResource1"></asp:Label><br />
                    </td>
                    <td>
                    <br />
                        <asp:TextBox ID="txtName" runat="server" EnableViewState="False"
                            ViewStateMode="Disabled" meta:resourcekey="txtNameResource1"></asp:TextBox><br />
                        <asp:TextBox ID="txtFName" runat="server" EnableViewState="False"
                            ViewStateMode="Disabled" meta:resourcekey="txtFNameResource1"></asp:TextBox><br />
                        <asp:TextBox ID="txtAge" runat="server" EnableViewState="False"
                            ViewStateMode="Disabled" meta:resourcekey="txtAgeResource1"></asp:TextBox><br />
                        <asp:TextBox ID="txtCountry" runat="server" EnableViewState="False"
                            ViewStateMode="Disabled" meta:resourcekey="txtCountryResource1"></asp:TextBox><br />
                        <asp:TextBox ID="txtEmail" runat="server" EnableViewState="False"
                            ViewStateMode="Disabled" meta:resourcekey="txtEmailResource1"></asp:TextBox><br />
                        <asp:Button ID="btnSubmit" runat="server" Text="Submit"
                            onclick="btnSubmit_Click" meta:resourcekey="btnSubmitResource1" /><br />
                    </td>
                </tr>
            </table>
            <br />
            <br />
            <asp:Label ID="lblShow" runat ="server" meta:resourcekey="lblShowResource1" ></asp:Label>
        </fieldset>
    </center>
    </form>
</body>
</html>
WorkingwithEmail.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;
using System.Threading;


public partial class WorkingwithEmail_ : System.Web.UI.Page
{
    protected override void InitializeCulture()
    {
        if (Request.Form["ddlCulture"] != null)
        {
            UICulture = Request.Form["ddlCulture"];
        }
        base.InitializeCulture();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        //foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.NeutralCultures))
        //{
        //    ddlCulture.Items.Add(new ListItem(ci.NativeName, ci.Name));
        //}
        Thread.CurrentThread.CurrentCulture=new CultureInfo (ddlCulture.SelectedItem.Value);
        Thread.CurrentThread.CurrentUICulture = new CultureInfo(ddlCulture.SelectedItem.Value);
        Page.Culture = ddlCulture.SelectedItem.Value;
        Page.UICulture = ddlCulture.SelectedItem.Value;
       
        if (IsPostBack)
        {
            ViewState["name"] = txtName.Text;
            ViewState["FName"] = txtFName.Text;
            ViewState["Age"] = txtAge.Text;
            ViewState["Country"] = txtCountry.Text;
            ViewState["Email"] = txtEmail.Text;
        }
        else
        {
            lblName.Font.Size  = FontUnit.Large;
            lblFName.Font.Size = FontUnit.Large;
            lblAge.Font.Size = FontUnit.Large;
            lblCountry.Font.Size = FontUnit.Large;
            lblEmail.Font.Size = FontUnit.Large;
        }
    }
  
    protected void txtEmailID_TextChanged(object sender, EventArgs e)
    {
        SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\Profile\App_Data\EmailID.mdf;Integrated Security=True;User Instance=True");
        DataSet ds = new DataSet();
        SqlCommand cmdShow = new SqlCommand("Select Email from Mail where Email='"+txtEmailID .Text+ "'", connection);
        SqlDataAdapter da = new SqlDataAdapter(cmdShow);
        da.Fill(ds);
        DataTable dt = null;
        dt = ds.Tables[0];
        if (dt.Rows.Count != 0)
        {
            showiffalse.Visible  = true;
            Image2.Visible = false;
            Image1.Visible = true;
            lblStatus.EnableViewState = false;          
            lblStatus.Text = GetGlobalResourceObject("MyGlobalResource","labelstatus").ToString();          

        }
        else
        {
            showiftrue.Visible  = true;
            Image1.Visible = false;
            Image2.Visible = true;
            lblStatus.EnableViewState = false;
            lblStatus.Text = GetGlobalResourceObject("MyGlobalResource", "labelntStatus").ToString();  
        }


       
    }
  
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
            lblShow.ForeColor = System.Drawing.Color.BlanchedAlmond;
            lblShow.Text = " Hello " + ViewState["name"] + " " + ViewState["FName"] + " from " + ViewState["Country"] + " You are " + ViewState["Age"] + " years old ";
            //lblShow.Text = " Hello " + txtName.Text  + " " + txtFName .Text  + " from " + txtCountry .Text + " You are " + txtAge .Text  + " years old ";
    }
}

Wednesday, February 15, 2012

Reading and writing into ViewState

Here is a asimple example of ViewState. Add a button (btnShow) and Label (lblShow to ypur markup and add the following code to your code-behind file
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ViewStateExample : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            lblShow.ForeColor = (System.Drawing .Color )ViewState["Forecolor"];//reading from viewstate
        }
        else
        {
            lblShow.ForeColor = System.Drawing.Color.DarkViolet;
            ViewState["Forecolor"] = lblShow.ForeColor;//writing into viewstate
        }
    }
    protected void btnShow_Click(object sender, EventArgs e)
    {
        lblShow.Text = "Hey GOOOD MORINING :-)";
    }
}

Tuesday, February 14, 2012

Working with DataCache

I have coded the following program to Explain DataCache ( SQLInjection to get datafrom database and put it in to cache) and reading data from an XML file and writing it in to cache




DataCache.aspx :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataCache.aspx.cs" Inherits="DataCache" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
  
    <center ><fieldset ><legend>Data</legend>
<asp:GridView ID="gdData" runat="server" AutoGenerateColumns="true"  EnableModelValidation="True">
</asp:GridView>
   
</fieldset></center>
<fieldset >
<legend>reading from XML</legend>
<asp:GridView ID="gdXmlData" runat="server"></asp:GridView>
 </fieldset>
  
  
    </form>
</body>
</html>

DataCache.aspx.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DLL;

public partial class DataCache : System.Web.UI.Page
{
   
    protected void Page_Load(object sender, EventArgs e)
    {
        gdData.DataSource = DataLayer.selectDept();
        gdData.DataBind();
        gdXmlData.DataSource = DataLayer.ReadFromXmlFile();
        gdXmlData.DataBind();
    }
   
}
DataLayer.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DLL
{
    public static  class DataLayer
    {
        public static void DeleteStaleCache()// explicitly remove cached data from Cache
        {
            HttpContext.Current.Cache.Remove("data");
        }
       
        public static DataTable selectDept()// selecting data from datasource and adding in to Cache
        {
            DataTable dt = new DataTable();
            if (HttpContext.Current.Cache["data"] == null)
            {
               // string conn = ConfigurationManager.ConnectionStrings["SqlDataSource1"].ToString();//  using predefined datasources to create connection
                SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\Cachedata\App_Data\SCOTT.mdf;Integrated Security=True;User Instance=True");
               
                SqlCommand cmd = new SqlCommand("Select * from Department", connection);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
               
                HttpContext.Current.Cache.Insert("data", dt, null, DateTime .Now.AddSeconds(10), System.Web.Caching.Cache.NoSlidingExpiration);
            }
            else
            {
                dt = HttpContext.Current.Cache["data"] as DataTable;
            }
            return dt;
        }

        public static DataTable ReadFromXmlFile()
        {
            DataSet ds = new DataSet();
            DataTable dt = null;
            if (HttpContext.Current.Cache["xmlOutput"] == null)
            {

                string filePath = HttpContext.Current.Server.MapPath("~/App_Data/Department.xml");
                ds.ReadXml(filePath);
             
                dt = ds.Tables[0];
                HttpContext.Current.Cache.Insert("xmlOutput", dt, null, DateTime.Now.AddSeconds(20), System.Web.Caching.Cache.NoSlidingExpiration);
            }
            else
            {
                dt = HttpContext.Current.Cache["xmlOutput"] as DataTable;
            }
          
            return dt;
        }
    }
}

Monday, February 13, 2012

Adding Hash to Passwords


Salted Password Hashing - Doing it Right
If you're a web developer, you've probably had to make a login system. If you've had to make a login system, you've had to use some form of hashing to protect your users' passwords in the event of a security breach. There are a lot of conflicting ideas and misconceptions on how to do password hashing properly. Password hashing is one of those things that's SO simple, but SO MANY people do it wrong. With this page, I hope to explain HOW to securely store passwords in a database, and WHY it should be done that way.





What is password hashing?
hash("hello") = 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
hash("hbllo") = 58756879c05c68dfac9866712fad6a93f8146f337a69afe7dd238f3364946366
hash("waltz") = c0e81794384491161f1777c232bc6bd9ec38f616560b120fda8e90f383853542
Hash algorithms are one way functions, meaning: they turn any amount of data into a fixed-length checksum that cannot be reversed. They also have the property that if the input changes by even a tiny bit, the resulting hash is COMPLETELY different. This is great for us, because we want to be able to be able to store passwords in an encrypted form that's impossible to decrypt. But at the same time, we need to be able to verify that a user's password is correct when they login. Generally, we follow this process:

  1. The user creates an account.
  2. Their password is hashed and stored in the database. At no point is the unhashed user's password ever written to the hard drive.
  3. When the user attempts to login, the hash of the password they entered is checked against the hash in the database.
  4. If the hashes match, the user is granted access. If not, the user is told they entered an incorrect password.
  5. Steps 3 and 4 repeat everytime someone tries to login to their account.


You may think that simply hashing passwords is enough to keep your users' passwords secure in the event of a database leak. Although normal hashing is FAR better than storing passwords in plain text (not hashed), there are a lot of ways to quickly recover passwords from normal hashes. We can do more to make cracking the hashes MUCH more difficult for someone who has stolen your database. If your users' passwords are only hashed, approximately 40% of the hashes can be cracked by a service like CrackStation in the first day that someone gets a hold of your database.

How Hashes are Cracked

  • Dictionary & Brute Force
    Trying aaaa : failed
    Trying aaab : failed
    Trying aaac : failed
             ...
    Trying acdb : failed
    Trying acdc : success!
    Trying apple : failed
    Trying blueberry : failed
    Trying justinbeiber : failed
               ...
    Trying letmein : failed
    Trying secretpassword : success!

    When you have a hash you want to crack, the simplest form of attack is to guess the password using word lists or password cracking dictionaries. That involves hashing every word in the list, and seeing if it's hash matches the hash you're trying to crack. If it does, then you have just found the password for that hash. Brute force attacks are the same as dictionary attacks except they don't use a word list; they try every possible combination of letters, numbers, and symbols.

    There is no way to prevent dictionary attacks or brute force attacks. They can be made less effective, but there isn't a way to prevent them altogeather. If your password hashing system is secure, the only way to crack a hash will be to guess the correct password through a dictionary attack or brute force attack.

  • Lookup Tables
    Searching: 5f4dcc3b5aa765d61d8327deb882cf99: FOUND: password5
    Searching: 6cbe615c106f422d23669b610b564800:  not in database
    Searching: 630bf032efe4507f2c57b280995925a9: FOUND: letMEin12
         ...
    Searching: 386f43fab5d096a7a66d67c8f213e5ec: FOUND: mcd0nalds
    Searching: d5ec75d5fe70d428685510fae36492d9: FOUND: p@ssw0rd!

    Say you have a database of 1 Million hashes. You want to perform a dictionary attack on every hash, but you don't want to do 1 million dictionary attacks. What you do is hash every word in your dictionary, and store the word:hash pair in a lookup table. Next, you go through all the hashes you want to crack and see if the hash exists in the lookup table. If it does, you've just found the password. In this case the lookup table method is MUCH faster than doing 1 million dictionary attacks. You only have to hash each word in your wordlist once, then perform 1 million lookups (which are VERY fast). These lookup table databases DO exist! CrackStation is one of them!

  • Rainbow Tables

    Rainbow tables are a hybrid of lookup tables and brute force. In brief, they combine the two methods to reduce the overall size needed to store the wordlist. They do so by using a time-memory trade-off, making it take a little longer to crack one hash, but reducing the amount of hard drive space required to store the lookup table. For our purposes, we can think of lookup tables and rainbow tables as the same thing.

The WRONG Way: Double Hashing & Wacky Hash Functions

This is a common one. The idea is that if you do something like md5(md5($password)) or even md5(sha1($password)) it will be more secure since plain md5 is "broken". I've even seen someone claim that it's better to use a super complicated function like md5(sha1(md5(md5($password) + sha1($password)) + md5($password))). While complicated hash functions can sometimes be useful for generating encryption keys, you won't get much more security by combining hash functions. It's far better to choose a secure hash algorithm in the first place, and use salt, which I will discuss later. Once you are using salt, you can use multiple secure hash functions, for example SHA256(WHIRLPOOL($password + $salt) + $salt). Combining secure hash functions will help if a practical collision attack is ever found for one of the hash algorithms, but it doesn't stop attackers from building lookup tables.

The attacks on MD5 are collision attacks. That means it's possible to find two different strings that have the same MD5 hash. If we were trying to prevent such an attack from affecting our cryptosystem, double hashing is the wrong thing to do. If you can find two strings of data such that md5($data) == md5($differentData), then md5(md5($data)) will STILL be the same as md5(md5($differentData)). Because the "inside" hashes are the same, so the "outside" hashes will be too. Adding the second hash did nothing. The collision attacks on MD5 don't make it any easier to recover the password from an md5 hash, but it's good practice to stop using MD5 just because there are much better functions readily available.

Double hashing does not protect against lookup tables or rainbow tables. It makes the process of generating the lookup table two times slower, but we want it to be impossible to use lookup tables. We can easily do so by adding "salt".

Adding Salt

hash("hello") =                    2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
hash("hello" + "QxLUF1bgIAdeQX") = 9e209040c863f84a31e719795b2577523954739fe5ed3b58a75cff2127075ed1
hash("hello" + "bv5PehSMfV11Cd") = d1d3ec2e6f20fd420d50e2642992841d8338a314b8ea157c9e18477aaef226ab
Salt is nothing complicated, just a string of random characters that get appended to the password before hashing. When done properly, it renders lookup tables and rainbow tables useless. Salt does so because, by adding extra characters, the resulting hash is COMPLETELY different than the unsalted hash of the password. For example, if the user's password was "apple", the SHA256 hash would be 3a7bd3e2360a3d29eea436fcfb7e... but if we append the salt, "Uwqe2uXdSKpAAi" before hashing, we get b7d07a9b609b222a73c750584e69... which has NO similarity AT ALL to the unsalted hash. Even a small change in the salt will result in a completely different hash, so if a lookup table has been created for unsalted hashes, it cannot be used to crack salted hashes. If a lookup table were created with the salt "HdK92TLAOP71", then it would be useless for cracking hashes that were salted with "EEbbTsLyddNO". When salting is done properly, it renders lookup tables and rainbow tables completely useless.

The WRONG Way: Short Salt & Salt Re-use

The most common error of hash salting is using the same salt for every password. Someone trying to crack 1 million hashes that were all salted with the same salt would simply have to re-make his lookup table. He would just create a lookup table matching the words in a dictionary to the salted hashes of the words. He could then use the lookup table to VERY quickly attempt to crack all 1 million passwords.

The second most common error of hash salting is using a salt that's too short. Imagine 10 million password are hashed using random salts, but the salt is only 2 ASCII characters. Since there are only 95 printable ASCII characters, and the salt is only 2 characters long, there are 952 = 9 025 possible salt values. Since there are 10 million passwords, there will be 10 000 000 / 9 025 = 1 108 passwords using each salt. Someone trying to crack these hashes would make a lookup table for every possible salt value (9025 lookup tables), then use each lookup table to try to crack all the passwords that were using the same salt. The obvious fix to this problem is to use really long salt so that it's impossible to create a lookup table for every possible salt value.

It's also important not to rely on the username for salt. Usernames will be unique on YOUR website, but many other websites will have users of the same name. Someone trying to crack hashes would make a lookup table for every common username, and use them to crack hashes from different websites' databases. Since our goal is to have a unique and random salt for every password, using the username as salt has little security benefit.

The RIGHT Way: How to Hash Properly

To combat lookup tables and rainbow tables, all we have to do is give each password a long unique salt. Since no two passwords will ever be hashed using the same salt, and since there are so many possible salt values, lookup tables and rainbow tables become useless. The only way to recover the password from a hash with a unique salt is to guess the password (dictionary attack) or perform a brute force attack.

To guarantee the uniquness of the salt, it's best to use a randomly generated salt that's at least as long as the output of the hash function. If your hash function has a 256 bit output, then use 256 bits of salt. I find that the easiest way to ensure you're getting enough salt is to generate a random hex string that's the same length as the hash output. Make sure you use a Cryptographically Secure Pseudo-Random Number Generator (CSPRNG). Do NOT use your language's math library's rand() function. There will be a proper CSPRNG for you to use. In php, it's mcrypt_create_iv() and in .NET it's System.Security.Cryptography.RNGCryptoServiceProvider. Since you want each password to have it's own salt, it's important to change the salt whenever the password is changed.

You only need to generate the salt when an account is created or a user changes their password. You store the salt in your database so that it can be used to validate the user's password when they login. The salt doesn't have to be secret at all. All that matters is that it's unique for every hash that's stored in your database.

The salt need not be secret because it's only purpose is to make sure that if two users have the same password, the hash of their passwords will be different. Once the password has been hashed with the salt, there's no way that the salt can be "removed" from the hash, even if it is known by the password cracker.

The algorithm for storing the password is as follows:
  1. Generate a long random salt using a CSPRNG.
  2. Compute $hash = Hash($password . $salt), where Hash() is a strong hash function like SHA256.
  3. Save $hash and $salt in the database.
To validate a password (when the user tries to login):
  1. Get the $hash and $salt for that user from the database.
  2. Compute the hash of the password they tried to login with. $userhash = Hash($pass_to_check . $salt).
  3. Compare $hash with $userhash. If they are EXACTLY the same, then the password is valid. If there is any difference, then the password is invalid.


Instead of using multiple hash functions or creating your own, just stick to one well known and well tested algorithm. All you need is one. I would reccomend using SHA256.

Rules of thumb:

  • Use a well-known and secure hash algorithm like SHA256.
  • Each password should be hashed with a different salt.
  • Salt should be a random string of characters at least AS LONG AS the output of the hash function.
  • Use a CSPRNG to generate salt, NOT your language's built in rand() function.
  • When passwords are changed, the salt must be changed.

FAQ

What hash algorithm should I use?

DO use:

DO NOT use:

  • MD5
  • SHA0 or SHA1
  • crypt unless it uses SHA256 or SHA512
  • Any algorithm that you made yourself or hasn't gone through an intensive peer review process like the SHA3 competition

How long should the salt be?

The salt should be at least as long as the hash function. For example, if your hash function is 256 bits, then you should have a salt of at least 256 bits. I find that the easiest way to generate enough salt is to generate a random string of hex characters that is the same length as the hash function output (64 hex characters for 256 bits). First and foremost, your salt should be long enough so that no two users' passwords will ever be hashed using the same salt.

How do I generate the salt?

Use a Cryptographically Secure Pseudo-Random Number Generator (CSPRNG). Do NOT use your language's math library's rand() function. There will be a proper CSPRNG for you to use. In PHP, it's mcrypt_create_iv() and in .NET it's System.Security.Cryptography.RNGCryptoServiceProvider. The imporant thing is that the salt is unique for each user. Using a high quality CSPRNG to generate a long salt will practically guarantee uniqueness without needing to manually check if the salt has been used before.

What do I do if my database gets leaked/hacked?

Tell your users RIGHT AWAY. Even if the passwords were salted and hashed properly, dictionary and brute force attacks can still be used to recover your users' passwords. It's important that you let your users know so that they can change their passwords if they suspect someone would try to brute force the hash of their password. If you got hacked, always assume that the hacker was able to obtain full control over your server and was able to leak the entire database. When you tell them, include the following information:

  • How you got hacked.
  • How much of the database you think the hacker got.
  • Explain what personal information was in the database.
  • Explain how you were hashing the passwords, and that their passwords may still be recovered through brute force or dictionary attacks.

Why bother hashing?

Your users are entering their password into your website. They might be using the same passwords for other websites, like their bank. If your database gets hacked, and you didn't hash the passwords, then everyone will know your users' passwords. YOU are responsible for your users' security when using your website. If you are some sort of software that will be sold or distributed to many people, you have an even higher degree of responsibility to your users.

What should my password policy be? Should I enforce strong passwords?

Don't limit your users. I would reccomend somehow dynamically showing users the strength of their password as they type it, and let them decide how secure they want their password to be. If your service handles sensitive user information, you may want to ensure that there is at least 1 number and 1 symbol in the password. Passwords should be able to contain ANY type of character. The password length should be a minimum of 6 characters and a maximum of beyond 100 characters (Yes, There are people who use 100 character and longer passwords!).

If someone has access to my database, can't they just replace the hash of my password with their own hash so they can login?

Yes. But if someone has accesss to your database, they probably already have access to everything on your server, so they wouldn't need to login to your account to get what they want.

However, there is something you can do to prevent this. Create special database permissions so that only the account creation script has write access to the user table, and give all other scripts read only access. Then, if an attacker can access your user table through a SQL injection vulnerability, he won't be able to modify the hashes.

Is there anything that can be done to make dictionary attacks and brute force attacks harder?

$x = SHA256($password . $salt)
Repeat 65000 times:
$x = SHA256($x . $password . $salt)
Yes. You can have your program recursively hash the password many thousands of times (feed the output back into the input). Doing so makes the password hashing process thousands of times slower, so it will take thousands of times longer to brute force the password. This is called key stretching. A common key stretching algorithm is PBKDF2. If you want to use PBKDF2 in PHP, use Defuse Cyber-Security's implementation.


PHP Password Hashing Code

The following is a secure implementation of salted hashing in PHP. If you want to use PBKDF2 in PHP, use Defuse Cyber-Security's implementation.

//Takes a password and returns the salted hash
//$password - the password to hash
//returns - the hash of the password (128 hex characters)
function HashPassword($password)
{
 $salt = bin2hex(mcrypt_create_iv(32, MCRYPT_DEV_URANDOM)); //get 256 random bits in hex
 $hash = hash("sha256", $salt . $password); //prepend the salt, then hash
 //store the salt and hash in the same string, so only 1 DB column is needed
 $final = $salt . $hash; 
 return $final;
}

//Validates a password
//returns true if hash is the correct hash for that password
//$hash - the hash created by HashPassword (stored in your DB)
//$password - the password to verify
//returns - true if the password is valid, false otherwise.
function ValidatePassword($password, $correctHash)
{
 $salt = substr($correctHash, 0, 64); //get the salt from the front of the hash
 $validHash = substr($correctHash, 64, 64); //the SHA256

 $testHash = hash("sha256", $salt . $password); //hash the password being tested
 
 //if the hashes are exactly the same, the password is valid
 return $testHash === $validHash;
}
   

ASP.NET (C#) Password Hashing Code

The following code is a secure implementation of salted hashing in C# for ASP.NET

using System;
using System.Text;
using System.Security.Cryptography;

namespace DEFUSE
{
    /*
     * PasswordHash - A salted password hashing library
     * WWW: https://defuse.ca/
     * Use:
     *      Use 'HashPassword' to create the initial hash, store that in your DB
     *      Then use 'ValidatePassword' with the hash from the DB to verify a password
     *      NOTE: Salting happens automatically, there is no need for a separate salt field in the DB
     */
    class PasswordHash
    {
 /// <summary>
 /// Hashes a password
 /// </summary>
 /// <param name="password">The password to hash</param>

 /// <returns>The hashed password as a 128 character hex string</returns>
 public static string HashPassword(string password)
 {
     string salt = GetRandomSalt();
     string hash = Sha256Hex(salt + password);
     return salt + hash;
 }

 /// <summary>
 /// Validates a password
 /// </summary>
 /// <param name="password">The password to test</param>

 /// <param name="correctHash">The hash of the correct password</param>
 /// <returns>True if password is the correct password, false otherwise</returns>
 public static bool ValidatePassword(string password, string correctHash )
 {
     if (correctHash.Length < 128)
  throw new ArgumentException("correctHash must be 128 hex characters!");
     string salt = correctHash.Substring(0, 64);
     string validHash = correctHash.Substring(64, 64);
     string passHash = Sha256Hex(salt + password);
     return string.Compare(validHash, passHash) == 0;
 }

 //returns the SHA256 hash of a string, formatted in hex
 private static string Sha256Hex(string toHash)
 {
     SHA256Managed hash = new SHA256Managed();
     byte[] utf8 = UTF8Encoding.UTF8.GetBytes(toHash);
     return BytesToHex(hash.ComputeHash(utf8));
 }

 //Returns a random 64 character hex string (256 bits)
 private static string GetRandomSalt()
 {
     RNGCryptoServiceProvider random = new RNGCryptoServiceProvider();
     byte[] salt = new byte[32]; //256 bits
     random.GetBytes(salt);
     return BytesToHex(salt);
 }

 //Converts a byte array to a hex string
 private static string BytesToHex(byte[] toConvert)
 {
     StringBuilder s = new StringBuilder(toConvert.Length * 2);
     foreach (byte b in toConvert)
     {
  s.Append(b.ToString("x2"));
     }
     return s.ToString();
 }
    }
}
   

Article by Defuse Cyber-Security.