Let's break down the issues in your code that are causing the 500 error and other logic problems, then fix them step by step:

Key Issues in Your Code #

• SQL Parameter Syntax & Passing Error
  In your first SELECT query, you have an extra space after the colon (: username) which is invalid syntax. Also, db.execute expects a dictionary of named parameters, not just passing the username string directly.

• Missing Database Commit
  After inserting the user into the users table, you don't commit the transaction. Without db.commit(), the data won't be saved to the database, leading to failures when you try to fetch the user right after insertion.

• Incorrect Table Name in SELECT Query
  When trying to fetch the newly registered user, you wrote SELECT * FROM rows instead of SELECT * FROM users—this typo will throw a "table not found" error, triggering the 500 status.

• Invalid Template Paths
  render_template doesn't require a leading slash. Using /index or /register will cause Flask to fail locating your template files; use the exact filenames like register.html or index.html instead.

• Password Input Type (Usability Fix)
  Your password fields use type="text" which shows input as plain text. Change this to type="password" for basic security and better user experience (not a 500 error, but an important improvement).

Corrected Code #

register.html (Fixed Password Inputs + Basic Frontend Validation) #

{% extends "layout.html" %}
{% block title %} Register {% endblock %}
{% block main %}
<form action="/register" method="post">
    <input type="email" name="email" placeholder="email" required><br>
    <input type="text" name="username" placeholder="username" required><br>
    <input type="password" name="password" id="pass" placeholder="password" required><br>
    <input type="password" name="confirm" id="confirm" placeholder="confirm" required><br>
    <pre></pre>
    <input type="submit" value="Submit Form">
</form>
{% endblock %}

application.py (Fixed Logic & SQL Errors) #

@app.route("/register", methods=["GET", "POST"])
def register():
    """Register user"""
    if request.method == "POST":
        # Grab all form inputs first for cleaner code
        username = request.form.get("username")
        password = request.form.get("password")
        confirm = request.form.get("confirm")
        email = request.form.get("email")
        
        # Validate empty fields
        if not username:
            return render_template("failuser.html", message="Username cannot be empty")
        if not password or not confirm:
            return render_template("failpass.html", message="Password fields cannot be empty")
        if password != confirm:
            return render_template("failpass.html", message="Passwords do not match")
        if not email:
            return render_template("failuser.html", message="Email cannot be empty")
        
        # Check if username already exists
        rows = db.execute("SELECT * FROM users WHERE username = :username", {"username": username})
        if len(rows) >= 1:
            return render_template("failuser.html", message="Username already taken")
        
        # Hash password and insert user into database
        hashed_password = generate_password_hash(password)
        db.execute(
            "INSERT INTO users(username, hash, email) VALUES (:username, :hash, :email)",
            {"username": username, "hash": hashed_password, "email": email}
        )
        # Commit the transaction to save changes to the database
        db.commit()
        
        # Fetch the newly created user to set up session
        user = db.execute("SELECT * FROM users WHERE username = :username", {"username": username}).fetchone()
        if user:
            session["user_id"] = user["id"]
            return render_template("index.html")
        else:
            # Handle edge case where user wasn't saved properly
            return render_template("failuser.html", message="Failed to create account")
    else:
        return render_template("register.html")

Additional Notes #

• Added required attributes to form inputs to add basic frontend validation (always keep backend validation too, since frontend checks can be bypassed).
• Added specific error messages to make it clearer for users what went wrong.
• Used fetchone() to get a single user record instead of checking row length, which is more efficient.
• Ensured all SQL parameters are passed as a dictionary to avoid SQL injection risks and syntax errors.

内容的提问来源于stack exchange，提问作者aisha's SO