python - TypeError: Object of type Cursor is not JSON serializable when comparing request.form.get and db value

Question

I am pograming a simple web app with flask and am trying to implement a login process. My /register seems to work fine with username and hashed pwd being correctly inserted in my database when registering. However, when it comes to the login part I can't seem to be comparing the username stored in MySQL and the one from resquest.form.get, I get the following error message : TypeError: Object of type Cursor is not JSON serializable.

Here is my code:

@app.route('/login', methods=["GET", "POST"])
def login():
    
    db = sqlite3.connect("users.db")
    c = db.cursor()

    if request.method== "POST":
        username = request.form.get("username")
        password = request.form.get("password")
        pwd_hash = sha256(password.encode('utf-8')).hexdigest()

        # Ensure username was submitted
        if not request.form.get("username"):
            flash("must provide username", "error")
            return redirect("/login")

        # Ensure password was submitted
        elif not request.form.get("password"):
            flash("must provide password", "error")
            return redirect("/login")

        # Query database for username
        if (c.execute("SELECT COUNT(*) FROM users WHERE username=:username", {"username" : username}).fetchall()[0][0] == 0):
            flash("invalid username", "error")
            return redirect("/login")
        
        if (c.execute("SELECT password FROM users WHERE(username=:username)", {"username": username}).fetchall()[0][0] != pwd_hash):
            flash("invalid password", "error")
            return redirect("/login")
        
        # Remember which user has logged in
        session["user_id"] = c.execute("SELECT username FROM users WHERE(:username=:username)", {"username": username})

        # Redirect user to home page
        return redirect("/")

    # User reached route via GET (as by clicking a link or via redirect)
    else:
        return render_template("login.html")

Any idea ?

question from:https://stackoverflow.com/questions/65830434/typeerror-object-of-type-cursor-is-not-json-serializable-when-comparing-request

Answer 1

You didn't fetch the row here:

session["user_id"] = c.execute("SELECT username FROM users WHERE(:username=:username)", {"username": username})

c.execute() returns the cursor object, you can't put that into a session variable. It should be

session["user_id"] = c.execute("SELECT username FROM users WHERE(:username=:username)", {"username": username}).fetchone()[0]

But there doesn't seem to be a reason to do another query here. It's just going to return the same username that's in the parameter. So just write

sesion["user_id"] = username

BTW, it's generally considered poor security design to distinguish invalid usernames from invalid passwords. Just do a single query with both the username and password. If it fails, report "Invalid username or password".

        if (c.execute("SELECT COUNT(*) FROM users WHERE username=:username AND password=:password", {"username" : username, "password": pwd_hash}).fetchone()[0] == 0):
            flash("invalid username or password", "error")
            return redirect("/login")

Other issues:

1. You're assigning variables from the request.form parameters before you check that they were actually filled in.
2. If you only need one row, use fetchone() instead of fetchall()[0].