Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

  • Tom Allen 50 posts 71 karma points
    Nov 02, 2011 @ 16:41
    Tom Allen

    Member password hashing and CSV/SQL import direct to database


    I'm building a site to replace a previous non-Umbraco site which had about 1,200 members. I've imported these members using the excellent CMS Import package; however it has generated new passwords for all of the members.

    I have a CSV file with the original plain-text passwords alongside the usernames/emails (which I know is a bit of a security concern but I didn't build the original site). Is there any way I might use SQL to import the CSV, hash the passwords using Umbraco's algorithm, and update the Member records based on the email or username?



  • Tom Allen 50 posts 71 karma points
    Nov 02, 2011 @ 20:03
    Tom Allen

    OK - I solved this eventually. What a headache!

    For anyone in a similar situation (needing to update imported members with plain-text passwords from another database), here's my botch job:

    1. Export a CSV containing only the usernames and plain-text passwords, comma-separated, without text delimiters, like this:


    2. Create a new ASP.NET web app in VS
    3. In Default.aspx, put this code in the page body:

        <asp:TextBox ID="TextBox1" runat="server" TextMode="MultiLine"></asp:TextBox>
        <asp:TextBox ID="TextBox2" runat="server" TextMode="MultiLine"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />

    4. In Default.aspx.cs, paste this code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Text;
    using System.Security.Cryptography;
    namespace WebApplication1
        public partial class _Default : System.Web.UI.Page
            protected void Button1_Click(object sender, EventArgs e)
                TextBox2.Text = "";
                char[] delimiters = new char[] { '\r', '\n' };
                string[] data = TextBox1.Text.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
                foreach (string user in data)
                    char[] delimiters2 = new char[] { ',' };
                    string[] fields = user.Split(delimiters2, StringSplitOptions.RemoveEmptyEntries);
                    string username = fields[0];
                    string password = fields[1];
                    HMACSHA1 hash = new HMACSHA1();
                    hash.Key = Encoding.Unicode.GetBytes(password);
                    string encodedPassword = Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
                    TextBox2.Text += "UPDATE [dbo].[cmsMember] SET [Password] = '" + encodedPassword + "' WHERE [LoginName] = '" + username + "'" + System.Environment.NewLine;
                    TextBox2.Text += "GO" + System.Environment.NewLine;

    5. Fire up your new web app
    6. Copy/past the contents of your CSV into the first textbox
    7. Behold the SQL script that appears in the second textbox
    9. Run the SQL script on your database.

    Et voila - your members' passwords will be updated with their hashed versions. Hope that saves someone some time, some day...

Please Sign in or register to post replies

Write your reply to: