def on_put_author(self, req, resp, id): try: identificador = int(id) except: erro = { 'mensagem': 'Id informado invalido' } resp.status = falcon.HTTP_400 resp.body = json.dumps(erro) return cursor = db.cursor(dictionary=True) cursor.execute("SELECT * FROM authors WHERE id = %s", (id,)) autor = cursor.fetchone() if (autor is None): erro = { 'mensagem': 'Nao ha registro de autor para o id informado.' } resp.status = falcon.HTTP_400 resp.body = json.dumps(erro) return autorAlterado = json.loads(req.stream.read().decode('utf-8')) tuplaEdicaoDeAutor = (autorAlterado['name'], autorAlterado['birth_date'], autorAlterado['thumbnail_url'], autor['id']) cursor = db.cursor(dictionary=True) cursor.execute("UPDATE authors SET name = %s, birth_date = %s, thumbnail_url = %s WHERE id = %s", (tuplaEdicaoDeAutor)) db.commit() resp.status = falcon.HTTP_200 resp.body = json.dumps(autorAlterado, default=str)
def on_put_user(self, req, resp, id): try: identificador = int(id) except: erro = {'mensagem': 'Id informado invalido'} resp.status = falcon.HTTP_400 resp.body = json.dumps(erro) return cursor = db.cursor(dictionary=True) cursor.execute("SELECT * FROM users WHERE id = %s", (id, )) usuario = cursor.fetchone() if (usuario is None): erro = { 'mensagem': 'Nao ha registro de usuario para o id informado.' } resp.status = falcon.HTTP_400 resp.body = json.dumps(erro) return usuarioAlterado = json.loads(req.stream.read().decode('utf-8')) tuplaEdicaoDeUsuario = (usuarioAlterado['user_name'], usuarioAlterado['fullname']) cursor = db.cursor(dictionary=True) cursor.execute("UPDATE users SET user_name = %s, fullname = %s", (tuplaEdicaoDeUsuario)) db.commit() resp.status = falcon.HTTP_200 resp.body = json.dumps(usuarioAlterado, default=str)
def on_put(self, req, resp): try: identificador = int(id) except: erro = {'mensagem': 'Id informado invalido'} resp.status = falcon.HTTP_400 resp.body = json.dumps(erro) return cursor = db.cursor(dictionary=True) cursor.execute("SELECT * FROM genres WHERE id = %s", (id, )) genero = cursor.fetchone() if (genero is None): erro = { 'mensagem': 'Nao ha registro de genero para o id informado.' } resp.status = falcon.HTTP_400 resp.body = json.dumps(erro) return generoAlterado = json.loads(req.stream.read().decode('utf-8')) tuplaEdicaoDeGenero = (generoAlterado['name'], ) cursor = db.cursor(dictionary=True) cursor.execute("UPDATE genres SET name = %s", (tuplaEdicaoDeGenero)) db.commit() resp.status = falcon.HTTP_200 resp.body = json.dumps(generoAlterado, default=str)
def more_reviews(): if request.method == 'POST': views_amount = request.form.get('views_amount') print(views_amount) sql = '''SELECT reader.reader_name, reader.reader_id FROM reader WHERE reader_id IN (SELECT comment_by FROM review GROUP BY comment_by HAVING COUNT(comment_by) > '%s');''' % views_amount cursor = db.cursor() try: cursor.execute(sql) db.commit() answers = cursor.fetchall() print(answers) if answers: return render_template('show_readers_views.html', answers=answers, nav='reader', amount=views_amount) else: return render_template('more_views.html', error='no') except: db.rollback() return render_template('more_views.html', error='error') return render_template('more_views.html')
async def on_guild_remove(guild): try: cursor = db.cursor() cursor.execute(f'DELETE FROM Guilds WHERE Guild_ID = {guild.id}') db.commit() except mysql.connector.Error as err: print("Something went wrong: {}".format(err))
def reader_list(): cursor = db.cursor() sql = "SELECT * FROM reader;" cursor.execute(sql) readers = cursor.fetchall() db.commit() return render_template('reader.html', nav='reader', readers=readers)
def handleAuthLogonProof(self, pkt): data = ST_AUTH_LOGON_PROOF_C.parse(pkt) #print(data) self.srp.set_A(convertool.strToInt(data.SRP_A[::-1])) self.srp.calculate_u() self.srp.calculate_S_server() self.srp.calculate_K() self.srp.calculate_M1() self.srp.calculate_M2() # Store the sessionkey in the DB cursor = db.cursor() cursor.execute( 'UPDATE accounts SET server_K = ? WHERE username = ?', [convertool.intToHex(self.srp.get_K(), 80), self.env['username']]) db.commit() # Check for authentification correctness if data.SRP_M1.encode('hex') == '%x' % self.srp.get_M1(): print('M1 Matches !') else: print('Something goes wrong during authentification :(') # Create the response packet r = ST_AUTH_LOGON_PROOF_S.build( Container(opcode=01, error=0, SRP_M2=convertool.intToStr(self.srp.get_M2()), unk1=0x00800000, unk2=0, unk3=0))
def handleAuthLogonChallenge(self, pkt): data = ST_AUTH_LOGON_CHALLENGE_S.parse(pkt) #print(data) self.srp.set_I(self.env['username']) self.srp.set_P(self.env['userpass']) self.srp.set_B(convertool.strToInt(data.SRP_B[::-1])) self.srp.set_g(convertool.strToInt(data.SRP_g[::-1])) self.srp.set_N(convertool.strToInt(data.SRP_N[::-1])) self.srp.set_s(convertool.strToInt(data.SRP_s[::-1])) self.srp.generate_a() self.srp.calculate_A() self.srp.calculate_x() self.srp.calculate_v() self.srp.calculate_u() self.srp.calculate_S_client() self.srp.calculate_K() self.srp.calculate_M1() self.srp.calculate_M2() # Store the sessionkey in the DB cursor = db.cursor() cursor.execute( 'UPDATE accounts SET client_K = ? WHERE username = ?', [convertool.intToHex(self.srp.get_K(), 80), self.env['username']]) db.commit() r = ST_AUTH_LOGON_PROOF_C.build( Container( opcode=1, SRP_A=convertool.intToStr(self.srp.get_A())[::-1], SRP_M1=convertool.intToStr(self.srp.get_M1()), CRC=chr(0) * 20, # don't know how to calculate it unk=0)) self.writebuffer.append(r)
async def authenticate(req: Request): try: authorization = req.headers["Authorization"] # Authorization header has the form "Bearer jwtToken" jwtToken = authorization[7:] data = jwt.decode( jwtToken, JWT_SECRET, algorithms='HS256', ) userEmail = data['email'] cursor = db.cursor() cursor.execute(f""" SELECT * FROM user_table WHERE email = '{userEmail}' """) user = cursor.fetchone() if user is None: raise HTTPException( status_code = 401, detail = "Unauthorized request" ) return True except: raise HTTPException( status_code = 401, detail = "Unauthorized request" )
def handleRealmList(self, pkt): if not self.env['m_realms']: self.env['condition'].acquire() while not self.env['realms']: self.env['condition'].wait() # Retrieve the realm address cursor = db.cursor() rn = cursor.execute( 'SELECT realm_name FROM accounts WHERE username = ?', [self.env['username']]).fetchone()[0] # not safe rn = rn.encode( 'latin-1') # maybe realms name are in unicode need to check cursor.execute( 'UPDATE accounts SET realm_address = ? WHERE username = ?', [self.env['realms'][rn].address, self.env['username']]) db.commit() # Alter it and send the altered version m_realms = self.env['realms'] m_realms[ rn].address = '127.0.0.1:8085' # need to change to the proxy address m_realms[rn].name += ' - PROXY' c = Container(unk1=0, nb_realms=len(m_realms), Realm=m_realms.values(), unk2=0x10, unk3=0) pkt_p = ST_REALM_LIST_S_PAYLOAD.build(c) c = Container(opcode=0x10, size=len(pkt_p)) pkt_h = ST_REALM_LIST_S_HEADER.build(c) self.env['m_realms'] = pkt_h + pkt_p self.env['condition'].release() self.writebuffer.append(self.env['m_realms'])
def analysis_new_mission_commonparameters_tagged_contains(city): lines = commonparameters(city) db = pymysql.connect(host=HOST, user=USER, password=PASSWD, db=DB, charset='utf8') cursor = db.cursor() for l in range(0, len(lines)): cgi = lines[l][4] district = lines[l][3] shapes = gaodemapscene(district, city, 120000) Path = mpath.Path for s in range(0, len(shapes)): shape = shapes[s][-1] shape_array = np.array([ float(i) for i in shape.replace('|', ',').replace(';', ',').split(',') ]).reshape(-1, 2) point = (float(lines[l][15]), float(lines[l][14])) p = Path(shape_array) if p.contains_points([point]): query_commonparameters_tagged = """UPDATE {} set residential_flag='1' where cgi='{}'""".format( TABLE_NAME_ANALYSIS_Commonparameters, cgi) cursor.execute(query_commonparameters_tagged) break print('%.2f%%' % (l / len(lines) * 100)) cursor.close() db.commit() db.close()
def handleAuthLogonChallenge(self, pkt): data = ST_AUTH_LOGON_CHALLENGE_S.parse(pkt) #print(data) self.srp.set_I(self.env['username']) self.srp.set_P(self.env['userpass']) self.srp.set_B(convertool.strToInt(data.SRP_B[::-1])) self.srp.set_g(convertool.strToInt(data.SRP_g[::-1])) self.srp.set_N(convertool.strToInt(data.SRP_N[::-1])) self.srp.set_s(convertool.strToInt(data.SRP_s[::-1])) self.srp.generate_a() self.srp.calculate_A() self.srp.calculate_x() self.srp.calculate_v() self.srp.calculate_u() self.srp.calculate_S_client() self.srp.calculate_K() self.srp.calculate_M1() self.srp.calculate_M2() # Store the sessionkey in the DB cursor = db.cursor() cursor.execute('UPDATE accounts SET client_K = ? WHERE username = ?', [convertool.intToHex(self.srp.get_K(), 80), self.env['username']]) db.commit() r = ST_AUTH_LOGON_PROOF_C.build(Container( opcode = 1, SRP_A = convertool.intToStr(self.srp.get_A())[::-1], SRP_M1 = convertool.intToStr(self.srp.get_M1()), CRC = chr(0) * 20, # don't know how to calculate it unk = 0 )) self.writebuffer.append(r)
def is_apikey_valid(self, api_key, ip_address): """ checks the availability of api key and whether this can be used for API calls. """ query_template = """ SELECT * FROM ost_api_key WHERE apikey=%s """ cur = db.cursor() cur.execute(query_template, (api_key,)) data = cur.fetchone() cur.close() if not data: return False # if not data.get("ipaddr") == ip_address: # return False if not data.get("isactive") == 1: return False return True
def handleRealmList(self, pkt): if not self.env['m_realms']: self.env['condition'].acquire() while not self.env['realms']: self.env['condition'].wait() # Retrieve the realm address cursor = db.cursor() rn = cursor.execute('SELECT realm_name FROM accounts WHERE username = ?', [self.env['username']]).fetchone()[0] # not safe rn = rn.encode('latin-1') # maybe realms name are in unicode need to check cursor.execute('UPDATE accounts SET realm_address = ? WHERE username = ?', [self.env['realms'][rn].address, self.env['username']]) db.commit() # Alter it and send the altered version m_realms = self.env['realms'] m_realms[rn].address = '127.0.0.1:8085' # need to change to the proxy address m_realms[rn].name += ' - PROXY' c = Container( unk1 = 0, nb_realms = len(m_realms), Realm = m_realms.values(), unk2 = 0x10, unk3 = 0 ) pkt_p = ST_REALM_LIST_S_PAYLOAD.build(c) c = Container( opcode = 0x10, size = len(pkt_p) ) pkt_h = ST_REALM_LIST_S_HEADER.build(c) self.env['m_realms'] = pkt_h + pkt_p self.env['condition'].release() self.writebuffer.append(self.env['m_realms'])
def handleAuthLogonProof(self, pkt): data = ST_AUTH_LOGON_PROOF_C.parse(pkt) #print(data) self.srp.set_A(convertool.strToInt(data.SRP_A[::-1])) self.srp.calculate_u() self.srp.calculate_S_server() self.srp.calculate_K() self.srp.calculate_M1() self.srp.calculate_M2() # Store the sessionkey in the DB cursor = db.cursor() cursor.execute('UPDATE accounts SET server_K = ? WHERE username = ?', [convertool.intToHex(self.srp.get_K(), 80), self.env['username']]) db.commit() # Check for authentification correctness if data.SRP_M1.encode('hex') == '%x' % self.srp.get_M1(): print('M1 Matches !') else: print('Something goes wrong during authentification :(') # Create the response packet r = ST_AUTH_LOGON_PROOF_S.build(Container( opcode = 01, error = 0, SRP_M2 = convertool.intToStr(self.srp.get_M2()), unk1 = 0x00800000, unk2 = 0, unk3 = 0 ))
def on_post(self, req, resp): novoLivro = json.loads(req.stream.read().decode('utf-8')) # realizar verificação do author_id # realizar verificação do genre_id # realizar verificação se o author_id recebido existe no banco de dados # realizar verificação se o genre_id recebido existe no banco de dados tuplaLivro = (novoLivro['title'], novoLivro['sinopsis'], novoLivro['release_date'], novoLivro['thumbnail_url'], novoLivro['author_id']) cursor = db.cursor(dictionary=True) cursor.execute( "INSERT INTO books (title, sinopsis, release_date, thumbnail_url, author_id) VALUES (%s, %s, %s, %s, %s)", (tuplaLivro)) bookId = cursor.lastrowid tuplaLivroGenero = (bookId, novoLivro['genre_id']) cursor.execute( "INSERT INTO books_genres (book_id, genre_id) VALUES (%s, %s)", tuplaLivroGenero) db.commit() resp.status = falcon.HTTP_201 resp.body = json.dumps(novoLivro, default=str)
async def login(req: LoginModel): email = req.email password = req.password cursor = db.cursor() cursor.execute(f""" SELECT * FROM user_table WHERE email = '{email}' """) user = cursor.fetchone() if user is None: raise HTTPException( status_code = 400, detail = "Email or password is incorrent!" ) userEmail = user[0] userPassword = user[1] if bcrypt.checkpw(password.encode('utf-8'), userPassword.encode('utf-8')): jwtToken = jwt.encode( {'email': str(userEmail)}, JWT_SECRET, algorithm='HS256' ).decode('utf-8') return { 'jwtToken': jwtToken, 'message': "Logged in successfully!" } else: raise HTTPException( status_code = 400, detail = "Email or password is incorrent!" )
async def on_guild_join(guild): try: cursor = db.cursor() cursor.execute(f'INSERT INTO Guilds (Guild_ID, Prefix) VALUES(%s, %s)', (guild.id, "!")) db.commit() except mysql.connector.Error as err: print("Something went wrong: {}".format(err))
def home(): if request.method == 'GET': cursor = db.cursor() bus_stop_names = 'SELECT DISTINCT ORIGIN FROM BUS_STOP' cursor.execute(bus_stop_names) result = cursor.fetchall() db.commit() return render_template('home.html', bus_stops=result)
def collction_of_books_list(): cursor = db.cursor() sql = "SELECT * FROM collectionofbook;" cursor.execute(sql) db.commit() answer = cursor.fetchall() return render_template('collectionofbooks.html', nav='book-collections', collections=answer)
def get_user(self, email=None, username=None, uid=None): """ gets the user details from different user tables in a single query. """ EXCLUDE_FIELDS = ["passwd"] if not any([email, username, uid]): raise ServiceException('one of email, username or uid is needed') cur = db.cursor() if uid: query_template = """ SELECT * FROM ost_user t1 INNER JOIN ost_user__cdata t2 ON t2.user_id=t1.id INNER JOIN ost_user_email t3 ON t3.user_id=t1.id INNER JOIN ost_user_account t4 ON t4.user_id=t1.id WHERE t1.id=%s """ cur.execute(query_template, (uid, )) elif username: query_template = """ SELECT * FROM ost_user_account t1 INNER JOIN ost_user__cdata t2 ON t2.user_id=t1.user_id INNER JOIN ost_user_email t3 ON t3.user_id=t1.user_id INNER JOIN ost_user t4 ON t4.id=t1.user_id WHERE t1.username=%s """ cur.execute(query_template, (username, )) elif email: query_template = """ SELECT * FROM ost_user_email t1 INNER JOIN ost_user t2 ON t2.default_email_id=t1.id INNER JOIN ost_user__cdata t3 ON t3.user_id=t2.id INNER JOIN ost_user_account t4 ON t4.user_id=t2.id WHERE t1.address=%s """ cur.execute(query_template, (email, )) data = cur.fetchone() if not data: return data for field in EXCLUDE_FIELDS: data.pop(field) cur.close() return data
def detail_of_books_list(): cursor = db.cursor() sql = "SELECT * FROM detailofbook;" cursor.execute(sql) db.commit() details = cursor.fetchall() return render_template('detailofbooks.html', nav='book-details', details=details)
async def set_channel(self, ctx, channel: discord.TextChannel): try: cursor = db.cursor() cursor.execute(f'UPDATE Guilds SET announcement_channel = "{channel.id}" WHERE Guild_ID = {ctx.guild.id}') db.commit() embed = discord.Embed(description=f'Twitch announcements will now be placed in: {channel.mention}') await ctx.send(embed=embed) except mysql.connector.Error as err: print("Something went wrong: {}".format(err))
def add_organization(self, name, manager='', status=8, domain='', extra=None, address='', phone='', website='', notes=''): """ creates an organization in the osticket database """ def get_org_id_from_name(): query_template = """ SELECT id FROM ost_organization WHERE name=%s """ cur = db.cursor() cur.execute(query_template, (name, )) org_id = cur.fetchone().get("id") cur.close() return {"organization_id": org_id} query_template = """ START TRANSACTION; BEGIN; INSERT INTO ost_organization (name, manager, status, domain, extra, created, updated) VALUES (%s, %s, %s, %s, %s, %s, %s); SET @OrgID = LAST_INSERT_ID(); INSERT INTO ost_organization__cdata (org_id, name, address, phone, website, notes) VALUES (@OrgID, %s, %s, %s, %s, %s); COMMIT; SELECT @OrgID; """ cur = db.cursor() cur.execute(query_template, (name, manager, status, domain, extra, datetime.now(), datetime.now(), name, address, phone, website, notes)) cur.close() try: db.commit() except Exception as exc: db.rollback() raise ServiceException(exc.message) return get_org_id_from_name()
def get_lunar_phase(date): query = "SELECT * FROM lunar_phases WHERE date=%s" cursor = db.cursor() cursor.execute(query, [date_to_string(date)]) result = cursor.fetchone() if not result: raise Exception("Error: no result found!") cursor.close() phase = LunarPhase(date, result[1]) return json.dumps(phase.serialize())
def get_prefix(client, message): try: cursor = db.cursor() cursor.execute( f'SELECT Prefix FROM Guilds WHERE Guild_ID = {message.guild.id}') row = cursor.fetchone() return row[0] except mysql.connector.Error as err: print("Something went wrong: {}".format(err)) return '!'
def on_post(self, req, resp): novoAutor = json.loads(req.stream.read().decode('utf-8')) tuplaAutor = (novoAutor['name'], novoAutor['birth_date'], novoAutor['thumbnail_url']) cursor = db.cursor(dictionary=True) cursor.execute("INSERT INTO authors (name, birth_date, thumbnail_url) VALUES (%s, %s, %s)", (tuplaAutor)) db.commit() resp.status = falcon.HTTP_201 resp.body = json.dumps(novoAutor, default=str)
async def setprefix(self, ctx, prefix): try: cursor = db.cursor() cursor.execute(f'UPDATE Guilds SET Prefix = "{prefix}" WHERE Guild_ID = {ctx.guild.id}') db.commit() bot.command_prefix = prefix except mysql.connector.Error as err: print("Something went wrong: {}".format(err)) await ctx.message.add_reaction('✅')
def on_post(self, req, resp): novoGenero = json.loads(req.stream.read().decode('utf-8')) tuplaGenero = (novoGenero['name'], ) cursor = db.cursor(dictionary=True) cursor.execute("INSERT INTO genres (name) VALUES (%s)", (tuplaGenero)) db.commit() resp.status = falcon.HTTP_201 resp.body = json.dumps(novoGenero, default=str)
def get_user_id_from_email(): query_template = """ SELECT user_id FROM ost_user_email WHERE address=%s """ cur = db.cursor() cur.execute(query_template, (email, )) user_id = cur.fetchone().get("user_id") cur.close() return {"user_id": user_id}
def adminsdata(): if request.method == 'GET': cursor = db.cursor() bus_stop_crowd = "select CCTV_ID, NAME,COUNT from SCHEDULE where FACILITY = 'STOP'" bus_crowd = "select CCTV_ID,NAME,COUNT,concat('<a href=\"https://www.google.com/maps/@',latitude,',' , longitude,',17z\">',latitude,',',longitude ,'</a>') as Location from transport.SCHEDULE as S left join traccar.tc_positions as T on T.deviceid = S.deviceid where S.FACILITY = 'BUS' and T.id = ( SELECT max(T.id) FROM traccar.tc_positions )" cursor.execute(bus_stop_crowd) bus_stop_result = cursor.fetchall() cursor.execute(bus_crowd) stop_result = cursor.fetchall() db.commit() return jsonify(bus_stop_result, stop_result)
def get_org_id_from_name(): query_template = """ SELECT id FROM ost_organization WHERE name=%s """ cur = db.cursor() cur.execute(query_template, (name, )) org_id = cur.fetchone().get("id") cur.close() return {"organization_id": org_id}
def on_post(self, req, resp): novoUsuario = json.loads(req.stream.read().decode('utf-8')) tuplaUsuario = (novoUsuario['user_name'], novoUsuario['fullname']) cursor = db.cursor(dictionary=True) cursor.execute( "INSERT INTO users (user_name, fullname) VALUES (%s, %s)", (tuplaUsuario)) db.commit() resp.status = falcon.HTTP_201 resp.body = json.dumps(novoUsuario, default=str)
def checkTiles(directory): result = True cursor = db.cursor() cursor.execute(""" SELECT iid, "imageWidth", "imageHeight", "tileWidth", "tileHeight" FROM images WHERE status >= %s """ % IMAGE_STATUS_COMPLETED) for row in cursor: if not checkImages(os.path.join(directory, "%d" % row[0]), *row[1:]): result = False cursor.close() return result
def handleAuthLogonChallenge(self, pkt): data = ST_AUTH_LOGON_CHALLENGE_C.parse(pkt) #print(data) # Get database cursor cursor = db.cursor() # Set the username in the env self.env['username'] = data.I self.env['userpass'] = cursor.execute('SELECT password FROM accounts WHERE username = ?', [data.I]).fetchone()[0] # not safe should do some checking # Do client part authentification self.env['client'].initAuthentification() # Init SRP Object self.srp.set_I(data.I) self.srp.set_P(self.env['userpass']) self.srp.generate_b() self.srp.generate_s() self.srp.calculate_x() self.srp.calculate_v() self.srp.calculate_B() # Create the response packet r = ST_AUTH_LOGON_CHALLENGE_S.build(Container( opcode = 0, unk = 0, error = 0, SRP_B = convertool.intToStr(self.srp.get_B())[::-1], SRP_g = chr(self.srp.get_g()), SRP_N = convertool.intToStr(self.srp.get_N())[::-1], SRP_s = convertool.intToStr(self.srp.get_s())[::-1], CRC_salt = '430d7525f492e4e03bc66b8d1130cfac'.decode('hex'), security_flag = 0 )) self.writebuffer.append(r)
import sys import asyncore from realm_proxy import RealmServer from world_proxy import WorldServer from database import db from sqlite3 import OperationalError args = sys.argv[1:] if len(args) == 0: rserver = RealmServer() wserver = WorldServer() asyncore.loop() elif args[0] == '-sql': cursor = db.cursor() while True: sql = raw_input('> ') try: t = cursor.execute(sql).fetchall() db.commit() for r in t: print r except OperationalError as e: print('SQL ERROR : %s' % e)