Connecting HTML Login/Registration Form to SQLite Database

HTML Form Integration with SQLite Database for User Authentication

When developing a web application that requires user authentication, one of the fundamental tasks is connecting an HTML login/registration form to a backend database. In this case, the database is SQLite, and the table in question is lit_login_credentials, which stores user information such as username, first name, last name, email, and password. The goal is to ensure that user inputs from the HTML form are correctly saved into the SQLite database and that the application can authenticate users based on the stored credentials. Additionally, upon successful login, the user should be redirected to another page.

The challenge here lies in the fact that HTML alone cannot directly interact with a SQLite database. HTML is a markup language used for structuring content on the web, and it lacks the capability to perform database operations. Therefore, a server-side scripting language or a web application framework is required to bridge the gap between the HTML form and the SQLite database. This involves handling form submissions, processing the data, executing SQL queries, and managing user sessions.

Lack of Server-Side Scripting or Framework for Database Interaction

The core issue stems from the absence of a server-side scripting language or a web application framework that can facilitate the interaction between the HTML form and the SQLite database. HTML forms can collect user input, but they cannot directly communicate with a database. This communication must be handled by a server-side component, which can be written in languages such as Python, PHP, Ruby, or JavaScript (Node.js). Without this intermediary, the data collected from the HTML form cannot be processed, stored, or retrieved from the SQLite database.

Moreover, the SQLite database itself is a serverless database, meaning it does not run as a separate process that can be accessed over a network. Instead, it is embedded within the application, and direct access to the database file is required. This further complicates the integration with a web application, as the web server must have the necessary permissions to read from and write to the SQLite database file.

Implementing Server-Side Scripting with SQLite Integration

To resolve the issue, a server-side scripting language or framework must be employed to handle the interaction between the HTML form and the SQLite database. Below are the detailed steps to achieve this:

Setting Up the Environment

First, ensure that the necessary tools and libraries are installed. For this example, we will use Python with the Flask framework and the sqlite3 module, which is included in Python’s standard library. Flask is a lightweight web framework that is well-suited for small to medium-sized web applications.

1. Install Python: Ensure that Python is installed on your system. You can download it from the official Python website.
2. Install Flask: Use pip, Python’s package manager, to install Flask. Run the following command in your terminal or command prompt:

   pip install Flask
   

3. Create a Project Directory: Create a directory for your project and navigate into it.

   mkdir my_flask_app
   cd my_flask_app
   

4. Initialize a Virtual Environment: It is good practice to use a virtual environment to manage dependencies. Create and activate a virtual environment:

   python -m venv venv
   source venv/bin/activate  # On Windows, use `venv\Scripts\activate`
   

Creating the SQLite Database

Next, create the SQLite database and the lit_login_credentials table. This can be done using a Python script or directly via the SQLite command-line tool.

1. Create the Database: Create a new SQLite database file named users.db in your project directory.

   sqlite3 users.db
   

2. Create the Table: Execute the following SQL command to create the lit_login_credentials table:

   CREATE TABLE lit_login_credentials (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       username TEXT NOT NULL UNIQUE,
       first_name TEXT NOT NULL,
       last_name TEXT NOT NULL,
       email TEXT NOT NULL UNIQUE,
       password TEXT NOT NULL
   );
   

3. Exit SQLite: Exit the SQLite command-line tool by typing .exit.

Developing the Flask Application

Now, develop the Flask application that will handle the HTML form submissions and interact with the SQLite database.

1. Create the Flask Application: Create a new Python file named app.py in your project directory.

   from flask import Flask, render_template, request, redirect, url_for
   import sqlite3
   
   app = Flask(__name__)
   
   # Database connection function
   def get_db_connection():
       conn = sqlite3.connect('users.db')
       conn.row_factory = sqlite3.Row
       return conn
   
   @app.route('/')
   def index():
       return render_template('index.html')
   
   @app.route('/register', methods=['GET', 'POST'])
   def register():
       if request.method == 'POST':
           username = request.form['username']
           first_name = request.form['first_name']
           last_name = request.form['last_name']
           email = request.form['email']
           password = request.form['password']
   
           conn = get_db_connection()
           conn.execute('INSERT INTO lit_login_credentials (username, first_name, last_name, email, password) VALUES (?, ?, ?, ?, ?)',
                        (username, first_name, last_name, email, password))
           conn.commit()
           conn.close()
           return redirect(url_for('login'))
       return render_template('register.html')
   
   @app.route('/login', methods=['GET', 'POST'])
   def login():
       if request.method == 'POST':
           username = request.form['username']
           password = request.form['password']
   
           conn = get_db_connection()
           user = conn.execute('SELECT * FROM lit_login_credentials WHERE username = ? AND password = ?',
                               (username, password)).fetchone()
           conn.close()
   
           if user:
               return redirect(url_for('dashboard'))
           else:
               return 'Invalid username or password'
       return render_template('login.html')
   
   @app.route('/dashboard')
   def dashboard():
       return 'Welcome to the dashboard!'
   
   if __name__ == '__main__':
       app.run(debug=True)
   

2. Create HTML Templates: Create a templates directory in your project directory and add the following HTML files:

   • index.html: The main page with links to the login and registration forms.
   • register.html: The registration form.
   • login.html: The login form.

   index.html:

   <!DOCTYPE html>
   <html lang="en">
   <head>
       <meta charset="UTF-8">
       <meta name="viewport" content="width=device-width, initial-scale=1.0">
       <title>Home</title>
   </head>
   <body>
       <h1>Welcome</h1>
       <a href="{{ url_for('login') }}">Login</a>
       <a href="{{ url_for('register') }}">Register</a>
   </body>
   </html>
   

   register.html:

   <!DOCTYPE html>
   <html lang="en">
   <head>
       <meta charset="UTF-8">
       <meta name="viewport" content="width=device-width, initial-scale=1.0">
       <title>Register</title>
   </head>
   <body>
       <h1>Register</h1>
       <form method="POST">
           <label for="username">Username:</label>
           <input type="text" id="username" name="username" required>
           <br>
           <label for="first_name">First Name:</label>
           <input type="text" id="first_name" name="first_name" required>
           <br>
           <label for="last_name">Last Name:</label>
           <input type="text" id="last_name" name="last_name" required>
           <br>
           <label for="email">Email:</label>
           <input type="email" id="email" name="email" required>
           <br>
           <label for="password">Password:</label>
           <input type="password" id="password" name="password" required>
           <br>
           <button type="submit">Register</button>
       </form>
   </body>
   </html>
   

   login.html:

   <!DOCTYPE html>
   <html lang="en">
   <head>
       <meta charset="UTF-8">
       <meta name="viewport" content="width=device-width, initial-scale=1.0">
       <title>Login</title>
   </head>
   <body>
       <h1>Login</h1>
       <form method="POST">
           <label for="username">Username:</label>
           <input type="text" id="username" name="username" required>
           <br>
           <label for="password">Password:</label>
           <input type="password" id="password" name="password" required>
           <br>
           <button type="submit">Login</button>
       </form>
   </body>
   </html>
   

Running the Application

1. Start the Flask Application: Run the Flask application by executing the following command in your terminal or command prompt:

   python app.py
   

2. Access the Application: Open a web browser and navigate to http://127.0.0.1:5000/. You should see the home page with links to the login and registration forms.
3. Test the Registration and Login: Use the registration form to create a new user account, then log in using the credentials you provided. Upon successful login, you should be redirected to the dashboard.

Security Considerations

While the above implementation works for basic user authentication, it is important to consider security best practices:

1. Password Hashing: Storing plain-text passwords in the database is a significant security risk. Use a library like bcrypt or hashlib to hash passwords before storing them.
2. SQL Injection: Always use parameterized queries to prevent SQL injection attacks. The sqlite3 module in Python supports parameterized queries, as shown in the example.
3. Session Management: Implement proper session management to ensure that users remain authenticated across different pages of the application. Flask provides built-in support for sessions.
4. HTTPS: Use HTTPS to encrypt data transmitted between the client and the server, especially when dealing with sensitive information like passwords.

Conclusion

Connecting an HTML login/registration form to a SQLite database involves several steps, including setting up the environment, creating the database, developing the server-side application, and ensuring security. By following the detailed steps outlined above, you can successfully integrate an HTML form with a SQLite database for user authentication. This approach can be adapted to other server-side languages and frameworks, depending on your specific requirements and preferences.