Introduction
In web applications where user authentication is managed through an SQLite database, there may arise situations where developers or testers need to update or verify user credentials directly from the database. This blog demonstrates a step-by-step approach to modifying the Users.db SQLite file for password updates using DB Browser (SQLite), while providing insights into how different application files handle user authentication, encryption, and JWT generation.
This use case is particularly relevant in development or troubleshooting scenarios where understanding the flow of authentication and encrypted password verification is essential.
Background About the Web application:
The Users.db file is managed through four key components in the application: UserController.cs, CipheContext.cs, EncryptionManager.cs, and AuthServiceManager.cs.
These files collectively handle interactions, operations, and authentication related to the Users.db database file.
- UserController.cs: The Authenticate method receives a request containing user credentials which you have given from Postman. It passes these credentials to an authentication service (_authService) to verify them. If the response from the service is null (indicating invalid credentials), it returns a Bad Request response with an error message stating "Username or password is incorrect”. If the credentials are valid, it responds with a success status OK along with the authentication details.
The PostUserMaster function in this UserController encrypts the user's password received in the request, adds the user's data to the database, and then returns a response confirming the creation of the user.
- AuthServiceManager.cs: The Authenticate method in the AuthServiceManager class is responsible for verifying user credentials and managing the authentication process. It first checks the database for a user with the provided username and verifies if the user is active. If a matching user is found and their password matches the input using BCrypt's verification method, it generates a JWT token via the generateJwtToken function. This token is then used to create an AuthenticateResponse object, which is returned to signify successful authentication. If the user is not found or the password doesn't match, it returns null, indicating authentication failure.
Both generateJwtToken and generateJwtToken1 methods create JWT (JSON Web Token) strings for authentication purposes. They use the JwtSecurityTokenHandler class to generate tokens that are valid for one day (Expires = DateTime.UtcNow.AddDays(1)).
These methods build a token descriptor containing user information or user ID as a claim. They also set the token's expiration, issuance time, and define the signing credentials using a symmetric key obtained from the _appSettings.Secret. Finally, the token is created and serialized into a string using tokenHandler.WriteToken(token), which is then returned for use in the authentication process.
- CipheContext.cs: The path specified in the optionsBuilder.UseSqlite method within the CipheContext class points to the location of the SQLite database file named Users.db. It's set to a specific directory on the local machine, such as "C:\\Users\\MI\\Downloads\\repository - Dev (4)\\repository - Dev\\Users.db". This path is crucial as it determines where the application will look for and interact with the SQLite database file for storing and retrieving data.
The OnConfiguring method in the DbContext class is essential for defining which database provider to use and setting up the appropriate connection string. Here, it's used to define the SQLite database file's location, while OnModelCreating is employed to configure the primary key for the userMaster entity, ensuring proper database structure and key constraints.
- EncryptionManager.cs: The EncryptionManager class is a static utility that offers encryption and decryption functionalities using TripleDES encryption. It obtains an encryption secret from application settings to perform secure encryption and decryption operations on provided strings, enhancing data security within the CipheConnectApp.
The web application validates both the name and plain text password provided in Postman, alongside the username and hashed password stored in the Users.db file. If the web application successfully verifies this information, it means the plain text password entered in Postman matches the hash present in the Users.db file under the password column. Then, it will return the token in the Postman. In case of a mismatch, an error is returned in Postman indicating that the username and password are incorrect. The password stored in the Users.db file is in encrypted or hashed form, derived from the plain text password inputted in Postman.
Password Update Procedure in Users.db:
Screenshots of postman request containing old password:
- First, install the DB browser (SQLite) on your system to open the Users.db file.
- Once the installation is successful, click the "Open Database" option to access the Users.db file.
- Choose the Users.db file you wish to open, then click "OK" to open it. Once opened, it will display all the tables and columns it contains.
- Now, right-click on the "UserMaster" table to view its data. Select "Browse Table," and it will show you all the table data.
Note that the password stored in the database is in an encrypted (hashed) format.
- To generate a hash of your plain text password, you can use a web application or your web browser.
To generate the hash of plain text using your web browser. In web browser, Paste this link: https://bcrypt.online/ . Using this website, you can generate the hash code of any new plain text, or you can verify any hash code with its plain text.
Here's an example of how to do it with a web application:
To get the hash of your plain text. Add the below line in your web application. Through debugging one by one line of code, you can get the hash of this plain text. Copy the hash password.
var hashPassword = BCrypt.Net.BCrypt.HashPassword("Your plain text password");
- After generating the hash, update the plain text password in the Postman request body and paste the hash of that plain text password that you copied into the Users.db file. Your new password will now work.
Screenshots of postman request containing new password:
Conclusion
Directly updating hashed passwords in an SQLite database can be useful for development, testing, or recovery scenarios. However, it's essential to follow encryption practices and ensure consistency between application logic and database values. Understanding the roles of controllers, authentication services, and encryption managers strengthens your ability to debug and manage web authentication systems securely and effectively.