def register_user(data): username = data["username"].strip() password = data["password"].strip() user_data = db_query().get_one( "users", "username='******' or email='" + data["email"].strip() + "'") if user_data == None: ids = str(uuid.uuid4()).replace("-", "") code = random.randint(1111, 9999) stored_password = hash_password(password) insert_data = { "username": username, "email": data["email"].strip(), "password": stored_password, "status": "deactive", "code": code, "role": "user", "id": ids } link = "http://127.0.0.1:5000/activate?id=" + ids + "&code=" + str( code) + "" insert_in_db = db_query().insert("users", insert_data) if insert_in_db: return link # try: # # mail_verification_code(link,data["email"]) # return True # except Exception as e: # raise e # return False else: return False else: return False
def newprov(): if session['logged']: test = 1 dific = tipo = qtd = 0 try: dific = request.form.get('dificuldade') tipo = request.form.get('tipo') qtd = request.form.get('qtd') except ValueError: test = 0 try: qtd = int(qtd) except ValueError: test = 0 if test: banco = get_db().cursor() banco.execute(db_query(get_db(), 'INSERE_PROVA'), (session['prof_logged'], dific, tipo, qtd)) banco.execute(db_query(get_db(), 'SELECT_ID_PROVA'), (qtd, session['prof_logged'], tipo, dific)) get_db().commit() idprova = banco.fetchone() banco.close() return render_template("pages/mensagem.jade", mensagem="Codigo da prova: ", variavel=idprova[0]) else: return render_template("pages/mensagem.jade", mensagem="Os dados nao passaram na validacao") else: return redirect(url_for('logar'))
def get_chapters(ids=None): if ids is None: sql = "SELECT * FROM chapter" res = db_query(sql) return res if len(ids) == 0: return None sql = "SELECT * FROM chapter WHERE id IN (" for _ in range(len(ids) - 1): sql += "%s, " sql += "%s)" res = db_query(sql, (chapter_id for chapter_id in ids)) return res
def confirm(): try: key = request.args.get('id') banco = get_db().cursor() banco.execute(db_query(get_db(), 'SELECT_EMAIL_TEMP'), [key]) mail = banco.fetchone() banco.execute(db_query(get_db(), 'CONFIRMA_EMAIL'), [mail[0]]) banco.execute(db_query(get_db(), 'REMOVE_EMAIL_TEMP'), [key]) get_db().commit() banco.close() except ValueError: print ValueError return render_template("pages/mensagem.jade", mensagem="Chave expirada") return render_template("pages/mensagem.jade", mensagem="Cadastro ativado com sucesso")
def renew(): try: origem = request.form.get('tipo') old = request.form.get('old') new1 = request.form.get('key1') new2 = request.form.get('key2') except ValueError: return render_template("pages/mensagem.jade", titulo="Houve um erro") if origem == '0': banco = get_db().cursor() banco.execute(db_query(get_db(), 'SELECT_SENHA'), [session['prof_logged']]) senha = banco.fetchone() password = hashlib.md5(str(old) + "lolzinho") key = password.hexdigest() if senha[0] == key: if new1 == new2: passw = hashlib.md5(str(new1) + "lolzinho") new1 = passw.hexdigest() try: banco.execute(db_query(get_db(), 'UPDATE_SENHA_ID'), (new1, session['prof_logged'])) get_db().commit() banco.close() return render_template("pages/mensagem.jade", titulo="Senha alterada com sucesso") except ValueError: return render_template("pages/mensagem.jade", titulo="Houve um erro durante o processamento") else: return render_template("pages/mensagem.jade", titulo="Senhas nao conferem") else: return render_template("pages/mensagem.jade", titulo="Senha nao confere com a antiga") elif origem == '1': if new1 == new2: passw = hashlib.md5(str(new1) + "lolzinho") new1 = passw.hexdigest() try: if 'reset' in session: banco = get_db().cursor() banco.execute(db_query(get_db(), 'UPDATE_SENHA_EMAIL'), (new1, session['reset'])) banco.execute(db_query(get_db(), 'REMOVE_EMAIL_TEMP'), (session['reset'], )) get_db().commit() banco.close() session.pop('reset') return render_template("pages/mensagem.jade", titulo="Senha alterada com sucesso") else: return render_template("pages/mensagem.jade", titulo="Nao foi possivel localizar seu cadastro") except ValueError: return render_template("pages/mensagem.jade", titulo="Houve um erro durante o processamento") else: return render_template("pages/mensagem.jade", titulo="Senhas nao conferem") return render_template("pages/mensagem.jade", titulo="Os dados nao foram obtidos")
def prova(): try: session['alunoNome'] = request.form.get('nome') session['alunoEmail'] = request.form.get('email') session['alunoProva'] = request.form.get('prova') except ValueError: session['alunoNome'] = "Nao deu" session['alunoEmail'] = "Nao deu" session['alunoProva'] = "Nao deu" try: session['alunoProva'] = int(session['alunoProva']) except ValueError: return render_template("pages/mensagem.jade", mensagem="Prova Inexistente") banco = get_db().cursor() banco.execute(db_query(get_db(), 'SELECT_PROVA'), (session['alunoProva'],)) provas = banco.fetchone() banco.close() if provas: session['qtd_quest'] = provas[4] session['istest'] = 1 session['respondidas'] = 0 return render_template("pages/prova.jade", nome=session['alunoNome'], email=session['alunoEmail'], prova=provas[0]) else: return render_template("pages/mensagem.jade", mensagem="Prova Inexistente")
def api_res_prov(): resultados = [] if session['logged']: banco = get_db().cursor() banco.execute(db_query(get_db(), 'SELECT_RESOLUCAO'), (session['prof_logged'], session['prova'])) resultados = banco.fetchall() banco.close() return jsonify(resultados=resultados)
def get_questions_by_kp(kp_ids): if len(kp_ids) == 0: return None sql = "SELECT `id`, `type` FROM question WHERE knowledge_point IN (" for _ in range(len(kp_ids) - 1): sql += "%s, " sql += "%s)" res = db_query(sql, (kp_id for kp_id in kp_ids)) return res
def adduser(name, email, user, key): banco = get_db().cursor() passw = hashlib.md5(str(key) + "lolzinho") key = passw.hexdigest() mail = hashlib.md5(str(email) + "churros") mail = mail.hexdigest() try: banco.execute(db_query(get_db(), 'INSERT_PROF'), (name, email, user, key)) banco.execute(db_query(get_db(), 'INSERT_EMAIL_TEMP'), (email, mail)) get_db().commit() banco.close() except ValueError: return render_template("pages/mensagem.jade", mensagem="Houve um erro") try: send_mail(email, mail, 0) return 0 except ValueError: return render_template("pages/mensagem.jade", mensagem="Houve um erro")
def get_knowledge_points(chapter_ids): if len(chapter_ids) == 0: return None sql = "SELECT `id`, `k_level`, `number`, `score`, `title`, `chapter_id` FROM knowledge_point WHERE chapter_id IN (" for _ in range(len(chapter_ids) - 1): sql += "%s, " sql += "%s)" res = db_query(sql, (chapter_id for chapter_id in chapter_ids)) return res
def get(self, format="csv"): if format == 'csv': self.set_header('Content-Type', 'text/csv') self.set_header('Content-Disposition', 'attachment; filename=export.csv') self.write(','.join(fields) + '\n') sql = "select %s from indicator where username = ?" % ", ".join(fields) indicators = db_query(sql, [self.current_user]) for indicator in indicators: self.write(",".join([str(indicator[f]) for f in fields]) + "\n") else: self.write("Unimplemented export")
def view_prov(): if session['logged']: banco = get_db().cursor() banco.execute(db_query(get_db(), 'SELECT_PROVA_PROF'), (session['prof_logged'],)) resultados = banco.fetchall() banco.close() if resultados: return render_template("pages/view_prov.jade", mensagem="Provas", resultado=resultados) else: return render_template("pages/mensagem.jade", mensagem="Nao ha dados") else: return redirect(url_for('logar'))
def rescue(): try: key = request.args.get('id') banco = get_db().cursor() banco.execute(db_query(get_db(), 'SELECT_EMAIL_TEMP'), (key, )) session['reset'] = banco.fetchone() session['reset'] = session['reset'][0] get_db().commit() banco.close() except ValueError: return render_template("pages/mensagem.jade", titulo="Houve um erro :(", mensagem="Nao foi possivel consultar " "o seu email") return render_template("pages/renew_pass.jade")
def login(username, password): password = sha(password) try: sql = "SELECT username FROM user WHERE username = %s AND password = %s" res = db_query(sql, (username, password)) if len(res) == 1 and res[0].get("username") == username: ts = get_now_ts() sql = "UPDATE user SET last_login = %s WHERE username = %s" db_exec(sql, (ts, username)) return True return False except Exception as e: logging.error("[database] unknown erro=%s" % e) return False
def sendforgot(): try: email = request.form.get('mail') mail = hashlib.md5(str(email) + "churros") mail = mail.hexdigest() banco = get_db().cursor() banco.execute(db_query(get_db(), 'INSERT_EMAIL_TEMP'), (email, mail)) get_db().commit() banco.close() except ValueError: return render_template("pages/mensagem.jade", titulo="Houve um erro :(", mensagem="Nao foi possivel consultar " "o seu email") try: send_mail(email, mail, 1) return render_template("pages/mensagem.jade", mensagem="Email enviado") except ValueError: return render_template("pages/mensagem.jade", mensagem="Houve um erro")
def main(): dbuser = rj.get_db_user(); dbpwd = rj.get_db_pwd() dbhost = rj.get_db_host(); dbname = rj.get_db_name() dbport = rj.get_db_port(); dbtables = rj.get_db_tables() sender = rj.get_sender(); smtphost = rj.get_smtp_host() recipient = rj.get_recipient() db = MySQLDatabase(dbuser, dbpwd, dbname, dbhost, dbport) Session = db.mysql_session() files_list = StorageFilesList().create_selected_list() for file_path in files_list: cksgz_stg = md5Checksum(file_path).calculate_checksum() cks_stg = md5Checksum(file_path).get_checksum_gz() head, fname_gz = os.path.split(file_path) for tbl in dbtables: db_element = db_query(tbl, Session, fname_gz) if db_element is not None: cks_db = db_element[1] cksgz_db = db_element[2] if cks_db is not None and cksgz_db is not None: if cksgz_stg == cksgz_db and cks_stg == cks_db: try: print(file_path) #os.remove(file_path) except Exception as e: msg = "File removal exception --" log.error("{0}{1}".format(msg,e)) else: message = 'Severe alert - storage and DB file checksums DO NOT MATCH' SendEmail(message,recipient,smtphost,sender).send_email() else: message = 'Severe alert - checksum not calculated' SendEmail(message,recipient,smtphost,sender).send_email() else: pass db.close_session()
def trylogin(user, key): password = hashlib.md5(str(key) + "lolzinho") key = password.hexdigest() banco = get_db().cursor() banco.execute(db_query(get_db(), 'SELECT_LOGIN'), (user,)) senha = banco.fetchone() banco.close() if senha: if senha[3] == 0: return 2 if key == senha[1]: session['prof_logged'] = senha[0] session['prof_name'] = senha[2] return 1 else: return 0 return -1
def res_prov(): try: session['prova'] = request.form.get('prova') except ValueError: return render_template("pages/mensagem.jade", mensagem="Houve um erro, tente novamente") if session['logged']: banco = get_db().cursor() banco.execute(db_query(get_db(), 'SELECT_RESOLUCAO'), (session['prof_logged'], session['prova'])) resultados = banco.fetchall() banco.close() if resultados: return render_template("pages/result.jade", mensagem="Resultados para a prova", resultado=resultados, variavel=session['prova']) else: return render_template("pages/mensagem.jade", mensagem="Nao ha dados") else: return redirect(url_for('logar'))
def validate_login(data): user_data = db_query().get_one( "users", "username='******' or email='" + data["username"].strip() + "'") if user_data == None: return "username not found" elif user_data != None: if user_data["status"] == "active": print("active") if user_data["username"] == data["username"] and verify_password( user_data["password"], data["password"] ) or user_data["email"] == data["username"] and verify_password( user_data["password"], data["password"]): print("matched") session['id'] = user_data["id"] session['role'] = user_data["role"] session['username'] = user_data["username"] session['email'] = user_data["email"] return True else: return "invalid user" else: return "You are not active"
def register(reg_data): full_name = reg_data.get("full_name", None) password = reg_data.get("password", None) phone = reg_data.get("phone", None) username = reg_data.get("username", None) email = reg_data.get("email", None) if not (full_name and password and phone and username and email): return False sql = "SELECT username FROM user WHERE username = %s OR email = %s" res = db_query(sql, (username, email)) if len(res) > 0: return False sql = "INSERT INTO user (create_on, update_on, email, enable, full_name, is_admin, last_login, " \ "login_count, password, phone, username) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" ts = get_now_ts() password = sha(password) try: db_exec( sql, (ts, ts, email, 1, full_name, 1, ts, 1, password, phone, username)) return True except Exception as e: logging.error("[user.register] unknown error=%s" % e) return False
def get(self, start_date=None): # Get the latest period start logging.info("start_date = %s", start_date) sql = "select date from indicator where period_start = ? and username = ? order by date asc" res = db_query(sql, [1, self.current_user]) start_list = [r['date'] for r in res] indicators = [] end_date = '9999-12-30' previous_date, next_date = None, None y_max_t = 0 y_min_t = 999 y_max_w = 0 y_min_w = 999 events = [] if start_list: if not start_date: start_date = start_list[-1] if len(start_list) > 1: previous_date = start_list[-2] else: idx = start_list.index(start_date) if idx < (len(start_list) - 1): end_date = start_list[idx + 1] next_date = end_date if idx > 0: previous_date = start_list[idx - 1] sql = "select * from indicator where date >= ? and date < ? and username = ? order by date desc" res = db_query(sql, [start_date, end_date, self.current_user]) for row in res: indicator = {} for k in row.keys(): if 'temp' in k or 'weight' in k: indicator[k] = float(row[k]) if row[k] else row[k] elif k == 'event': if row[k] and row[k] != '000': date_parts = row['date'].split('-') date_display_parts = [] for idx, v in enumerate(row[k]): if v == '1': events.append(row['date']) date_display_parts.append('<span class="event%s">%s</span>' % ((idx + 1), date_parts[idx])) else: events.append('') date_display_parts.append(date_parts[idx]) indicator["date_display"] = "-".join(date_display_parts) else: indicator[k] = row[k] for t in ('morning_temp', 'night_temp'): if indicator[t]: y_max_t = max(y_max_t, float(indicator[t])) y_min_t = min(y_min_t, float(indicator[t])) for w in ('morning_weight', 'night_weight'): if indicator[w]: y_max_w = max(y_max_w, float(indicator[w])) y_min_w = min(y_min_w, float(indicator[w])) indicators.append(indicator) y_max_t += 0.1 y_min_t -= 0.1 y_max_w += 0.2 y_min_w -= 0.2 if events: holder = "" extra = [] for e in events[:3]: extra.append(e) if e: holder = e break events.extend(extra) for idx, v in enumerate(events): if not v: events[idx] = holder self.render("index.html", indicators=indicators, previous_date=previous_date, next_date=next_date, start_list=start_list, y_max_t=y_max_t, y_min_t=y_min_t, y_max_w=y_max_w, y_min_w=y_min_w, start_date=start_date, event_map=self.get_event_map(), events=events)
def get_question_detail(question_id): sql = "SELECT `id`, `stem`, `type` FROM question WHERE id = %s" res = db_query(sql, (question_id, ), one=True) return res
def get_song_likes(songid): database.db_query("SELECT * IN songs")
def question(): # verifica se a questao anterior foi respondida, se sim gera outra if session['valid']: banco = get_db().cursor() # verifiva se e uma prova if session['istest']: # verifica se ja respondeu todas as questoes if session['respondidas'] == session['qtd_quest']: # grava a resolucao no banco banco.execute(db_query(get_db(), 'INSERT_RESOLUCAO'), (session['count'], session['alunoNome'], session['alunoProva'], session['alunoEmail'])) get_db().commit() banco.close() conj = [] conj.append(session['alunoNome']) conj.append(session['count']) conj.append(session['qtd_quest']) conj.append(session['alunoProva']) try: send_mail(session['alunoEmail'], conj, 2) except ValueError: pass # exibe os acertos return render_template("pages/mensagem.jade", mensagem="Acertos: ", variavel=session['count']) # se nao terminou gera uma nova questao a partir dos padroes da prova else: banco.execute(db_query(get_db(), 'SELECT_PROVA'), (session['alunoProva'],)) thisprova = banco.fetchone() session['difi'] = thisprova[3] tipoq = thisprova[2] banco.execute(db_query(get_db(), 'SELECT_ID_QUESTAO_TIP_DIF'), (session['difi'], tipoq)) questao = banco.fetchall() quest = randint(0, len(questao)-1) banco.execute(db_query(get_db(), 'SELECT_QUESTAO_ID'), (questao[quest][0],)) questao = banco.fetchone() mod = questao[2] # se nao for prova busca questoes no banco baseado na dificuldade else: banco.execute(db_query(get_db(), 'SELECT_ID_QUESTAO_DIF'), (session['difi'],)) questao = banco.fetchall() quest = randint(0, len(questao)-1) banco.execute(db_query(get_db(), 'SELECT_QUESTAO_ID'), (questao[quest][0],)) questao = banco.fetchone() mod = questao[2] # compila a expressao expr, text, count = rexpr.compile(mod) args = [] # gera 'count' argumentos randomicos for i in xrange(count): args.append(randint(questao[4], questao[5])) # resolve a expressao com os valores gerados result, args = rexpr.eval(expr, args) # armazena os valores para o caso de nao responder a questao session['respo'] = int(result) resp = hashlib.md5(str(int(result)) + "paodebatata") expressao = text.format(*tuple(args)) session['quest'] = expressao banco.close() # pro caso da questao anterior nao ter sido resolvida else: result = session['respo'] resp = hashlib.md5(str(int(result)) + "paodebatata") expressao = session['quest'] # MUDA O PARAMETRO VERIFICADOR DA RESPOSTA session['valid'] = 0 if session['istest']: return render_template("pages/question.jade", vari="Quantidade de questoes: ", acertos=session['qtd_quest'], expressao=expressao, secret=resp.hexdigest()) return render_template("pages/question.jade", vari="Acertos", expressao=expressao, acertos=session['count'], secret=resp.hexdigest())