Flask, User Dashboard displaying other users info?

I’m having this issue that all the users who logged in can see all the articles in the dashboard for example if user 1 wrote an article every other user that login and enter the dashboard will see user 1 article, and I don’t want that I want each user sees only their articles and no one else on the dashboard (the articles are being retrieved from the articles table, I have two tables users table and articles table). I’m using flask

# Dashboard
@app.route('/dashboard')
@is_logged_in
def dashboard():
# Create Cursor
cur = mysql.connection.cursor()

# Get articles
result = cur.execute("SELECT * FROM articles")
articles = cur.fetchall()

if result > 0:
    return render_template('dashboard.html', articles=articles)
else:
    msg = 'No Articles Found'
    return render_template('dashboard.html', msg=msg)
# Close Connection for displaying articles
cur.close()

As you can see in this image (click here) user 3 can see other users articles on his dashboard

This is why:

result = cur.execute("SELECT * FROM articles")

You need to change the results to something that is related to the user, like so.

Ex.

result = cur.execute("SELECT * FROM articles WHERE USER=" + user) 

Essentially, what you need to do is return records which match the user that is logged in. This will require you to change your table to include a record of who made the article.

Thank you for your response

i’m still having an issue I couldn’t manage to do it

let me give you the tables that I have

users table
userstable

articles table
atricles table

these are the tables as you can see the username column in the users table is matching the author column in the articles table

how I’m going to display only each user data from the articles I want each user to display only his articles that matches his username

Thanks

Ahh I see the problem.

You need to add a foreign key to the users table primary key.

In your article table you store their username, but if your users id is the primary key then you need to link to that instead as a foreign key so the table knows where to pull the needed info into scope.

I’m sorry but how i’m going to link both tables with the users id??

can you please provide me with the code? I couldn’t manage to do it

I’m a newbie to databases and MySQL

Thanks

I think you could use the Flask-SQLAlchemy plug in. It makes dealing with databases a bit easier.

Miguel Grinberg has a really good book on Flask. This is the GitHub for the code and has a section on building exactly what you are looking for and explains the keys and displaying the right info from the database.

Hope that helps.

In essence, something like this:

CREATE TABLE users (
   user_id int not null auto_increment primary key,
   user_fname varchar(255) not null,
   user_lname varchar(255) not null,
) ENGINE=InnoDB;
 
CREATE TABLE articles (
   art_id int not null auto_increment primary key,
   art_title varchar(255) not null,
   art_body text not null,
   art_author int not null,
   FOREIGN KEY fk_user(art_author) REFERENCES users(user_id)
   ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

And then well you pull data from the database with your queries:
SELECT * FROM articles WHERE fk_user=$USER

$USER is a parameter that you pass in your flask function.

when I execute the following MySQL command I get this error

#1215 - Cannot add foreign key constraint

I have searched online and they say the data types must match to create the foreign key, so I thought it’s because a VARCHAR and a number this is what causing the error so I changed it to FOREIGN KEY fk_user(art_author) REFERENCES users(username)
ON UPDATE CASCADE ON DELETE CASCADE

instead of user_id, I have replaced it with username so the Data type match, but even I still get the same error.

Thanks.

The issue with just using the user name is that you can’t pull info about the users table without doing a left join. It is better practice to use the users id. Because, if you do plan to use user name then you need to make sure no other user has the same user name, so you need to make sure that the user name field has a unique constraint on it.

CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)

hmm i’m trying to solve this now :slight_smile:

It took me 5 days and I’m still stuck

Post your entire database schema.

so I made some changes and I think the database is passed and ok

This is the users table

  CREATE TABLE users(
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),email VARCHAR(100),
  username VARCHAR(30) NOT NULL UNIQUE,
  password VARCHAR(100),
  register_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

This is the articles table

CREATE TABLE articles(
id INT(11) AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
author VARCHAR(30),
body TEXT,
   CONSTRAINT articles_users_fk FOREIGN KEY (author) REFERENCES users (username),
CREATE_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

both tables are ready in phpmyadmin

now how would I implement the code so I can only display the logged in user articles, not all the articles

This is the Dashboard code

# Dashboard
@app.route('/dashboard')
@is_logged_in
def dashboard():

# Create Cursor
cur = mysql.connection.cursor()

# Get articles
result = cur.execute("SELECT * FROM articles")
articles = cur.fetchall()

if result > 0:
    return render_template('dashboard.html', articles=articles)
else:
    msg = 'No Articles Found'
    return render_template('dashboard.html', msg=msg)
# Close Connection for displaying articles
cur.close()

what to do next?

Browsing on a tablet,
haven’t tried the code, but…

Remember Johnny Tables;

Use escape_string from the connection object instead of just %s or .format or +

Also, pick the columns you need in your select instead of * , it makes your code more readable and it makes it easier to add/remove/reorder columns later.

I just ordered the book for mysql and python :slight_smile: can’t wait

so now what I’m going to do?

I couldn’t understand …USER=" + user…

Thanks

Imagine you’re using that select to pass a user from a login form and your code looks like:

c.execute('SELECT foo FROM User WHERE username = "%s"' % username)

Someone could enter "; DROP TABLE LIKE "%"; and the server would happily delete all the tables, maybe, depending on the privileges.

Better thing to do is
c.execute('SELECT foo FROM User WHERE username = "%s"', (username,)) which is the same-ish as

c.execute('SELECT foo FROM User WHERE username = "%s"' % c.escape_string(username)).


Same thing when passing things over to templating, if you have a list of users somewhere, you don’t want to allow someone who creates a username like <script>append_to_inner_html_an_image_with_user_cookie_in_url</script> to steal user cookies by running a server that logs requests that your users browsers will make. You should escape the string to be safe to render in the HTML context of the template.

Beware that you’re mixing and matching user input with your code all the time, and you’re serving other users what someone on the internet might have typed on your website. If you’re going to be building web stuff make sure you’re escaping stuff correctly depending on the context where you’re using the strings… It’s very very easy to get things very wrong.

Happy to communicate via forums,

… and I apologize for derailing the thread