Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: locked Sqlite Data
Here is my situation with code and error.
 
I have developed an asp.net 4.0 website with SQLite3 using visual studio and I am running
TechInfoSystems.Data.SQLiteProvider.dll
and
System.Data.SQLite.dll 1.0.66.0
 
I just recently decided I can't save passwords for user login as clear text in my database, so I am now hashing them using SHA1. Login still works great and now the passwords are saved as one-way hex values in the database which is what I want.
 
What this requires though is adequate password recovery. I put a link to reset password on the login.aspx page, this link takes you to ResetPassword.aspx which contains the asp.net control PasswordRecovery. I changed the UserName, question, and success parts into templates for a different format.
 
Now, When the User is redirected to ResetPassword.aspx, user is prompted to enter their username. Great! Then click submit button, and the question template arises to ask the user what the security question answer is. Great! here is the problem, if the user enters an incorrect password, the success page is not shown, and the page sits their processing. After ~20 seconds I see an error that says the database is locked. HERE IS THAT EXACT ERROR:
 

The database file is locked
database is locked
 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
 
Exception Details: System.Data.SQLite.SQLiteException: The database file is locked
database is locked
 
Source Error:
 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
 
Stack Trace:
 

[SQLiteException (0x80004005): The database file is locked
database is locked]
System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt) +499
System.Data.SQLite.SQLiteDataReader.NextResult() +249
System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) +79
System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) +38
System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() +39
TechInfoSystems.Data.SQLite.SQLiteMembershipProvider.UpdateFailureCount(String username, String failureType, Boolean isAuthenticated) +2265
TechInfoSystems.Data.SQLite.SQLiteMembershipProvider.ResetPassword(String username, String passwordAnswer) +1760
System.Web.Security.MembershipUser.ResetPassword(String passwordAnswer) +110
System.Web.Security.MembershipUser.ResetPassword(String passwordAnswer, Boolean useAnswer, Boolean throwOnError) +48
System.Web.UI.WebControls.PasswordRecovery.AttemptSendPasswordQuestionView() +374
System.Web.UI.WebControls.PasswordRecovery.AttemptSendPassword() +77
System.Web.UI.WebControls.PasswordRecovery.OnBubbleEvent(Object source, EventArgs e) +103
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +125
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +167
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
 

HERE IS MY CODE:
<%@ Page Title="Change Password" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="ResetPassword.aspx.cs" Inherits="Account_ChangePassword" %>
 
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>
        Reset Password
    </h2>
                <asp:PasswordRecovery ID="PasswordRecovery" runat="server" Height="104px" Width="409px" Enabled="True" ViewStateMode="Disabled" MembershipProvider="SQLiteMembershipProvider">
                    <MailDefinition IsBodyHtml="True" From="No-Reply@gmail.com" >
 
                    </MailDefinition>
 
                     <UserNameTemplate >
 
                                        <fieldset class="passwordRecovery">
                                            <legend>Password Recovery</legend>
                                        <p>
                                             <asp:Label ID="UserNameLabel" runat="server" AssociatedControlID="UserName">Username:</asp:Label>
                                            <asp:TextBox ID="UserName" runat="server" CssClass="textEntry"></asp:TextBox>
 
                                            <asp:RequiredFieldValidator ID="UserNameRequired" runat="server" ControlToValidate="UserName"
                                                     CssClass="failureNotification" ErrorMessage="User Name is required." ToolTip="User Name is required."
                                                    ValidationGroup="PasswordRecovery1">*</asp:RequiredFieldValidator>
                                        </p>
                                            <p>
                                                &nbsp;<asp:ValidationSummary ID="LoginUserValidationSummary" runat="server"
                                                    CssClass="failureNotification" Height="37px"
                                                    ValidationGroup="PasswordRecovery1" Width="378px" />
                                                <p>
                                                    <br />
                                                </p>
                                            </p>
                                 </fieldset>
                                 <p align="right">
                                 <asp:Button ID="Submit" runat="server" CommandName="Submit" Text="Submit"
                                         ValidationGroup="passwordRecovery" />
                                 </p>
                    </UserNameTemplate>
                    <QuestionTemplate>
                        <table cellpadding="1" cellspacing="0" style="border-collapse:collapse;">
                            <tr>
                                <td>
                                    <table cellpadding="0" style="height:104px;width:409px;">
                                        <tr>
                                            <td align="left" colspan="2">
                                                Identity Confirmation</td>
                                        </tr>
                                        <tr>
                                            <td align="left" colspan="2">
                                                Answer the following question to receive your password.</td>
                                        </tr>
                                        <tr>
                                            <td align="right">
                                                User Name:</td>
                                            <td>
                                                <asp:Literal ID="UserName" runat="server"></asp:Literal>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td align="right">
                                                Question:</td>
                                            <td>
                                                <asp:Literal ID="Question" runat="server"></asp:Literal>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td align="right">
                                                <asp:Label ID="AnswerLabel" runat="server" AssociatedControlID="Answer">Answer:</asp:Label>
                                            </td>
                                            <td>
                                                <asp:TextBox ID="Answer" runat="server"></asp:TextBox>
                                                <asp:RequiredFieldValidator ID="AnswerRequired" runat="server"
                                                    ControlToValidate="Answer" ErrorMessage="Answer is required."
                                                    ToolTip="Answer is required." ValidationGroup="PasswordRecovery1"
                                                    CssClass="failureNotification">*</asp:RequiredFieldValidator>
                                            </td>
                                        </tr>
                                        <tr>
                                            <td align="left" colspan="2" style="color:Red;">
                                                <asp:Literal ID="FailureText" runat="server" EnableViewState="False"></asp:Literal>
                                                <br />
                                                <asp:ValidationSummary ID="LoginUserValidationSummary" runat="server"
                                                    CssClass="failureNotification" Height="37px"
                                                    ValidationGroup="PasswordRecovery1" Width="378px" />
                                            </td>
                                        </tr>
                                        <tr>
                                            <td align="right" colspan="2">
                                                <asp:Button ID="SubmitButton" runat="server" CommandName="Submit" Text="Submit"
                                                    ValidationGroup="PasswordRecovery1" onclick="SubmitButton_Click" />
                                            </td>
                                        </tr>
                                    </table>
                                </td>
                            </tr>
                        </table>
                    </QuestionTemplate>
                   <SuccessTemplate>
                         <table cellpadding="1" cellspacing="0" style="border-collapse:collapse;">
                             <tr>
                                 <td>
                                     <table cellpadding="0" style="height:104px;width:409px;">
                                         <tr>
                                             <td>
                                                 Your password has been sent to you.</td>
                                         </tr>
                                     </table>
                                 </td>
                             </tr>
                         </table>
                    </SuccessTemplate>
                </asp:PasswordRecovery>
</asp:Content>
 

 

IF the user enters the correct security question answer, the user sees the success page and an email is properly dispatched to the user.
 
I understand people have had multithreading problems with SQLite and usually this is the error that is produced, but This provider is meant to mimmick the SQLprovider that microsoft made but for SQLite. What I mean by this is, the .net Controls that microsoft have to offer, "PasswordRecovery" should not have queries that cause concurrency issues.
 
UPDATE: The c# code is embedded in one of the classes that this .NET control uses and is executed behind the scenes, so I can't pinpoint what actual method makes the database lock out. I am guessing its MembershipUser.ResetPassword(passwordAnswer). I implemented an onClick method after answering the security question that tries to manually reset the password, used that method, and thats where it locked the database.I also tried SQLiteMembershipProvider.ResetPassword(), and that also locked the database. Keep in mind, I am not explicitly making database queries, the .net providers are. So I can be sure that the queries are disposed of properly.
 
I appreciate your input as I have looked for similar problems with this control and have found nothing.
 
Thanks.
Posted 12-Nov-11 11:01am
Edited 13-Nov-11 11:13am
v8
Comments
thatraja at 13-Nov-11 3:59am
   
Give us complete error message with full details
koolsuccess at 13-Nov-11 15:18pm
   
I have updated my question above and included full details. Please ask additional questions if you need more information.
Thanks.
koolsuccess at 13-Nov-11 16:04pm
   
Thanks, I have included the Code a well
koolsuccess at 13-Nov-11 16:15pm
   
Hey thatraja, I have examined those links and they are not of any help. I am not explicitly making calls to the database where I need to worry about disposing objects, etc, the .NET control is doing that behind the scenes, and I assume it is doing it in a way that should avoid concurrency.
 
Please read my update and check out the code I have provided.
 
THANKS =)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

raja, I looked through all those links and unfortunately, they do not resolve the specific problem I am having.
 
I updated my question, and still looking for suggestions...
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

I solved this myself.
 
I had to avoid using the PasswordRecovery Control that asp.net offers.
 
I am also discontinuing the use of a security question/answer.
 
Now, when a user needs to reset his/her password, all they need to do is type in their email address, and I manually ResetPassword() //only non overloaded method works without locking my database.
 
Then I manually email them the password using the email they provided.
C# Code behind:
 
protected void Submit_Click(object sender, EventArgs e)
{
string email = EmailTextBox.Text.ToString();
String username = Membership.Providers["SQLiteMembershipProvider"].GetUserNameByEmail(email);
 
if (username != null)
{
CustomValidator1.IsValid = true;
String newPassword = "";
MembershipUser mu = Membership.Providers["SQLiteMembershipProvider"].GetUser(username, false);
if (mu != null)
newPassword = mu.ResetPassword();
System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();
SmtpClient smtp = new SmtpClient();
message.To.Add(xxxxx@xx.com");
message.Subject = "Your Password has been reset";
message.From = new MailAddress("No-Reply@gmail.com");
message.Body = "Your new Password is: " + newPassword;
message.IsBodyHtml = true;
smtp.Host = "smtp.gmail.com";
smtp.Port = 25;
smtp.EnableSsl = true;
smtp.Credentials = new System.Net.NetworkCredential("xxxx", "xxxxx");
smtp.Send(message);
Session["PasswordEmailed"] = true;
Response.Redirect("~/Account/Login.aspx", true);
}
else
{
CustomValidator1.IsValid = false;
CustomValidator1.DataBind();

}
}
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

SQLite only supports sequential writes. You need to close the first connection and then update the failure count. Change GetPassword in SQLiteMembershipProvider.cs to this:
 
public override string GetPassword(string username, string answer)
{
    // MY HACK TO MAKE UPDATE FAILURE COUNT WORK //
    bool passed = true;
    // MY HACK TO MAKE UPDATE FAILURE COUNT WORK //

    if (!EnablePasswordRetrieval)
	{
		throw new ProviderException("Password retrieval not enabled.");
	}
 
	if (PasswordFormat == MembershipPasswordFormat.Hashed)
	{
		throw new ProviderException("Cannot retrieve hashed passwords.");
	}
 
	SQLiteConnection cn = GetDBConnectionForMembership();
	try
	{
		using (SQLiteCommand cmd = cn.CreateCommand())
		{
			cmd.CommandText = "SELECT Password, PasswordFormat, PasswordSalt, PasswordAnswer, IsLockedOut FROM "
				+ USER_TB_NAME + " WHERE LoweredUsername = $Username AND ApplicationId = $ApplicationId";
 
			cmd.Parameters.AddWithValue("$Username", username.ToLowerInvariant());
			cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
 
			if (cn.State == ConnectionState.Closed)
				cn.Open();
 
			using (SQLiteDataReader dr = cmd.ExecuteReader((CommandBehavior.SingleRow)))
			{
				string password, passwordAnswer, passwordSalt;
				MembershipPasswordFormat passwordFormat;
 
				if (dr.HasRows)
				{
					dr.Read();
 
					if (dr.GetBoolean(4))
						throw new MembershipPasswordException("The supplied user is locked out.");
 
					password = dr.GetString(0);
					passwordFormat = (MembershipPasswordFormat)Enum.Parse(typeof(MembershipPasswordFormat), dr.GetString(1));
					passwordSalt = dr.GetString(2);
					passwordAnswer = (dr.GetValue(3) == DBNull.Value ? String.Empty : dr.GetString(3));
				}
				else
				{
					throw new MembershipPasswordException("The supplied user name is not found.");
				}
 
				if (RequiresQuestionAndAnswer && !ComparePasswords(answer, passwordAnswer, passwordSalt, passwordFormat))
				{
                                    // MY HACK TO MAKE UPDATE FAILURE COUNT WORK //
                                    passed = false;
                                    // MY HACK TO MAKE UPDATE FAILURE COUNT WORK //
				}
 
				if (passwordFormat == MembershipPasswordFormat.Encrypted)
				{
					password = UnEncodePassword(password, passwordFormat);
				}
 
				return password;
			}
		}
	}
	finally
	{
		if (!IsTransactionInProgress())
			cn.Dispose();
                // MY HACK TO MAKE UPDATE FAILURE COUNT WORK //
                if (passed == false)
                {
                    UpdateFailureCount(username, "passwordAnswer", false);
        
                    throw new MembershipPasswordException("Incorrect password answer.");
                }
                // MY HACK TO MAKE UPDATE FAILURE COUNT WORK //
    	}
}
 
Hoped this helps.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 575
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 369
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,476
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,917


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 23 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100