def __init__(self): #PG-connection setup print( "AUTHORS NOTE: If you submit faulty information here, I am not responsible for the consequences." ) print "The idea is that you, the authorized database user, log in." print "Then the interface is available to employees whos should only be able to enter shipments as they are made." while True: try: params = { 'host': 'nestor2.csc.kth.se', 'user': pgdb.escape_string(raw_input("Username: "******"Password: "******"Record a shipment", "Show stock", "Show shipments", "Exit" ] self.cur = self.conn.cursor()
def insert(self): """inserts tuples. Will query the user for the information required to create tuples.""" #First we should get which table to insert into table = pgdb.escape_string(raw_input("Choose table: ")) print table columns = pgdb.escape_string(raw_input("Choose column(s): ")) #Then filter (as seen in the select func above, i.e. the where clause) #values = pgdb.escape_string(raw_input("Values of tuple to insert: ")) values = raw_input("Values of tuple to insert: ") #valueList = values.split(",") #print "we got values=>>%s<<" % values # try/define query, inspired by line 119--122 try: query = """INSERT INTO %s(%s) VALUES (%s);""" % (table, columns, values) except (NameError,ValueError, TypeError,SyntaxError): print " Bad input." return print query #execute the query (line 128) self.cur.execute(query) #print "Comitting..." self.conn.commit() #this might better be done only in the exit method
def makeShipments(self): try: CID = int(raw_input("cutomerID: ")) SID = int(input("shipment ID: ")) Sisbn= pgdb.escape_string((raw_input("isbn: ").strip())) Sdate= pgdb.escape_string(raw_input("Ship date: ").strip()) except (NameError,ValueError, TypeError,SyntaxError): print("Incorrect input.Try again...") return query = "SELECT stock FROM stock WHERE isbn='%s';" % (Sisbn) print query #start a transaction try: self.cur.commit() self.cur.execute("set transaction isolation level serializable") try: self.cur.execute(query) except(pgdb.DatabaseError, pgdb.OperationalError): print("Incorrect query.") return stock_result = self.cur.fetchone() if stock_result is None: print("No books found with isbn: %s" % Sisbn) return cnt = stock_result[0]; if cnt < 1: print("No more books in stock :(") return else: print "We have the book in stock" query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn) self.cur.execute(query) print "stock decremented" query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % (SID,CID,Sisbn,Sdate) print query try: self.cur.execute(query) # execute insert except (pgdb.DatabaseError, pgdb.OperationalError): print "Failed on insert!" return print "shipment created" # This ends the transaction (and starts a new one) self.conn.commit() except (pgdb.DatabaseError, pgdb.OperationalError) as e: print " Exception encountered while modifying table data." self.conn.rollback() print " Rolling back." return
def shipments(self): #Test input is ID=671, fname='Chuck', lname='Brown' try: #Check that input is integer ID = int(raw_input("customerID: ")) except (NameError, ValueError, TypeError, SyntaxError): print("Non numerical ID. Try again...") return #Good against SQL injections attacks because escape characters are removed fname = pgdb.escape_string(raw_input("First Name: ").strip()) lname = pgdb.escape_string(raw_input("Last Name: ").strip()) query = "SELECT first_name, last_name FROM customers WHERE customer_id = %s;" % ID print(query) print("------------------------------------------------------------") try: self.cur.execute(query) except (NameError, ValueError, TypeError, SyntaxError): print("Query execution failed.") return #fetchone() retrieves one result row for the query that was executed. #empty list (none) -> nothing was retrieved from the DB customer_list = self.cur.fetchone() if customer_list is None: print("Customer does not exist in DB") return else: if customer_list[0].lower() == fname.lower( ) and customer_list[1].lower() == lname.lower(): print("Welcome %s %s" % (fname, lname)) else: print( "Name %s %s does not match %s" % (fname, lname, ID) ) #ID exists but first name or/and last name are incorrect return #isbn alone is ambigous because it's a key to 2 tables (stock,shipments) #and for that reason it must be specified on which table the SQL should check query = """SELECT shipment_id,ship_date,shipments.isbn,title FROM Shipments JOIN editions ON shipments.isbn = editions.isbn JOIN books ON editions.book_id = books.book_id WHERE customer_id = %s; """ % ID print("------------------------------------------------------------") try: self.cur.execute(query) print("Customer: %d | %s | %s" % (ID, fname, lname)) print("shipment_id, ship_date, isbn, title") self.print_answer() except (NameError, ValueError, TypeError, SyntaxError): print("Query execution failed.") return print("------------------------------------------------------------\n")
def shipments(self): # These input funtions are not correct so exceptions caught and handled. # ID should be hard typed to an integer # So think that they can enter: 1 OR 1=1 while True: try: ID = int(input("ID: ")) break except NameError: print "Please enter a number" # These names inputs are terrible and allow injection attacks. # So think that they can enter: Hilbert' OR 'a'='a fname = pgdb.escape_string(raw_input("First Name: ").strip()) lname = pgdb.escape_string(raw_input("Last Name: ").strip()) # THIS IS NOT RIGHT YOU MUST FIGURE OUT WHAT QUERY MAKES SENSE query = "SELECT first_name, last_name FROM customers WHERE customer_id = '%s';" %(ID) #NEED TO Catch excemptions ie bad queries (ie there are pgdb.someError type errors codes) try: self.cur.execute(query) except pg.ProgrammingError as detail: print "Something is not right... please try again" # NEED TO figure out how to get and test the output to see if the customer is in customers # test code here... # HINT: in python lists are accessed from 0 that is mylist[0] is the first element # also a list of a list (such as the result of a query) has two indecies so starts with mylist[0][0] result = self.cur.fetchone() if result: if result[0].lower() == fname.lower() and result[1].lower() == lname.lower(): # now the test is done print "Customer %d %s %s:"%(ID, fname, lname) # THIS IS NOT RIGHT YOU MUST PRINT OUT a listing of shipment_id,ship_date,isbn,title for this customer else: print "The ID does not match with the name you entered" return else: print "Sorry we could no find the ID in the database" return query = """SELECT shipment_id, ship_date, isbn, title FROM shipments NATURAL JOIN editions NATURAL JOIN books WHERE customer_id = %s"""%(ID) # YOU MUST CATCH EXCEPTIONS HERE AGAIN try: self.cur.execute(query) except pg.ProgrammingError as detail: print detail # Here the list should print for example: # Customer 860 Tim Owens: # shipment_id,ship_date,isbn,title # 510, 2001-08-14 16:33:47+02, 0823015505, Dynamic Anatomy self.print_answer()
def makeShipments(self): #THESE INPUT LINES ARE NOT GOOD ENOUGH # YOU NEED TO TYPE CAST/ESCAPE THESE AND CATCH EXCEPTIONS try: # Try to obtain the input. CID = int(input("cutomerID: ")) SID = int(input("shipment ID: ")) Sisbn = pgdb.escape_string(raw_input("isbn: ").strip()) Sdate = pgdb.escape_string(raw_input("Ship date: ").strip()) # THIS IS NOT RIGHT YOU MUST FORM A QUERY THAT HELPS query = "SELECT stock FROM stock WHERE isbn='%s'" % Sisbn except (NameError, ValueError, TypeError, SyntaxError) as detail: print detail.message.split('\n')[0] return print query # HERE YOU SHOULD start a transaction try: #YOU NEED TO Catch exceptions ie bad queries self.cur.execute(query) # HERE YOU NEED TO USE THE RESULT OF THE QUERY TO TEST IF THERE ARE # ANY BOOKS IN STOCK result = self.cur.fetchone() if not result: print("No books found with isbn '%s'" % Sisbn) return # YOU NEED TO CHANGE THIS TO SOMETHING REAL cnt = result[0] if cnt < 1: print("No more books in stock :(") return else: print "WE have the book in stock" query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % Sisbn print query #YOU NEED TO Catch exceptions and rollback the transaction self.cur.execute(query) print "stock decremented" query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % (SID, CID, Sisbn, Sdate) print query #YOU NEED TO Catch exceptions and rollback the transaction self.cur.execute(query) print "shipment created" # This ends the transaction (and starts a new one) self.conn.commit() except (pgdb.DatabaseError, pgdb.OperationalError) as error: print " Exception encountered while modifying table data." + error.message self.conn.rollback() print " Rolling back." return
def _getShipmentInfo(self): try: cid = int(input("customerID: ")) sid = int(input("shipment ID: ")) shipment_isbn = pgdb.escape_string((raw_input("isbn: ")).strip()) shipment_date = pgdb.escape_string(raw_input("Ship date: ")).strip() # THIS IS NOT RIGHT YOU MUST FORM A QUERY THAT HELPS query ="SELECT stock FROM stock WHERE isbn='%s'" % shipment_isbn except (NameError, ValueError, TypeError, SyntaxError) as e: print "Error in input" + str(e) # Not sure if it works, haven't tested return None #print "query=" + query result = (sid, cid, shipment_isbn, shipment_date, query) return result
def getnodes(self, ids): nodes = [] idstr = "" first = True if len(ids) == 0: idstr = "-1" for i in ids: if first: idstr += "%d" % (i) first = False else: idstr += ", %d" % (i) sql = "SELECT ID,DATA FROM %sNODES WHERE ID IN (%s)" % (self._prefix, pgdb.escape_string(idstr)) try: cursor = self._get_cur() cursor.execute(sql) row = cursor.fetchone() while row != None: node = cPickle.loads(str(row[1])) node.set_id(row[0]) nodes.append(node) row = cursor.fetchone() except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def __make_query(key, operator, pattern): if not key: return None # Translate keyed values. That function returns the input # value unchanged if there's no reason to translate anything. pattern = _coded_value(key, pattern) pattern = pgdb.escape_string(pattern) key = key.replace('"', r'\"') if '~' in operator: # 1. dot is not a wildcard here but rather a literal dot pattern = pattern.replace('.', '\.') # 2. a * indicates to not do a substring search if '*' in pattern: pattern = pattern.replace('*', '.*') pattern = '^%s$' % (pattern) # 3. empty pattern means search for everything if pattern == '': pattern = '.*' return "\"%s\" %s '%s'" % (key, operator, pattern)
def quote(self, value): """ Returns quoted version of the specified value. """ # The pgdb._quote function is good enough for general SQL # quoting, except for array types. if isinstance(value, (list, tuple, set)): return "ARRAY[%s]" % ", ".join(map (PostgreSQL.quote_string, value)) else: ret = None if isinstance(value, str): ret = "'" + pgdb.escape_string( value ) + "'" else: ret = pgdb.escape_string( str(value) ) return ret
def getnodes(self, ids): nodes = [] idstr = "" first = True if len(ids) == 0: idstr = "-1" for i in ids: if first: idstr += "%d" % (i) first = False else: idstr += ", %d" % (i) sql = "SELECT ID,DATA FROM %sNODES WHERE ID IN (%s)" % ( self._prefix, pgdb.escape_string(idstr)) try: cursor = self._get_cur() cursor.execute(sql) row = cursor.fetchone() while row != None: node = cPickle.loads(str(row[1])) node.set_id(row[0]) nodes.append(node) row = cursor.fetchone() except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def escape(self, str): if self.dbinfo.dbtype == T_MYSQL: return MySQLdb.escape_string(str) elif self.dbinfo.dbtype == T_PGDB: return pgdb.escape_string(str) return str
def makeShipments(self): #THESE INPUT LINES ARE NOT GOOD ENOUGH # YOU NEED TO TYPE CAST/ESCAPE THESE AND CATCH EXCEPTIONS while True: try: CID = int(raw_input("customerID: ")) break except (NameError, ValueError, TypeError, SyntaxError): print 'Input must be a number.' while True: try: SID = int(input("shipment ID: ")) break except (NameError, ValueError, TypeError, SyntaxError): print 'Input must be a number.' Sisbn = pgdb.escape_string(raw_input("isbn: ").strip()) while True: try: Sdate = datetime.datetime.strptime( raw_input("Ship date: ").strip(), '%Y-%m-%d') break except ValueError: print "Incorrect data format, should be YYYY-MM-DD" # THIS IS NOT RIGHT YOU MUST FORM A QUERY THAT HELPS query = "SELECT * FROM shipments WHERE customer_id = '%i' AND shipment_id = '%i'" % ( CID, SID) print query # HERE YOU SHOULD start a transaction #YOU NEED TO Catch exceptions ie bad queries self.cur.execute(query) #HERE YOU NEED TO USE THE RESULT OF THE QUERY TO TEST IF THER ARE #ANY BOOKS IN STOCK # YOU NEED TO CHANGE THIS TO SOMETHING REAL cnt = 0 if cnt < 1: print("No more books in stock :(") return else: print "WE have the book in stock" query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn) print query #YOU NEED TO Catch exceptions and rollback the transaction self.cur.execute(query) print "stock decremented" query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % ( SID, CID, Sisbn, Sdate) print query #YOU NEED TO Catch exceptions and rollback the transaction self.cur.execute(query) print "shipment created" # This ends the transaction (and starts a new one) self.conn.commit()
def shipments(self): # These input funtions are not correct so exceptions caught and handled. # ID must be integer while True: try: ID = int(raw_input("customerID: ")) break except (NameError, ValueError, TypeError, SyntaxError): print("That was not a number, try again.") # Escape_string to avoid injection attacks. fname = pgdb.escape_string(raw_input("First Name: ").strip()) lname = pgdb.escape_string(raw_input("Last Name: ").strip()) #---Ask for the user query = "SELECT * FROM customers WHERE customer_id = '%i' AND first_name ='%s' AND last_name='%s'" % ( ID, fname, lname) print query #NEED TO Catch excemptions ie bad queries (ie there are pgdb.someError type errors codes) try: self.cur.execute(query) except (NameError, ValueError, TypeError, SyntaxError): print('Opps, something went wrong with the query..') #--Check if user exsists (if there is any query-response) try: response = self.cur.fetchall() # Error if no tuples in response response[0][0] print('User found!') query = "SELECT shipment_id, ship_date, shipments.isbn, title FROM shipments, customers, books, editions WHERE shipments.isbn = editions.isbn AND editions.book_id = books.book_id AND shipments.customer_id = customers.customer_id AND shipments.customer_id = '%i' AND first_name = '%s' AND last_name = '%s';" % ( ID, fname, lname) except (TypeError, IndexError): print "Sorry, user not found.." return #--Ask for shipments try: self.cur.execute(query) except (NameError, ValueError, TypeError, SyntaxError): print('Opps, something went wrong with the query..') self.print_answer()
def convList2Array(list): """ Converts python list to pg array. """ array = '{' for item in list: array += "'%s'," % pgdb.escape_string(item) # trim ending ',' if len(array) > 1: array = array[0:-1] array += '}' return array
def convList2Array(list): """ Converts python list to pg array. """ array = '{' for item in list: if isinstance(item, str): item = pgdb.escape_string(item) array += "%s," % str(item) # trim ending ',' if len(array) > 1: array = array[0:-1] array += '}' return array
def get_tiles_to_flag(self, flaggingname=None): if (self.db is None): logger.error('Cannot retrieve flagging information without database connection') return if (flaggingname is not None): flagged_tiles = dbobj.execute('select flagged_tiles from tile_flags where starttime < %d and stoptime > %d and name = %s' % (self.gpstime, self.gpstime, pgdb.escape_string(flaggingname)), db=self.db) else: flagged_tiles = list(itertools.chain.from_iterable(dbobj.execute('select flagged_tiles from tile_flags where starttime < %d and stoptime > %d' % (self.gpstime, self.gpstime), db=self.db))) # logical or of all of the flagging sets that are returned for x in flagged_tiles: for z in x.replace('{','').replace('}','').split(','): self.tiles_to_flag.add(z) logger.info('Will flag tiles %s' % list(self.tiles_to_flag))
def check_name_avail(name): conn = _get_db_conn() cur = conn.cursor() cur.execute("SELECT name FROM vmmachines WHERE name = '" + MySQLdb.escape_string(name) + "'") row = cur.fetchone() if row is None: pg_conn = _get_pg_db_conn() pg_cur = pg_conn.cursor() pg_cur.execute("SELECT hostname FROM hosts WHERE hostname = '" + pgdb.escape_string(name) + "'") row = pg_cur.fetchone() if row is not None: # Found in PG Start DB, FAIL return {'status': 'OK', 'avail':0} #_release_db_conn() # NOT Found in Start OR vm list, OK return {'status': 'OK', 'avail': 1} #_release_db_conn() # Found Somewhere, FAIL return {'status': 'OK', 'avail': 0}
def remove(self): """Removes tuples. Will query the user for the information required to identify a tuple. If the filter field is left blank, no filters will be used.""" #First we should get which table to remove from table = pgdb.escape_string(raw_input("Choose table: ")) print table #Then filter (as seen in the select func above, i.e. the where clause) filters = raw_input("Apply filters: ") # try/define query, inspired by line 119--122 try: query = """DELETE FROM %s%s;""" % (table, "" if filters =="" else " WHERE %s" % filters) except (NameError,ValueError, TypeError,SyntaxError): print " Bad input." return print(query) #execute the query (line 128) self.cur.execute(query)
def sync(filename): for line in open(filename,'r').readlines()[1:]: elems = line.strip().split('~') artist = "" title = "" if len(elems) == 2: (artist,title) = elems if artist != 'Various': cursor.execute("select records.recordnumber,author,title from records,track,groops,lineupset,lineup where records.recordnumber = track.recordnumber and track.trackrefnumber = lineupset.tracknumber and lineupset.lineupnumber = lineup.lineupnumber AND lineup.groopnumber = groops.groopnumber and lower(records.title) = '" + pgdb.escape_string(title.lower()) + "' AND lower(groops.show_name) = '" + pgdb.escape_string(artist.lower()) + "'") else: cursor.execute("select recordnumber from records where author = '" + pgdb.escape_string(artist) + "' AND title = '" + pgdb.escape_string(title) + "'") row = cursor.fetchone() seen = False done = [] while row != None: if not seen: if (artist,title) not in done: print artist,title,"(" + filename + ")" seen = True if row[0] not in done: print "\t",row[0] done.append(row[0]) row = cursor.fetchone()
def makeShipments(self): #---Get customer id, shipment id, isbn and shipment date. Avoid insertons and catch errors. #---Get customer id while True: try: CID = int(raw_input("customerID: ")) break except (NameError, ValueError, TypeError, SyntaxError): print 'Input must be a number.' #---Get shipment ID while True: try: SID = int(input("shipment ID: ")) break except (NameError, ValueError, TypeError, SyntaxError): print 'Input must be a number.' #---Get isbn Sisbn = pgdb.escape_string(raw_input("isbn: ").strip()) #---Get date or set today as default if no input while True: try: Sdate = pgdb.escape_string(raw_input("Ship date: ").strip()) if Sdate == '': Sdate = datetime.datetime.now() Sdate = '%s-%s-%s' % (Sdate.year, Sdate.month, Sdate.day) Sdate = datetime.datetime.strptime(Sdate, '%Y-%m-%d') break except ValueError: print "Incorrect data format, should be YYYY-MM-DD" #---Create query to get stock of choosen book query = "SELECT stock FROM stock WHERE isbn = '%s';" % (Sisbn) print query #---Starts a new transaction (nothing to commit) - Might be unneccesary since there is a commit at the end aswell self.conn.commit() #---Get stock of choosen book: try: self.cur.execute(query) stock = self.cur.fetchall()[0][0] except (NameError, ValueError, TypeError, SyntaxError, pgdb.DatabaseError): print('Opps, something went wrong with the query..') return #---Check if book is in stock: if stock < 1: print("No more books in stock :(") return else: print "WE have the book in stock" #---Create query for stock decreasement query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn) print query #---Decrease stock but catch exceptions and rollback the transaction if there's any problem try: self.cur.execute(query) print "Stock decremented" except (NameError, ValueError, TypeError, SyntaxError, pgdb.DatabaseError): print('Opps, something went wrong with the query..') self.conn.rollback return #---Create query for new shipment query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % ( SID, CID, Sisbn, Sdate) print query #---Add to shipments but catch exceptions and rollback the transaction if there's any problem try: self.cur.execute(query) print "Shipment created" except (NameError, ValueError, TypeError, SyntaxError): print('Opps, something went wrong with the query..') self.conn.rollback return except (pgdb.DatabaseError): print('ERROR! Shipment ID already exsits, try again..') self.conn.rollback return # This ends the transaction (and starts a new one) self.conn.commit()
def find(self, title): self.cur.execute("SELECT tile, stock FROM books NATURAL JOIN editions NATURAL JOIN stock WHERE title='" + pgdb.escape_string(title) + "';")
def shipments(self): # These input funtions are not correct so exceptions caught and handled. qFromS = "" # ID should be hard typed to an integer # So think that they can enter: 1 OR 1=1 try: ID = int(raw_input("customerID: ")) #castar till int except (NameError, ValueError, TypeError, SyntaxError): print( "Something went wrong in your input of ID, try starting over.." ) return # These names inputs are terrible and allow injection attacks. # So think that they can enter: Hilbert' OR 'a'='a fname = pgdb.escape_string(raw_input("First Name: ").strip( )) #escape_string - tar bort alla "farliga" tecken lname = pgdb.escape_string(raw_input( "Last Name: ").strip()) #slipper ocksa skriva ex. 'title' # THIS IS NOT RIGHT YOU MUST FIGURE OUT WHAT QUERY MAKES SENSE try: query = "SELECT first_name, last_name FROM customers WHERE customer_id=%s" % ID except (NameError, ValueError, TypeError, SyntaxError): print("Something went wrong in your input, try starting over..") #"100 OR 1=1" #WHERE customer_id = 7216784 OR 1=1; print query #NEED TO Catch excemptions ie bad queries (ie there are pgdb.someError type errors codes) try: self.cur.execute(query) print "SUCCESS... Welcome %s %s" % (fname, lname) except pgdb.Error as e: print("sry cant find you in DB") #-------- SETTING VAR TO LOWERCASE TO MAKE COMPARE EASIER ------------------ fnameL = fname.lower() lnameL = lname.lower() #---------------------------------------------------------------------------- tupleFromS = self.cur.fetchone( ) #Getting matching tuple from Shipments according to ID printCols = "shipment_id, ship_date, isbn, title" #Declaring new variable for SELECTION and final print of res if tupleFromS[0].lower() == fnameL and tupleFromS[1].lower() == lnameL: print "SHOW EVERYTHING FROM SHIPMENTS" try: qFromS = "SELECT " + printCols + " FROM (SELECT * FROM editions NATURAL JOIN (SELECT * FROM shipments NATURAL JOIN books) AS t1) AS t2 WHERE customer_id = %s" % ID except (NameError, ValueError, TypeError, SyntaxError): print "Something went wrong looking up your shipments.." try: self.cur.execute(qFromS) except (pgdb.Error) as e: print e print "Looks like no match can be found.." else: print "sry cannot find ID in DB" # NEED TO figure out how to get and test the output to see if the customer is in customers # test code here... # HINT: in pyton lists are accessed from 0 that is mylist[0] is the first element # also a list of a list (such as the result of a query) has two indecies so starts with mylist[0][0] # now the test is done #print "good name" # THIS IS NOT RIGHT YOU MUST PRINT OUT a listing of shipment_id,ship_date,isbn,title for this customer #query ="SELECT shipment_id, ship_date, isbn, title FROM shipments WHERE customer_id IN(%s)" %....... # YOU MUST CATCH EXCEPTIONS HERE AGAIN #self.cur.execute(query) # Here the list should print for example: # Customer 860 Tim Owens: # shipment_id,ship_date,isbn,title # 510, 2001-08-14 16:33:47+02, 0823015505, Dynamic Anatomy #--------- PRINTING ACCORDING TO EX ------------------------------ print("\n-----------------------------------") print("Customer " + str(ID) + " " + fname + " " + lname) print(printCols) self.print_answer() print("-----------------------------------\n")
def Escape(query_str): return pgdb.escape_string(query_str)
def safe_input(self,str): return pgdb.escape_string(raw_input(str))
def makeShipments(self): #THESE INPUT LINES ARE NOT GOOD ENOUGH # YOU NEED TO TYPE CAST/ESCAPE THESE AND CATCH EXCEPTIONS while True: try: CID = int(raw_input("customerID: ")) break except (NameError, ValueError, TypeError, SyntaxError): print 'Input must be a number.' while True: try: SID = int(input("shipment ID: ")) break except (NameError, ValueError, TypeError, SyntaxError): print 'Input must be a number.' Sisbn = pgdb.escape_string(raw_input("isbn: ").strip()) #---Check date format or set today as default if no input while True: try: Sdate = pgdb.escape_string(raw_input("Ship date: ").strip()) if Sdate == '': Sdate = datetime.datetime.now() Sdate = '%s-%s-%s' % (Sdate.year, Sdate.month, Sdate.day) Sdate = datetime.datetime.strptime(Sdate, '%Y-%m-%d') break except ValueError: print "Incorrect data format, should be YYYY-MM-DD" # Check current shipments query = "SELECT stock FROM stock WHERE isbn = '%s';" % (Sisbn) print query # HERE YOU SHOULD start a transaction #---not done this yet... #---Get stock of choosen book: try: self.cur.execute(query) stock = self.cur.fetchall()[0][0] except (NameError, ValueError, TypeError, SyntaxError): print('Opps, something went wrong with the query..') # print stock #---Check if book is in stock: if stock < 1: print("No more books in stock :(") return else: print "WE have the book in stock" query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn) print query #YOU NEED TO Catch exceptions and rollback the transaction self.cur.execute(query) print "stock decremented" query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % ( SID, CID, Sisbn, Sdate) print query #YOU NEED TO Catch exceptions and rollback the transaction self.cur.execute(query) print "shipment created" # This ends the transaction (and starts a new one) self.conn.commit()
def create_vm(uname, hname, dsize, ssize, imagename, mac, allocid, mem, owner, start_register, start_range): user = get_user_info(uname); conn = _get_db_conn(); cur = conn.cursor(); adsize = None assize = None amac = None amem = None aowner = None usingPrealloc = 0 if allocid == 'new': allocid = None # Check if we got an alloc id or else that we're an admin if (allocid == None and user['admin'] != 1): #_release_db_conn() return "{status: 'FAIL', reason: 'Not an admin. Must use preallocated VM'}"; # Get the allocation we're going to use, if we're using one. if (allocid != None): cur.execute("SELECT mac, disk, swap, mem, owner FROM allocvmmachines WHERE id = " + str(int(allocid)) + ";"); row = cur.fetchone() if (row != None): usingPrealloc = 1 amac = row[0] adsize = row[1] assize = row[2] amem = row[3] aowner = row[4] if (usingPrealloc == 0 and user['admin'] != 1): #_release_db_conn() return "{status: 'FAIL', reason: 'Not authorized to create new VM'}" # Check that the base image name is legitimate cur.execute("SELECT name FROM images WHERE name = '" + MySQLdb.escape_string(imagename) + "'") row = cur.fetchall() if (row is None): #_release_db_conn() return {'status': 'OK', 'reason': 'image name is not valid'} # If we're using a pre-allocated VM, make sure un-changeable settings aren't changed. if (usingPrealloc == 1 and ((amac != None and amac != mac) or (adsize != None and int(adsize) != int(dsize)) or (assize != None and int(assize) != int(ssize)) or (amem != None and int(amem) != int(mem)) or (aowner != None and aowner != owner))): #_release_db_conn() return "{status: 'FAIL', reason: 'Locked attributes do not match amac:" + str(amac) + " adsize:" + str(adsize) + " assize:" + str(assize) + " amem:" + str(amem) + " aowner:" + str(aowner) + "'}" # If the user chose to do a registration on start, register it if (start_register == 'on'): # DO Network Registration pg_conn = _get_pg_db_conn() pg_cur = pg_conn.cursor() # Figure out the IP range we want to deal with. if (start_range == 'userrack'): first_ip = '129.21.50.176' last_ip = '129.21.60.249' elif (start_range == 'projects'): first_ip = '129.21.50.1' last_ip = '129.21.50.64' pg_cur.execute("SELECT ip_address FROM host_cache WHERE ip_address BETWEEN '" + first_ip + "' AND '" + last_ip + "' AND in_use = false ORDER BY ip_address LIMIT 1") row = pg_cur.fetchone() if (row is not None): ip_addr = row[0] pg_cur.execute("UPDATE host_cache SET in_use = true WHERE ip_address = '" + str(ip_addr) +"'") try: pg_cur.execute("INSERT INTO hosts (hardware_address, ip_address, hostname, username, auth_user) VALUES ('" + pgdb.escape_string(mac) + "', '" + ip_addr + "', '" + pgdb.escape_string(hname) + "', '" + pgdb.escape_string(owner) + "', 'adinardi')") except pgdb.DatabaseError: # END HERE. Return failure since we can't register pg_conn.rollback() #_release_db_conn() return {'status': 'FAIL', 'reason': 'Hostname or MAC address already registered.'} pg_cur.execute("INSERT INTO process_queue (event_class) VALUES ('RELDHCP')") pg_conn.commit() else: return {'status': 'FAIL', 'reason': 'No more IP addresses available to be registered'} # ADD MYSQL DB WRITE HERE. cur.execute("INSERT INTO vmmachines (name, owner, mac, disk, mem, swap) VALUES('" + MySQLdb.escape_string(hname) + "', '" + MySQLdb.escape_string(owner) + "', '" + MySQLdb.escape_string(mac) + "', '" + MySQLdb.escape_string(dsize) + "', '" + MySQLdb.escape_string(mem) + "', '" + MySQLdb.escape_string(ssize) + "')"); vmid = conn.insert_id() cur.execute("INSERT INTO vmdisks (file, device, vmmachineid) VALUES ('disk', 'xvda2', " + str(vmid) + ")") cur.execute("INSERT INTO vmdisks (file, device, vmmachineid) VALUES ('swap', 'xvda1', " + str(vmid) + ")") if (usingPrealloc == 1): cur.execute("DELETE FROM allocvmmachines WHERE id = " + str(int(allocid))) # Write Config FILE = open('/mnt/vms/configs/new/' + hname + '.defs', "w"); FILE.write("-n " + hname + " -d " + dsize + " -s " + ssize + " -b " + imagename + " -m " + mac + " -e " + mem); FILE.close(); #_release_db_conn() return "{status: 'OK'}"
def shipments(self): print "In shipments()" # These input funtions are not correct so exceptions caught and handled. # ID should be hard typed to an integer # So think that they can enter: 1 OR 1=1 while True: try: ID = int(raw_input("customerID: ")) break except ValueError: print "customerID has to be an integer!" # These names inputs are terrible and allow injection attacks. # So think that they can enter: Hilbert' OR 'a'='a #fname = (raw_input("First Name: ").strip()) #lname = raw_input("Last Name: ").strip() fname = pgdb.escape_string((raw_input("First Name: ").strip())) lname = pgdb.escape_string(raw_input("Last Name: ").strip()) fname = "'%s'" % fname lname = "'%s'" % lname # THIS IS NOT RIGHT YOU MUST FIGURE OUT WHAT QUERY MAKES SENSE #query = "SELECT something FROM somewhere WHERE something" query = "SELECT customer_id, last_name, first_name FROM customers " query += "WHERE customer_id={ID} AND last_name={lname} AND first_name={fname};".format( ID=ID, fname=fname, lname=lname) #print "query='%s'\n" % query #NEED TO Catch excemptions ie bad queries (ie there are pgdb.someError type errors codes) try: self.cur.execute(query) #print "The query executed successfully!" except pgdb.DatabaseError as e: print "There was an exception executing the query '%s': %s" %\ (query, str(e)) self.exit() if len(fname) >= 2: fname = fname[1:-1] if len(lname) >= 2: lname = lname[1:-1] # NEED TO figure out how to get and test the output to see if the customer is in customers # test code here... # HINT: in pyton lists are accessed from 0 that is mylist[0] is the first element # also a list of a list (such as the result of a query) has two indecies so starts with mylist[0][0] # now the test is done if self.cur.fetchone(): #print "Good name!" pass else: print "Couldn't find the customer {lname}, {fname} with id {id}.".format( id=ID, fname=fname, lname=lname) print "Check your ID, first name or last name (consider the case!)" return # THIS IS NOT RIGHT YOU MUST PRINT OUT a listing of shipment_id,ship_date,isbn,title for this customer #query ="SELECT something FROM somewhere WHERE conditions" #query ="SELECT shipment_id, ship_date, isbn, title FROM shipments WHERE customer_id={id}".format(id=ID) #query ="SELECT shipment_id, ship_date, isbn, title FROM shipments, books WHERE customer_id={id}".format(id=ID) query = """SELECT shipment_id, ship_date, isbn, title FROM shipments NATURAL JOIN books NATURAL JOIN editions WHERE customer_id={id}""".format(id=ID) # YOU MUST CATCH EXCEPTIONS HERE AGAIN try: self.cur.execute(query) #print "The query executed successfully!" except pgdb.DatabaseError as e: print "There was an exception executing the query '%s': %s" %\ (query, str(e)) self.exit() values = self.cur.fetchall() print "Customer {id} {fname} {lname}:".format( id=ID, #fname=pgdb.escape_string(fname), #lname=pgdb.escape_string(lname)) fname=fname, lname=lname) #print "\tshipment_id\tship_date\tisbn\ttitle" print "\tship_id\tship_date\t\tisbn\t\ttitle" for shipment in values: print "\t" + "\t".join(map(str, shipment))
def escapeArrayElement(query_str): # also escape backslashes and double quotes, in addition to the doubling of single quotes return pgdb.escape_string(query_str.encode(errors='backslashreplace')).decode(errors='backslashreplace').replace('\\','\\\\').replace('"','\\"')
def makeShipments(self): #THESE INPUT LINES ARE NOT GOOD ENOUGH #YOU NEED TO TYPE CAST/ESCAPE THESE AND CATCH EXCEPTIONS try: CID = int(raw_input("customer ID: ")) SID = int(input("shipment ID: ")) Sisbn = pgdb.escape_string(raw_input("isbn: ")).strip() Sdate = pgdb.escape_string(raw_input("Ship date: ").strip()) #QUERY THAT HELPS query = "SELECT stock FROM stock WHERE isbn = '%s';" % (Sisbn) print query except (NameError, ValueError, TypeError, SyntaxError) as e: print "Your input is wrong: " + e return #HERE YOU SHOULD start a transaction #YOU NEED TO Catch exceptions ie bad queries #---------------- TRANSACTION ------------------------- try: self.cur.execute(query) stockInfo = self.cur.fetchall() #matrix [[31]] print stockInfo print stockInfo[0][ 0] #printing what is in our first row&col of cursor i.e. stock siffra for stock in stockInfo: print "STOOOOOCK FOR ISBN: " + str(stock) cnt = 0 if stockInfo: cnt = stockInfo[0][0] if cnt < 1: print "No more books in stock :(" return else: print "We have the book in stock (" + str( cnt) + " books left)" else: print "Sry, no more books w that ISBN in stock" except (pgdb.Error) as e: print e print "Sry cannot find any stock..." return #HERE YOU NEED TO USE THE RESULT OF THE QUERY TO TEST IF THER ARE #ANY BOOKS IN STOCK # YOU NEED TO CHANGE THIS TO SOMETHING REAL #ev en till check av stock>0 har? query = """UPDATE stock SET stock=stock-1 WHERE isbn='%s';""" % (Sisbn) print query #YOU NEED TO Catch exceptions and rollback the transaction try: self.cur.execute(query) self.conn.commit() print "stock decremented" except pgdb.Error as e: self.conn.rollback() print e return query = """INSERT INTO shipments VALUES (%i, %i, '%s','%s');""" % ( SID, CID, Sisbn, Sdate) print query #YOU NEED TO Catch exceptions and rollback the transaction try: self.cur.execute(query) print("\n---------------") print "shipment created" print("---------------\n") except pgdb.Error as e: self.conn.rollback() print e return # This ends the transaction (and starts a new one) self.conn.commit()
def escape_pg_string(string): return "'" + pgdb.escape_string(string) + "'"
def make_instr_config(self, flaggingname=None): """ based on a recipe from Bryna Hazelton 2012-05 """ if (self.gpstime is None): logger.error('Must supply gpstime for determining instr_config') return if (self.db is None): logger.error('Cannot retrieve configuration information without database connection') return self.get_tiles_to_flag(flaggingname=flaggingname) # figure out which receivers are active active_receivers = dbobj.execute('select receiver_id from receiver_info where active = true and begintime < %d and endtime > %d' % (self.gpstime,self.gpstime), db=self.db) self.active_receivers = [x[0] for x in active_receivers] logger.info('Found active receivers %s' % self.active_receivers) if len(self.active_receivers)<1: logger.error('No active receivers found for gpstime=%d' % self.gpstime) return if 102 in self.active_receivers: # the offset for mapping from each Rx to the slot numbers rxslotoffset={1:0,102:16,3:32,4:48} else: rxslotoffset={1:0,2:16,3:32,4:48} slotispowered={} for receiver in self.active_receivers: slotispowered[int(receiver)]=True try: slot_power=dbobj.execute('select slot_power from obsc_recv_cmds where rx_id=%s and starttime=%d' % ( pgdb.escape_string(receiver),self.gpstime), db=self.db)[0][0] # if any of them is False if 'f' in slot_power: slotispowered[int(receiver)]=False except: logger.warning('Unable to retrieve slot power information for gpstime=%d, Rx=%s' % (self.gpstime,receiver)) pass # loop over each input # figure out which receiver and tile it connects to for inputnumber in xrange(_NINP): for receiver in rxslotoffset.keys(): # get the offset if rxslotoffset[receiver]==(inputnumber / _INPUTSPERRX)*_INPUTSPERRX: break slotoffset=rxslotoffset[receiver] slot=self._inptoslot[inputnumber-slotoffset] pol=self._inptopol[inputnumber-slotoffset] tile=dbobj.execute('select tile from tile_connection where receiver_id = %s and receiver_slot = %d and begintime < %d and endtime > %d' % ( receiver,slot,self.gpstime,self.gpstime), db=self.db)[0][0] cable_electrical_length=dbobj.execute('select physlength from cable_info ci inner join tile_connection tc on ci.name = tc.cable_name where tc.tile=%s and ci.begintime < %d and ci.endtime > %d and tc.begintime < %d and tc.endtime > %d' % ( pgdb.escape_string(tile),self.gpstime,self.gpstime, self.gpstime,self.gpstime), db=self.db)[0][0] # flagging flagthisinput=False if not slotispowered[int(receiver)]: flagthisinput=True if tile in self.tiles_to_flag or str(tile) in self.tiles_to_flag: flagthisinput=True t=tile_config(tile=tile, receiver=receiver, inputnumber=inputnumber, length=cable_electrical_length, pol=pol.upper(), slot=slot, flag=flagthisinput) self.inputs[inputnumber]=t
def update_stock(self, title, stock): self.cur.execute("UPDATE stock SET stock='" + pgdb.escape_string(stock) + "' WHERE title='" + pgdb.escape_string(title) + "';")