How to Implement Secure User Login with SQLite in a C# WPF Project?

Question

Hello
I am working on a C# WPF project that uses an SQLite database. I want to implement a username and password mechanism to prevent unauthorized access to the application. At the same time, I’m concerned about protecting sensitive information in the database (e.g., passwords or other critical data).

How can I securely set up a user authentication system using SQLite?
What methods (e.g., hashing algorithms) would you recommend for storing passwords?
What should I consider to ensure complete security for database access?

I would appreciate your guidance! Thanks in advance.

Answer

For securing the database see the following.

For hashing, consider using NuGet package BCrypt.Net-Next. Here is a simple example and I created a console code sample which can be adapted to WPF.

using System.Data.SQLite;
using Dapper;

namespace HashingPasswordsApp.Classes;
internal class DapperOperations
{
    private static string _name = "HashingPasswords.db";
    private static string ConnectionString() => $"Data Source={_name}";

    public static void Add()
    {

        Clients clients = new()
        {
            UserName = "JohnDoe",
            UserPassword = BC.HashPassword("MyPassword")
        };

        var addStatement =
            """
            INSERT INTO Clients (UserName, UserPassword)
            VALUES (@UserName, @UserPassword);
            SELECT last_insert_rowid();
            """;

        using var cn = new SQLiteConnection(ConnectionString());
        clients.Id = cn.ExecuteScalar(addStatement,clients).GetId();

        AnsiConsole.MarkupLine($"[bold yellow]Client {clients.UserName} added with Id {clients.Id}[/]");

        var selectStatement =
            """
            SELECT * FROM Clients WHERE UserName = 'JohnDoe';
            """;

        var client = cn.QueryFirstOrDefault(selectStatement);

        var verified = BC.Verify("MyPassword", client.UserPassword);

        if (verified)
        {
            AnsiConsole.MarkupLine("[bold green]Password verified[/]");
        }
        else
        {
            AnsiConsole.MarkupLine("[bold red]Password not verified[/]");
        }
    }
}

Answer

Hi,@fatih uyanık. Welcome to Microsoft Q&A.

Storing passwords using hashing

As Karen Payne MVP, mentioned, you might consider using BCrypt.Net-Next. Also consider using PBKDF2. The following is a simple implementation of user authentication through PBKDF2, you could refer to it.

MainWindow.xaml

MainWindow.xaml.cs(Install System.Data.SQLite via NuGet)

public partial class MainWindow : Window,INotifyPropertyChanged
{
    private string connectionString = $"Data Source=mydatabase.sqlite;Version=3;";

    private string userName_Login;
    public string UserName_Login { get { return userName_Login; } set {userName_Login = value; OnPropertyChanged(nameof(UserName_Login)); } }

    private string password_Login;
    public string Password_Login { get { return password_Login; } set { password_Login = value; OnPropertyChanged(nameof(Password_Login)); } }

    private string userName_register;
    public string UserName_Register { get { return userName_register; } set { userName_register = value; OnPropertyChanged(nameof(UserName_Register)); } }

    private string password_register;
    public string Password_Register { get { return password_register; } set { password_register = value; OnPropertyChanged(nameof(Password_Register)); } }

    public event PropertyChangedEventHandler? PropertyChanged;

    public void OnPropertyChanged(string propertyName)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }

    public MainWindow()
    {
        InitializeComponent();
        this.DataContext = this;
        string databasePath = "mydatabase.sqlite";
        if (!File.Exists(databasePath))
        {
            CreateDatabase(databasePath,connectionString);
        }
    }

    private void CreateDatabase(string databasePath,string connectionString) 
    { 
            SQLiteConnection.CreateFile(databasePath); 
            using (SQLiteConnection connection = new SQLiteConnection(connectionString)) 
            { 
                connection.Open();
                string createTableQuery = @"CREATE TABLE IF NOT EXISTS Users (ID INTEGER PRIMARY KEY AUTOINCREMENT,Username TEXT NOT NULL UNIQUE,PasswordHash TEXT NOT NULL);";     
                using (SQLiteCommand command = new SQLiteCommand(createTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
            }  
    }

    public void RegisterUser(string username, string password) 
    { 
        using (var connection = new SQLiteConnection(connectionString)) 
        { 
            connection.Open(); 
            string hashPassword = PasswordHelper.HashPassword(password);
            string query = "INSERT INTO Users (Username, PasswordHash) VALUES (@Username, @PasswordHash)"; 
            using (var command = new SQLiteCommand(query, connection)) 
            { 
                command.Parameters.AddWithValue("@Username", username); 
                command.Parameters.AddWithValue("@PasswordHash", hashPassword); 
                command.ExecuteNonQuery(); 
            } 
        } 
    }

    public bool LoginUser(string username, string password)
    {
        using (var connection = new SQLiteConnection(connectionString))
        {
            connection.Open(); string query = "SELECT PasswordHash FROM Users WHERE Username = @Username";
            using (var command = new SQLiteCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Username", username);
                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        string storedHash = reader.GetString(0);
                        return PasswordHelper.VerifyPassword(password, storedHash);
                    }
                    else
                    {    
                        return false;
                    }
                }
            }
        }
    }

    private void Button_Click(object sender, RoutedEventArgs e)
    {
        if (!string.IsNullOrEmpty(UserName_Login) && !string.IsNullOrEmpty(Password_Login))
        { 
            if (LoginUser(UserName_Login, Password_Login))
            {
                (new Window1()).Show();
                this.Close();
                return;
            }    
        }
        MessageBox.Show("login failed");

    }

    private void Button_Click_1(object sender, RoutedEventArgs e)
    {
        if(!string.IsNullOrEmpty(UserName_Register) && !string.IsNullOrEmpty(Password_Register))
        {
            RegisterUser(UserName_Register, Password_Register);
            MessageBox.Show("ok");
        }
    }
}

PasswordHelper.cs

    public class PasswordHelper
    {
        public static string HashPassword(string password) 
        { 
            using (var rng = new RNGCryptoServiceProvider()) 
            { 
                byte[] salt = new byte[16]; 
                rng.GetBytes(salt); 
                var pbkdf2 = new Rfc2898DeriveBytes(password, salt, 10000); 
                byte[] hash = pbkdf2.GetBytes(20); 
                byte[] hashBytes = new byte[36]; 
                Array.Copy(salt, 0, hashBytes, 0, 16); 
                Array.Copy(hash, 0, hashBytes, 16, 20); 
                return Convert.ToBase64String(hashBytes); 
            } 
        }
        public static bool VerifyPassword(string password, string storedHash)
        {
            byte[] hashBytes = Convert.FromBase64String(storedHash); 
            byte[] salt = new byte[16]; 
            Array.Copy(hashBytes, 0, salt, 0, 16); 
            var pbkdf2 = new Rfc2898DeriveBytes(password, salt, 10000); 
            byte[] hash = pbkdf2.GetBytes(20); 
            for (int i = 0; i < 20; i++) 
                if (hashBytes[i + 16] != hash[i]) 
                    return false; return true;
        }
    }

Window1.xaml

Ensure secure database access

1.Use SQLCipher This is an extension for SQLite databases that could encrypt the entire database file. 2.Access control Principle of least privilege: Only authorized users and applications could access the database, limiting unnecessary access.

User authentication and permission management: Configure different user roles and permissions to ensure that only necessary permissions are granted. 3.Secure Coding Practices Parameterized queries: To avoid SQL injection attacks, make sure all queries are parameterized.

using (var command = new SQLiteCommand("SELECT * FROM Users WHERE Username = @Username", connection))
{
    command.Parameters.AddWithValue("@Username", username);
    var reader = command.ExecuteReader();
}

Input Validation: Validate all user input to prevent malicious data from entering the system.

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

How to Implement Secure User Login with SQLite in a C# WPF Project?

2 answers

Your answer