Using SQLite for Simple Database Storage - Storing it in the Database

(Page 5 of 6 )

With the password encoded, even if people can read the database file they will have some difficulty reverse engineering the hash to determine a password.  Now we can store it in the database.

Public Sub SetPassword(ByVal pwd As String)

  Dim Query As New SQLiteCommand("UPDATE user SET password=? WHERE id=?", Conn)
  Dim p As String

  p = HashPassword(pwd)
  Query.CreateAndAddUnnamedParameters()
  Query.Parameters(0).Value = p
  Query.Parameters(1).Value = Id
  Query.Prepare()
  Query.ExecuteNonQuery()

End Sub

So that users can log in to our application, we need a way to verify user names and passwords. The Authenticate method takes the user name and password as arguments, verifies their authenticity, and if they're good, it will also populate the user object.

Public Function Authenticate(ByVal N As String, ByVal P As String) As Boolean

  Dim Query As New SQLiteCommand("SELECT id, name, email FROM user " +
                                 " WHERE name=? AND password=?", Conn)
  Dim Reader As SQLiteDataReader
  Dim pwd As String

  pwd = HashPassword(p)
  Query.CreateAndAddUnnamedParameters()
  Query.Parameters(0).Value = N
  Query.Parameters(1).Value = pwd
  Query.Prepare()
  Reader = Query.ExecuteReader

  If Reader.Read Then
    Populate(Reader)
    Return True
  Else
    Return False
  End If

End Function 

We also need to assure uniqueness for our user names.  The database schema itself only enforces uniqueness for the id.  We could put a UNIQUE constraint on the name field, but then our application would only die with a cryptic error, and it would really be better to inform the user of the problem gracefully and give them a chance to change things.  Thus our Unique function will check on the requested name to see if its already in use.

Public Function Unique(ByVal requested As String) As Boolean

  Dim Query As New SQLiteCommand()
  Dim count As Integer

  Query.Connection = Conn
  Query.CommandText = "SELECT COUNT(id) FROM user WHERE name=?"
  Query.CreateAndAddUnnamedParameters()
  Query.Parameters(0).Value = requested
  Query.Prepare()
  count = Query.ExecuteScalar()

  If count = 0 Then
    Return True
  Else
    Return False
  End If

End Function

I now have a complete User object which can maintain the membership on my website.  Because I've used the SQLite database, I can do this without having to configure a complicated database connection.  This can save me considerable money in a web hosting situation, and reduce my system administration overhead if I run this application on my own server. 

Next: The Last Step >>
 

More Database Articles
More By Clay Dowling

Comments On: Using SQLite for Simple Database Storage

· Hello!Great article here. SQLite really is a simple, fast and powerful database...    · Its a very helpful article...I have been looking for database designed with...   >>> Post your comment now!