def test_get_close_db(app):
with app.app_context():
db = get_db()
assert db is get_db()
with pytest.raises(sqlite3.ProgrammingError) as e:
db.execute('SELECT 1')
assert 'closed' in str(e.value)
def selectPassword(id):
db = get_db()
cur = db.cursor()
user_id = session.get('user_id')
categories = categoriesfunc()
categoriesforms = categoriesform()
passwords = passwordfunc()
hashedurl = str(id)
cur.execute(
'SELECT password FROM passwordinfo WHERE userid= ? AND '
'passwordIDEncrypted = ?', (user_id, hashedurl))
passwordtry = cur.fetchone()
passwordact = passwordtry["password"]
passworddec2 = CIPHER_SUITE.decrypt(passwordact)
passworddec2 = str(passworddec2.decode())
passwordchosen = db.execute(
"SELECT u.id, info.userid, info.titlename, info.username, info.lastmodified, "
"info.passwordIDEncrypted, info.website, info.password,strftime('%d/%m/%Y', info.created_timestamp) "
"as created_timestamp, "
"strftime('%d/%m/%Y', info.lastmodified) as lastmodified_date, cP.id, cP.categoryName "
"FROM passwordinfo info JOIN user u on info.userid = u.id"
" JOIN category cP on info.category_id = cP.id WHERE u.id = ? "
"AND info.passwordIDEncrypted = ?", (user_id, hashedurl))
return render_template('manager/selectpassword.html',
passwords=passwords,
categories=categories,
categoryforms=categoriesforms,
passwordchosen=passwordchosen,
passworddec2=passworddec2,
edit=True)
def categoriesfunc():
db = get_db()
user_id = session.get('user_id')
categories = db.execute(
"SELECT categoryName, id FROM category WHERE userID = ?", (user_id, ))
return categories
def newpassword():
if request.method == 'POST':
error = None
user_id = session.get('user_id')
formtitlename = request.form['titlename']
formwebsite = request.form['website']
formusername = request.form['username']
formpassword = request.form['password']
formpasswordbytes = bytes(formpassword, 'utf-8')
categoryform = request.form['category']
uniqueid = uuid.uuid4().hex
if formtitlename == '':
formtitlename = formwebsite
if formtitlename == '' or formwebsite == '':
error = "cannot submit empty passwords into database please type again!"
flash(error)
db = get_db()
if error is None:
db.execute(
'INSERT INTO passwordinfo (passwordIDEncrypted,website, username, titlename,'
' password, category_id, userid) '
'VALUES (?, ?, ?, ?, ?, ?,?)',
(str(uniqueid), formwebsite, formusername, formtitlename,
CIPHER_SUITE.encrypt(formpasswordbytes), categoryform, user_id))
db.commit()
return redirect(url_for('manager.index'))
def test_create(client, auth, app):
auth.login()
assert client.get('/create').status_code == 200
client.post('/create', data={'title': 'created', 'body': ''})
with app.app_context():
db = get_db()
count = db.execute('SELECT COUNT(id) FROM post').fetchone()[0]
assert count == 2
def test_update(client, auth, app):
auth.login()
assert client.get('/1/update').status_code == 200
client.post('/1/update', data={'title': 'updated', 'body': ''})
with app.app_context():
db = get_db()
post = db.execute('SELECT * FROM post WHERE id = 1').fetchone()
assert post['title'] == 'updated'
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute(
'SELECT * FROM user WHERE id = ?', (user_id,)
).fetchone()
def newcategory():
db = get_db()
user_id = session.get('user_id')
if request.method == 'POST':
formcategoryname = request.form['categoryname']
db.execute("INSERT INTO category (categoryName, userID) VALUES (?, ?)",
(formcategoryname, user_id))
db.commit()
return redirect(url_for('manager.index'))
def passwordfunc():
db = get_db()
user_id = session.get('user_id')
passwords = db.execute(
'SELECT info.userid,info.id, info.passwordIDEncrypted, info.titlename, info.username, info.passwordIDEncrypted'
' FROM passwordinfo info '
'JOIN user u on info.userid = u.id WHERE u.id = ?', (user_id, ))
return passwords
def updatecategory():
db = get_db()
user_id = session.get('user_id')
categoryid = request.form['categoryid']
categoryname = request.form['categoryname']
if request.method == 'POST':
db.execute(
"UPDATE category SET categoryName = ? WHERE userid = ? AND id = ?",
(categoryname, user_id, categoryid))
db.commit()
return redirect(url_for('manager.index'))
def deletepassword(id):
db = get_db()
user_id = session.get('user_id')
hashedurl = str(id)
if request.method == 'POST':
db.execute(
"DELETE FROM passwordinfo WHERE userid = ? AND passwordIDEncrypted = ?",
(user_id, hashedurl))
db.commit()
return redirect(url_for('manager.index'))
def get_post(id, check_author=True):
post = get_db().execute(
'SELECT p.id, title, body, created, author_id, username'
' FROM post p JOIN user u ON p.author_id = u.id'
' WHERE p.id = ?', (id, )).fetchone()
if post is None:
abort(404, "Post id {0} doesn't exist.".format(id))
if check_author and post['author_id'] != g.user['id']:
abort(403)
return post
def test_author_required(app, client, auth):
# change the post author to another user
with app.app_context():
db = get_db()
db.execute('UPDATE post SET author_id = 2 WHERE id = 1')
db.commit()
auth.login()
# current user can't modify other user's post
assert client.post('/1/update').status_code == 403
assert client.post('/1/delete').status_code == 403
# current user doesn't see edit link
assert b'href="/1/update"' not in client.get('/').data
def desc():
db = get_db()
user_id = session.get('user_id')
categories = categoriesfunc()
categoriesforms = categoriesform()
passwords = db.execute(
'SELECT info.userid,info.id, info.titlename, info.username, info.passwordIDEncrypted FROM passwordinfo info '
'JOIN user u on info.userid = u.id '
' WHERE u.id = ? ORDER BY titlename desc ', (user_id, ))
return render_template('manager/selectpassword.html',
passwords=passwords,
categories=categories,
categoryforms=categoriesforms,
edit=False)
def deletecategory():
db = get_db()
user_id = session.get('user_id')
categoryid = request.form['categoryid']
if request.method == 'POST':
db.execute(
" UPDATE passwordinfo SET category_id = '1' WHERE category_id = ? AND userid = ?",
(categoryid, user_id))
db.commit()
db.execute("DELETE FROM category WHERE userid = ? AND id = ? ",
(user_id, categoryid))
db.commit()
return redirect(url_for('manager.index'))
def register():
if request.method == 'POST':
fName = request.form['fName']
userEmail = request.form['userEmail']
userConfirm = request.form['userConfirm']
secureKey = request.form['secureKey']
password = request.form['password']
passwordConfirm = request.form['passwordConfirm']
db = get_db()
error = None
if not fName:
error = 'Full name is required'
elif not userEmail:
error = 'Email Address is required'
elif not userConfirm:
error = 'Confirm Email Address is required'
elif userEmail != userConfirm:
error = 'Your emails do not match'
elif not secureKey:
error = 'A secure Key is required'
elif not password:
error = 'Password is required'
elif len(password) <= 8:
error = 'Your password must be greater than 8 characters'
elif not re.fullmatch('^(?=.*?[A-Z])(?=.*?[a-z])(?=.*?[0-9])(?=.*?[#?!@$%^&*-]).{8,}$', password): # nopep8
error = "Your password must" "\u2022 be between 8-30 charcters" \
"\u2022 contain at least 1 digit" \
" \u2022 at least 1 special character !@#$%^&*\u2022" \
" a minimum of a 1 uppercase character and 1 lowercase character"
elif not passwordConfirm:
error = 'Please confirm your Password'
elif password != passwordConfirm:
error = 'Your passwords do not match'
elif db.execute(
'SELECT id FROM user WHERE userEmail = ?', (userEmail,)
).fetchone() is not None:
error = 'User {} is already registered.'.format(userEmail)
if error is None:
db.execute(
'INSERT INTO user (fName, userEmail, secureKey, password) VALUES (?, ?, ?, ?)',
(fName, userEmail, generate_password_hash(secureKey), generate_password_hash(password))
)
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/register.html')
def category(id):
db = get_db()
user_id = session.get('user_id')
categories = categoriesfunc()
categoriesforms = categoriesform()
passwords = db.execute(
'SELECT u.id, info.userid, info.titlename, info.username, info.lastmodified, cP.id, info.passwordIDEncrypted '
'FROM passwordinfo info JOIN user u on info.userid = u.id'
' JOIN category cP on info.category_id = cP.id WHERE u.id = ? '
'AND cP.id = ? '
'ORDER BY titlename asc', (user_id, id))
return render_template('manager/selectpassword.html',
passwords=passwords,
categories=categories,
categoryforms=categoriesforms,
edit=False)
def create():
if request.method == 'POST':
title = request.form['title']
body = request.form['body']
error = None
if not title:
error = 'Title is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'INSERT INTO post (title, body, author_id)'
' VALUES (?, ?, ?)', (title, body, g.user['id']))
db.commit()
return redirect(url_for('index'))
return render_template('create.html')
def update(id):
post = get_post(id)
if request.method == 'POST':
title = request.form['title']
body = request.form['body']
error = None
if not title:
error = 'Title is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute('UPDATE post SET title = ?, body = ?'
' WHERE id = ?', (title, body, id))
db.commit()
return redirect(url_for('blog.index'))
return render_template('update.html', post=post)
def savepassword(id):
db = get_db()
user_id = session.get('user_id')
hashedurl = str(id)
if request.method == 'POST':
savetitlename = request.form['titlename']
savewebsite = request.form['website']
saveusername = request.form['username']
savepassword = request.form['password']
savepasswordbytes = bytes(savepassword, 'utf-8')
savecategory = request.form['category']
db.execute(
"UPDATE passwordinfo SET titlename = ?, website = ?, username = ?, password = ?, category_id = ?,"
" lastmodified = CURRENT_TIMESTAMP WHERE userid = ? AND passwordIDEncrypted = ?",
(savetitlename, savewebsite, saveusername,
CIPHER_SUITE.encrypt(savepasswordbytes), int(savecategory),
user_id, hashedurl))
db.commit()
return redirect(url_for('manager.index'))
def search():
user_id = session.get('user_id')
categories = categoriesfunc()
categoriesforms = categoriesform()
if request.method == 'POST':
searchreq = '%' + request.form['search'] + '%'
if searchreq == '%%':
return redirect(url_for('manager.index'))
else:
db = get_db()
passwords = db.execute(
" SELECT info.userid,info.id, info.titlename, info.username, info.lastmodified, "
"info.passwordIDEncrypted FROM passwordinfo info "
" JOIN user u on info.userid = u.id "
" WHERE u.id = ? AND info.titlename LIKE ? OR info.website LIKE ? OR info.username LIKE ?",
(user_id, searchreq, searchreq, searchreq))
return render_template('manager/selectpassword.html',
passwords=passwords,
categories=categories,
categoryforms=categoriesforms,
edit=False)
def login():
if request.method == 'POST':
userEmail = request.form['userEmail']
secureKey = request.form['secureKey']
password = request.form['password']
db = get_db()
error = None
user = db.execute(
'SELECT * FROM user WHERE userEmail = ?', (userEmail,)
).fetchone()
if user is None or not check_password_hash(user['secureKey'], secureKey) or not check_password_hash(
user['password'], password):
error = "Your Email, Secure Key or Password is wrong. Please try again"
if error is None:
session.clear()
session['user_id'] = user['id']
session.permanent = True
return redirect(url_for('home'))
flash(error)
return render_template('auth/login.html')
def delete(id):
get_post(id)
db = get_db()
db.execute('DELETE FROM post WHERE id = ?', (id, ))
db.commit()
return redirect(url_for('blog.index'))
def index():
db = get_db()
posts = db.execute('SELECT p.id, title, body, created, author_id, username'
' FROM post p JOIN user u ON p.author_id = u.id'
' ORDER BY created DESC').fetchall()
return render_template('index.html', posts=posts)
