async def get(request: Request, *, session: Session):
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT notify_when, notify_by, notify_all, telegram_user_id "
"FROM users WHERE id = %s LIMIT 1", (session.user_id, ))
notification_settings = await cur.fetchone()
if not notification_settings["notify_all"]:
await cur.execute(
"SELECT herbs.id, herbs.latin_name, herbs.english_name, herbs.botanic_name "
"FROM notify_herbs JOIN herbs ON notify_herbs.herb_id = herbs.id "
"WHERE user_id = %s", (session.user_id, ))
herbs = await cur.fetchall()
else:
herbs = True
return web.json_response({
"when": [
x.name for x in [
NotificationWhen(notification_settings["notify_when"]) & y
for y in NotificationWhen
] if x != NotificationWhen.NONE
],
"by": [
x.name for x in [
NotificationBy(notification_settings["notify_by"]) & y
for y in NotificationBy if y != NotificationBy.NONE
] if x != NotificationWhen.NONE
],
"telegram_linked":
notification_settings["telegram_user_id"] is not None,
"herbs":
herbs
})
async def post(request: Request, *, params):
token = request.match_info["token"].strip()
logging.debug("Resetting password for recovery token {}".format(token))
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
# Recupera utente da token
await cur.execute(
"SELECT users.id FROM users JOIN password_reset_tokens ON users.id = password_reset_tokens.user_id "
"WHERE token = %s AND privileges & 2 > 0 AND `expire` > UNIX_TIMESTAMP() LIMIT 1",
(token, ))
db_user = await cur.fetchone()
if not db_user:
raise NotFoundError("Token non valido o scaduto.")
# Cripta nuova password
bcrypted_password = bcrypt.hashpw(params["password"],
bcrypt.gensalt())
# Aggiorna db
await cur.execute(
"UPDATE users SET password = %s WHERE id = %s LIMIT 1",
(bcrypted_password, db_user["id"]))
await cur.execute(
"DELETE FROM password_reset_tokens WHERE token = %s LIMIT 1",
(token, ))
await conn.commit()
# Ok!
return web.json_response({"message": "ok"})
async def post(request: Request, *, params, session: Session):
updates = ", ".join([
"notify_{name} = %({name})s".format(name=k)
for k in ("when", "by") if k in params
] + ["notify_all = %(notify_all)s"] if "herbs" in params else [])
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"UPDATE users SET {} WHERE id = %(user_id)s LIMIT 1".format(
updates), {
"when":
sum([NotificationWhen[k] for k in params["when"]]),
"by":
sum([NotificationBy[k] for k in params["by"]]),
"user_id":
session.user_id,
"notify_all":
"herbs" in params and type(params["herbs"]) is bool
})
if "herbs" in params:
await cur.execute(
"DELETE FROM notify_herbs WHERE user_id = %s",
(session.user_id, ))
if type(params["herbs"]) is list:
for herb_id in params["herbs"]:
await cur.execute(
"INSERT IGNORE INTO notify_herbs (user_id, herb_id) VALUES (%s, %s)",
(session.user_id, herb_id))
await conn.commit()
return web.json_response({"message": "ok"})
async def load_from_api_key(cls,
key: str,
already_hashed: bool = False) -> ApiKeySession:
"""
Ritorna un oggetto `ApiKeySession` in base al valore di `key`
:param key: api key. In chiaro o hash della key.
:param already_hashed: se `True`, `key` è l'hash SHA512 della api key, altrimenti è in chiaro.
:return: oggetto `ApiKeySession` che rappresenta la sessione dell'utente
:raise: `SessionError` se la api key non è presente nel database
"""
cls.logger.debug("Loading session for api key {}".format(key))
if not already_hashed:
hashed_key = general.sha512(key)
else:
hashed_key = key
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT user_id FROM api_keys WHERE key_hash = %s LIMIT 1",
(hashed_key, ))
key_record = await cur.fetchone()
if key_record is None:
raise SessionError("Invalid api key")
return ApiKeySession(key_record["user_id"], hashed_key)
async def delete(request: Request, *, session: Session):
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"UPDATE users SET telegram_user_id = NULL WHERE id = %s LIMIT 1",
(session.user_id, ))
await conn.commit()
return web.json_response({"message": "ok"})
async def get(request: Request, *, session: Session):
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT id, name FROM api_keys WHERE user_id = %s",
(session.user_id, ))
results = await cur.fetchall()
return web.json_response(results)
async def get(request: Request):
token = request.match_info["token"].strip()
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT id FROM password_reset_tokens WHERE token = %s AND `expire` > UNIX_TIMESTAMP() LIMIT 1",
(token, ))
if await cur.fetchone():
return web.json_response({"message": "ok"})
raise NotFoundError("Token invalido o scaduto.")
async def get(request: Request, *, session: Session):
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT id, name, surname, privileges, email FROM users WHERE id = %s LIMIT 1", (session.user_id,)
)
db_user = await cur.fetchone()
db_user["gravatar_hash"] = gravatar.get_hash(db_user["email"])
del db_user["email"]
return web.json_response(db_user)
async def wrapper(*args, **kwargs) -> Response:
resp = web.json_response({}, status=200)
try:
resp = await f(*args, **kwargs)
except api.NotFoundError as e:
resp = json_error_response(
404, readable_exception(e, "Resource not found"))
except api.InternalServerError as e:
resp = json_error_response(
500, readable_exception(e, "Internal server error"))
except api.ForbiddenError as e:
resp = json_error_response(
403, readable_exception(e, "Access forbidden"))
# except api.MissingArgumentsError as e:
# resp = json_error_response(400, readable_exception(e, "Missing some required arguments"))
except api.InvalidArgumentsError as e:
resp = json_error_response(
400, readable_exception(e, "Some arguments are not valid"))
except api.ConflictError as e:
resp = json_error_response(
409, readable_exception(e, "Resource conflict"))
except api.NotAcceptableError as e:
resp = json_error_response(406,
readable_exception(e, "Not acceptable"))
except api.Created as e:
resp = json_error_response(201, readable_exception(e, "Created"))
except api.NotAuthenticatedError:
resp = json_error_response(401, "Not authenticated")
except (api.ForceLogoutError, sessions.SessionError) as e:
resp = json_error_response(200,
readable_exception(e, "Disconnected"))
resp.del_cookie("session")
except asyncio.CancelledError:
resp = json_error_response(400, "Request was interrupted")
except Exception:
# Errore non gestito
# Stampa lo stacktrace in console
stack_trace = str(traceback.format_exc())
print(stack_trace)
# Se l'api è in debug mode, invia lo stacktrace al client
# altrimenti mostra un messaggio generico
if EmaNews().debug: # pragma: nocover
msg = readable_exception(traceback.format_exc(),
"Unhandled internal server error")
else:
msg = "Internal server error."
resp = json_error_response(500, msg)
# Report a sentry (se abilitato)
raven.on_exception()
finally:
# Ritorna la risposta al client
return resp
async def delete(request: Request, *, session: Session):
id_ = request.match_info["id_"].strip()
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"DELETE FROM api_keys WHERE id = %s AND user_id = %s LIMIT 1",
(
id_,
session.user_id,
))
await conn.commit()
return web.json_response({"message": "ok"})
async def wrapper(request: web.Request, *args, **kwargs) -> Response:
# Prendi cookie di sessione. None se non presente.
session_token_cookie = request.cookies.get("session")
# Prendi api key da header `X-EmaNews-Token` o parametro GET `apikey`.
# None se non presente.
api_key = next(
(x for x in (request.headers["X-EmaNews-Token"].strip(
) if "X-EmaNews-Token" in request.headers else None,
request.query["apikey"].strip() if "apikey" in
request.query else None) if x is not None and x),
None)
if session_token_cookie is not None:
# Cookie di sessione presente, prova a caricare la sessione da redis
try:
session = await sessions.SessionFactory.load_from_redis(
session_token_cookie)
except sessions.SessionError as e: # pragma: nocover
# Errore durante il caricamento della sessione, cancellala
await sessions.SessionFactory.delete_from_redis(
session_token_cookie)
# Rilancia l'eccezione per inviare l'errore al client
raise api.ForceLogoutError(e)
elif api_key is not None:
# Api key presente in header/querystring
try:
# Carica sessione
session = await sessions.SessionFactory.load_from_api_key(
api_key)
except sessions.SessionError as e:
# API key non valida
raise api.ForbiddenError(e)
else:
# Cookie sessione e api key mancante!
raise api.NotAuthenticatedError()
# Controllo privilegi utente in base alla sessione caricata
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT privileges FROM users WHERE id = %s LIMIT 1",
(session.user_id, ))
privs = await cur.fetchone()
if not privs: # pragma: nocover
raise sessions.SessionError("User not found")
if not (privs["privileges"]
& required_privileges): # pragma: nocover
raise api.ForbiddenError("Insufficient privileges")
# Sessione creata e privilegi corretti, chiama l'api handler
return await f(request, *args, session=session, **kwargs)
async def handle(request: Request, *, params):
ok_response = web.json_response({"message": "ok"})
logging.debug("Requested password reset for {}".format(params["email"]))
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT * FROM users WHERE email = %s AND privileges & 2 > 0 LIMIT 1",
(params["email"], ))
db_user = await cur.fetchone()
if not db_user:
logging.debug("Failed silently")
return ok_response
# Cancella token scaduti per questo utente
await cur.execute(
"DELETE FROM password_reset_tokens WHERE `expire` <= UNIX_TIMESTAMP() AND user_id = %s",
(db_user["id"]))
# Recupera eventuale token esistente
await cur.execute(
"SELECT token FROM password_reset_tokens WHERE user_id = %s AND `expire` > UNIX_TIMESTAMP() LIMIT 1",
(db_user["id"], ))
db_token = await cur.fetchone()
if not db_token:
# Nessun token, generane uno
logging.debug("Generating new token")
token = general.random_string_secure(64)
await cur.execute(
"INSERT INTO password_reset_tokens (user_id, token, `expire`) "
"VALUES (%s, %s, UNIX_TIMESTAMP() + 3600)",
(db_user["id"], token))
await conn.commit()
else:
# Token esistente
logging.debug("Using pre-existing token")
token = db_token["token"]
# Invia email
await EmaNews().mailgun_client.send(
to=params["email"],
subject="Recupero password EmaNews",
html=
"Clicca sul seguente link per reimpostare la tua password del tuo account EmaNews: "
"<a href='{}/password_reset/{}'>Reimposta password</a>.<br>"
"Il link scade tra 60 minuti. Se non sei stato tu a richiedere il reset "
"della tua password, ignora questa email.".format(
Config()["WEB_BASE_URL"].rstrip("/"), token))
return ok_response
async def post(request: Request, *, session: Session, params):
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
found = False
while not found: # pragma: nocover
key = general.random_string_secure(32)
key_hash = general.sha512(key)
await cur.execute(
"SELECT id FROM api_keys WHERE key_hash = %s LIMIT 1",
(key_hash, ))
found = not await cur.fetchone()
await cur.execute(
"INSERT INTO api_keys (name, key_hash, user_id) VALUES (%s, %s, %s)",
(params["name"], key_hash, session.user_id))
await conn.commit()
return web.json_response({"message": "ok", "key": key})
async def post(request: Request, *, params):
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
# Controlla se l'indirizzo email è stato già usato
await cur.execute("SELECT id FROM users WHERE email = %s LIMIT 1", (params["email"],))
other_user = await cur.fetchone()
if other_user:
# Già usato
raise ConflictError("Esiste già un altro utente registrato con questo indirizzo email.")
# Ok, hash password con bcrypt
bcrypted_password = bcrypt.hashpw(params["password"], bcrypt.gensalt())
# Insert in db
await cur.execute(
"INSERT INTO users (name, surname, email, password, privileges) "
"VALUES (%s, %s, %s, %s, %s)",
(
params["name"],
params["surname"],
params["email"],
bcrypted_password.decode(),
int(Privileges.PENDING_ACTIVATION)
)
)
# Genera e salva token attivazione account
token = general.random_string_secure(64)
await cur.execute("INSERT INTO activation_tokens (user_id, token) VALUES (%s, %s)",
(cur.lastrowid, token))
await conn.commit()
# Invio email
await EmaNews().mailgun_client.send(
to=params["email"],
subject="Conferma il tuo account EmaNews",
html="Clicca sul seguente link per attivare il tuo account EmaNews: "
"<a href='{}/activate/{}'>Attiva account</a>".format(
Config()["WEB_BASE_URL"].rstrip("/"), token
)
)
# Ok!
return web.json_response({
"message": "ok"
})
async def get(request: Request, *, session: Session):
token = ""
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT telegram_user_id FROM users WHERE id = %s LIMIT 1",
(session.user_id, ))
telegram_user_id = await cur.fetchone()
if telegram_user_id["telegram_user_id"] is not None:
raise NotAcceptableError("Account già collegato con Telegram")
await cur.execute(
"DELETE FROM telegram_link_tokens WHERE user_id = %s AND `expire` < UNIX_TIMESTAMP()",
(session.user_id, ))
await conn.commit()
await cur.execute(
"SELECT token FROM telegram_link_tokens WHERE user_id = %s AND `expire` > UNIX_TIMESTAMP() LIMIT 1",
(session.user_id, ))
token = await cur.fetchone()
if token is None:
found = False
while not found: # pragma: nocover
token = general.random_string_secure(16)
await cur.execute(
"SELECT id FROM telegram_link_tokens WHERE token = %s LIMIT 1",
(token, ))
found = not await cur.fetchone()
await cur.execute(
"INSERT INTO telegram_link_tokens (token, user_id, `expire`) VALUES (%s, %s, %s)",
(token, session.user_id, int(time.time()) + 3600))
else:
token = token["token"]
await conn.commit()
# Ok!
bot_username = (await EmaNews().bot.get_me())["username"]
return web.json_response({
"telegram_link":
"https://telegram.me/{}?start={}".format(bot_username, token)
})
async def handle(request: Request):
token = request.match_info["token"].strip()
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT id, user_id FROM activation_tokens WHERE token = %s LIMIT 1",
(token, ))
db_token = await cur.fetchone()
if not db_token:
raise NotFoundError("Token di attivazione non valido")
await cur.execute(
"DELETE FROM activation_tokens WHERE id = %s LIMIT 1",
(db_token["id"], ))
await cur.execute(
"UPDATE users SET privileges = %s WHERE id = %s LIMIT 1",
(int(Privileges.NORMAL), db_token["user_id"]))
await conn.commit()
# Ok!
return web.json_response({"message": "ok"})
async def handle(request: Request, *, params):
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT * FROM users WHERE email = %s LIMIT 1",
(params["email"], ))
db_user = await cur.fetchone()
if not db_user:
raise NotFoundError(
"L'indirizzo email inserito non è associato a nessun account"
)
if not bcrypt.checkpw(params["password"], db_user["password"]):
raise ForbiddenError("La password inserita è errata")
if db_user["privileges"] & Privileges.PENDING_ACTIVATION:
raise ForbiddenError(
"L'account non è ancora stato attivato. Per favore, controlla la tua casella "
"di posta elettronica e clicca sul link che hai ricevuto per verificare "
"il tuo account.")
session = await SessionFactory.new_redis_session(db_user["id"])
resp = web.json_response({"message": "ok"})
resp.set_cookie("session",
session.token,
max_age=RedisSession.SESSION_EXPIRE_TIME) # , secure=True)
return resp
async def handle(request: Request, *, session: Session, params):
herbs = []
# Condizioni WHERE
filters: List[str] = []
if params["query"]:
# WHERE per query ricerca
params["query"] = "%{}%".format(params["query"])
filters.append("({})".format(" OR ".join([
"{} LIKE %(query)s".format(x)
for x in ('latin_name', 'botanic_name', 'english_name')
])))
# Join AND delle condizioni del WHERE
filters_string = " AND ".join(filters)
# Clausola LIMIT
if not params["limit"]:
# No LIMIT
limit = ""
elif params["limit"] and not params["page"]:
# LIMIT senza pagina
limit = "LIMIT {}".format(params["limit"])
else:
# LIMIT con pagina
limit = "LIMIT {}, {}".format(params["page"] * params["limit"],
params["limit"])
# Costruisci due query identiche (results_query e count_query)
# per prendere i risultati che rispecchiano gli argomenti forniti
# e i risultati totali (query necessaria poichè è possibile implementare la clausola LIMIT)
results_query, count_query = [
"SELECT {what} FROM herbs {where} {order_by} ".format(
what=x,
where="WHERE {}".format(filters_string) if filters_string else "",
order_by="ORDER BY {} {}".format(params["order_by"],
params["direction"]))
for x in ("*", "COUNT(*) AS count")
]
# Aggiunti il LIMIT nella query dei risultati DOPO la comprehension
# in questo modo count_query non ha la clausola LIMIT
results_query += limit
# Prima connessione con SSDictCursor (per prendere X righe alla volta e non tutte le righe insieme,
# in questo modo si riduce notevolmente l'uso di memoria durante l'esecuzione della query quando
# ci sono query che ritornano molte righe)
async with EmaNews().db.acquire() as conn:
async with conn.cursor(SSDictCursor) as cur:
logging.debug(results_query)
logging.debug(params)
await cur.execute(results_query, {**params})
# Recupera tutti i chunk
while True:
herbs_chunk = await cur.fetchmany()
if not herbs_chunk:
break
# Per ogni erba nel chunk...
for herb in herbs_chunk:
# ...recupera i documenti se richiesto
if params["fetch_documents"]:
# Nuova connessione (DictCursor classico, poichè i documenti per ogni erba sono pochi)
async with EmaNews().db.acquire() as doc_conn:
async with doc_conn.cursor() as doc_cur:
await doc_cur.execute(
"SELECT * FROM documents WHERE herb_id = %s",
(herb["id"], ))
herb["documents"] = await doc_cur.fetchall()
# Aggiungi l'erba + documenti alla lista totale delle erbe
herbs.append(herb)
# Determina il numero totale di righe
if not limit:
# Se non c'è la clausola LIMIT, il numero totale di risultati è uguale
# alla lunghezza di total_herbs
total_herbs = len(herbs)
else:
# Altrimenti, apri una nuova connessione ed esegui count_query
# (uguale alla query per il fetch dei risultati, ma senza clausola
# LIMIT e con SELECT COUNT(*) invece che SELECT *
async with EmaNews().db.acquire() as conn:
async with conn.cursor() as cur:
logging.debug(count_query)
await cur.execute(count_query, {**params})
total_herbs = await cur.fetchone()
if not total_herbs:
total_herbs = 0
else:
total_herbs = total_herbs["count"]
# Ritorna il risultato al client HTTP
return web.json_response({"herbs": herbs, "total": total_herbs})
async def handle(request: Request):
return web.json_response({"message": "ok", "version": EmaNews().VERSION})