Question 286097 in Mysql Question posted in
The official MySQL documentation can be found here.

Flask Form Authentication validation with MySQL database

Hi there good programmers. I am simply trying to take input from a register form, check and confirm them by validating them against MySQL-workbench database. NB, I am fairly new to Flask and python.

My expectation:

If the input email exist, route and stay on sign up, else route to login.
But the app just keeps on taking inputs and sending them to database, even if it is the same info.

Thanks in advance
Motchello

Here is my code below

from flask import Flask
from flask import render_template, url_for
from flask import request, redirect
from sqlalchemy import String, Integer, CHAR
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import Session
from flask_sqlalchemy import SQLAlchemy

# import flask login
from flask_login import UserMixin


# create an inheritance class from DeclarativeBase
class Base(DeclarativeBase):
    pass


# instan
db = SQLAlchemy(model_class=Base)

SESSION_TYPE = 'sqlalchemy'
app = Flask(__name__)

# Create a connection to the database using mysql and + pymysql
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost/New_User_Database'
# config secrete key
app.config['SECRET_KEY'] = 'TooTopSecrete'

# initialize database app
db.init_app(app)

session = Session(app)


# Create a Table class using flask_sqlaclhemy
class Users(db.Model, UserMixin):
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    username: Mapped[str] = mapped_column(String(30), unique=True, nullable=False)
    email: Mapped[str] = mapped_column(String(30))
    password: Mapped[str] = mapped_column(CHAR(80))


with app.app_context():
    db.create_all()


# view route page for home
@app.route('/', methods=['GET'])
def home():
    return render_template('login.html')


# view route page for login
@app.route('/login', methods=['GET', 'POST'])
def login():
    return render_template('login.html')


# view route page for sign up
@app.route('/sign_up', methods=['GET', 'POST'])
def sign_up():
    if request.method == 'POST':
        user_email = request.form.get('email', False)
        email_in_database = Users.query.filter_by(email=Users.email).first()
        if user_email == email_in_database:
            return redirect(url_for('sign_up'))
        else:
            new_user = Users(
                username=request.form['username'],
                email=request.form['email'],
                password=request.form['password'],
            )
            db.session.add(new_user)
            db.session.commit()

            return redirect(url_for('login', ))

    return render_template('sign_up.html')


if __name__ == '__main__':
    app.run(debug=True)

Here is my sign_up.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title> Sign up</title>
    <style>

        input[type=email], select {
          width: 100%;
          padding: 12px 20px;
          margin: 8px 0;
          display: inline-block;
          border: 1px solid #ccc;
          border-radius: 4px;
          box-sizing: border-box;
        }

        input[type=password], select {
          width: 100%;
          padding: 12px 20px;
          margin: 8px 0;
          display: inline-block;
          border: 1px solid #ccc;
          border-radius: 4px;
          box-sizing: border-box;
        }

        input[type=text], select {
          width: 100%;
          padding: 12px 20px;
          margin: 8px 0;
          display: inline-block;
          border: 1px solid #ccc;
          border-radius: 4px;
          box-sizing: border-box;
        }

        input[type=submit] {
          width: 100%;
          background-color: #4CAF50;
          color: white;
          padding: 14px 20px;
          margin: 8px 0;
          border: none;
          border-radius: 4px;
          cursor: pointer;
        }

        input[type=submit]:hover {
          background-color: #45a049;
        }

        div {
          border-radius: 5px;
          background-color: #f2f2f2;
          padding: 20px;
        }
</style>
</head>
<body>

    <h1> Sign up </h1>

    <div>

        <form method="post" action="/sign_up" autocomplete="on">
        <input type="text" id="username" name="username" maxlength="40"
               placeholder="username" autofocus required><br><br>

        <input type="email" id="email" name="email" maxlength="40"
               placeholder="email" required><br><br>

        <input type="password" id="password" name="password" maxlength="64" min="6"
               placeholder="password" required><br><br>

        <input type="submit" value="Sign up"><br><br>
    </form>

        <form method="get" action="{{ url_for('login') }}" >

            <p> Already have an account? <a href="{{ url_for('login') }}"> LOGIN  </a></p><br>

        </form>


    </div>


</body>
</html>

And here is my login.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title> login </title>
    <style>

        input[type=email], select {
          width: 100%;
          padding: 12px 20px;
          margin: 8px 0;
          display: inline-block;
          border: 1px solid #ccc;
          border-radius: 4px;
          box-sizing: border-box;
        }

        input[type=password], select {
          width: 100%;
          padding: 12px 20px;
          margin: 8px 0;
          display: inline-block;
          border: 1px solid #ccc;
          border-radius: 4px;
          box-sizing: border-box;
        }

        input[type=submit] {
          width: 100%;
          background-color: #4CAF50;
          color: white;
          padding: 14px 20px;
          margin: 8px 0;
          border: none;
          border-radius: 4px;
          cursor: pointer;
        }

        input[type=submit]:hover {
          background-color: #45a049;
        }

        div {
          border-radius: 5px;
          background-color: #f2f2f2;
          padding: 20px;
        }
</style>
</head>
<body>

    <h1> Login </h1>
    <div>
         <form method="post" action="{{ url_for('login') }}" autocomplete="on">

            <input type="email" id="email" name="email" maxlength="40"
                   placeholder="email" required><br><br>

            <input type="password" id="password" name="password" maxlength="64" min="6"
                   placeholder="password" required><br><br>



            <input type="submit" value=" Login "><br><br>

        </form>

        <form method="get" action="{{ url_for('sign_up') }}" >

            <p> Don't have an account? <a href="{{ url_for('sign_up') }}"> SIGN UP  </a></p><br>

        </form>

    </div>



</body>
</html>

I have gone through some stack-overflow answers, and googled around but no avail. All the much help is appreciated.

Tags:

2

Answers


  1. 0

    My proposed solution, change these lines to:

    user_email = request.form.get('email', False)
user_from_db = Users.query.filter_by(email=user_email).first()
if user_from_db:
    return redirect(url_for('sign_up'))
else:
    # same code you had before

    The reason is you were getting a User from the database and calling it an email. But the Users.query.first() returns the first User with the email you specified or None if it is non-existent.

    Then you compared that User with the email, which is not correct, you were basically saying

    if "[email protected]" == None

    or

    if "[email protected]" == {id:..., email: the actual email, password=""}

    This is why what I think you were trying to achieve can be done with little change, by fetching that user with the email and then saying

    if user_from_db:

    which will be truthy when there is a user, but falsy if it is None (no user with that email in database).

    There are other things I presume are wrong, you said filter_by(email=Users.email) which I’m not sure what does exactly, probably tries filtering by a non-string? It didn’t seem to error so who knows, maybe it’ll work with your old line. But I changed that to filter_by(email=user_email) which is the email from the form. That’s saying "get me user from database with the email from the form".

    Some advice:

    • You really need to learn how to debug things like these yourself, look up debugging in python, there are two main ways, using a debugger, like pdb or placing print commands at key points in your program (or both).

    • You need to know what is going on in your program, like here, you were trying to compare a string and an object which doesn’t make sense, but with some print commands you can see what actually is going on in your program.

    Login or Signup to reply.
  2. 0

    I believe this is causing the bug:

    user_email = request.form.get('email')
email_in_database = Users.query.filter_by(email=Users.email).first()
if user_email == email_in_database:

    Try this instead:

    user_email = request.form.get('email')
email_in_database = Users.query.filter_by(email=user_email).first()
if email_in_database:

    Essentially, we just have to check whether the email exists in the SQL DB. We do not need to check a second time (with the == operator)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search