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()
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.
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 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.
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.
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.
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.
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.