def get_where(cursor, class_type, orderby='id', fields="*", start=0, limit=0, test_mode=False, **wheres): where = "" if wheres != {}: where = [] for k, v in wheres.items(): if v == None: continue # Array handler if type(v) in (list, tuple, set): if len(v) == 0: continue safe_vals = [] for i in v: if type(i) == str: safe_vals.append(database_f.escape(i)) else: safe_vals.append(str(i)) where.append("%s IN (%s)" % (k, ",".join(safe_vals))) # Non array elif type(v) == str: where.append("\"%s\" = '%s'" % (k, database_f.escape(v))) elif type(v) in (int, float): where.append("\"%s\" = '%s'" % (k, v)) else: raise Exception("No handler for type %s" % type(v)) where = " AND ".join(where) return _query(cursor, class_type, fields=fields, where=where, orderby=orderby, start=start, limit=limit, test_mode=test_mode)
def main(cursor): table_name = common_f.get_val("table", "") field_name = common_f.get_val("field", "") new_value = common_f.get_val("value", "").strip() where = common_f.get_val("where", "") print_query = common_f.get_val("p", False) silent = common_f.get_val("silent", False) new_value_db = new_value try: if new_value_db != float(new_value_db) and new_value_db != int(new_value_db): new_value_db = "'%s'" % database_f.escape(new_value_db) except Exception as e: new_value_db = "'%s'" % database_f.escape(new_value_db) query = """UPDATE {table} SET {field} = {value} WHERE {where};""".format( table=table_name, field=field_name, value=new_value_db, where=where ) try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n", ""), query)) if print_query: return query if not silent: return new_value else: return ""
def get_user(cursor, username, password, from_cookie=False): u = None query = """SELECT * FROM users WHERE lower(username) = '{}'""".format(database_f.escape(username)) try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) for row in cursor: u = User(**row) if u == None: return "Username not found" # Do we need to hash the password? h_password = password if not from_cookie: h_password = encode_password(password, u.salt, passes=1) # Is the password correct? if h_password != u.password: return "Incorrect password" # It's correct, return the user instance common_f.cache['user'] = u return u
def get_one(cursor, class_type, where="", test_mode=False, fields="*", orderby="", **wheres): if where == "" and wheres != {}: where = [] for k, v in wheres.items(): if type(v) == str: where.append("%s = '%s'" % (k, database_f.escape(v))) else: where.append("%s = '%s'" % (k, v)) where = " AND ".join(where) return _query(cursor, class_type, fields=fields, where=where, limit=1, orderby=orderby, test_mode=test_mode)
def test_escape(self): self.test_targets.append(database_f.escape) for str_in, expected in self.escape_vals: self.assertEqual(database_f.escape(str_in), expected)
def main(cursor, query_filter=""): query_filter = common_f.get_val("filter", query_filter) if query_filter == "": log_dict = common_q.get_all(cursor, user_log.UserLog, orderby="access_time DESC") elif query_filter == "today": today = datetime.date.today() today = time.mktime(today.timetuple()) log_dict = common_q.get_all( cursor, user_log.UserLog, where="access_time >= %d" % today, orderby="access_time DESC" ) else: log_dict = common_q.get_all( cursor, user_log.UserLog, where="mode='%s'" % database_f.escape(query_filter), orderby="access_time DESC" ) output = [] if len(log_dict) > 0: user_dict = common_q.get_all(cursor, user.User) user_dict[-1] = user.User(username="******") output.append( """<table border="0" cellspacing="0" cellpadding="5" style="width:100%;"> <tr class="row2"> <th>Date</th> <th>Mode</th> <th>User</th> <th>Load time</th> <th colspan="2"> </th> </tr> """ ) i = 1 for log_id, the_log in log_dict.items(): i += 1 the_date = the_log.access_time output.append( """ <tr class="row{row}"> <td>{date}</td> <td>{page}</td> <td>{user}</td> <td>{load_time}</td> <td class="block_cell"><a href="web.py?mode=edit_log&log={log_id}">View</a></td> <td class="block_cell"><a href="web.py?mode=edit_log&log={log_id}&sub_mode=delete">Delete</a></td> </tr>""".format( log_id=log_id, row=i % 2, load_time=round(the_log.load_time, 4), date=common_f.display_date(the_date, "%d of %B at %H:%M"), page="No mode specified" if the_log.page == "" else the_log.page, user=user_dict[the_log.user_id].username, ) ) output.append("</table>") else: output.append("<div style='padding:10px;'>No logs found</div>") return "".join(output)
def _query(cursor, class_type, fields="*", where = '', orderby = '', start=0, limit=0, test_mode=False): if type(class_type) == dict: table = class_type['Name'] else: table = class_type.table_info['Name'] query = """SELECT {fields} FROM {table}""".format( fields = ",".join(fields), table = table, ) # Where if where != '': query += " WHERE {}".format(where) # Order by if orderby != '': query += " ORDER BY {}".format(database_f.escape(orderby)) # Limit stuff if start > 0 and limit > 0: query += " LIMIT {}, {}".format(start, limit) if start > 0 and limit < 1: query += " LIMIT 0, {}".format(limit) if start < 1 and limit > 0: query += " LIMIT {}".format(limit) # Test mode? if test_mode: return query # Run query try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n",""), query)) # Get results, just 1 field? We want to return a list if len(fields) == 1 and type(fields) in (list, tuple): results = [] field_name = fields[0] for row in cursor: results.append(row[field_name]) else: if "id" in fields: results = collections.OrderedDict() for row in cursor: results[row['id']] = row elif fields == "*": results = collections.OrderedDict() for row in cursor: results[row['id']] = class_type(**row) else: raise Exception("") results = [] for row in cursor: results.append(row) # No results? if len(results) == 0: if limit == 1: return None else: return {} # If limit is 1 we simple return the first item in our results if limit == 1: if len(fields) == 1 and type(fields) in (list, tuple): return results[0] else: return results[list(results.keys())[0]] return results
def main(cursor, query_filter=""): query_filter = common_f.get_val("filter", query_filter) if query_filter == "": error_dict = common_q.get_all(cursor, error.Error, where="fixed = False", orderby="timestamp DESC") elif query_filter == "today": today = datetime.date.today() today = time.mktime(today.timetuple()) error_dict = common_q.get_all( cursor, error.Error, where="timestamp >= %d AND fixed = False" % today, orderby="timestamp DESC" ) else: error_dict = common_q.get_all( cursor, error.Error, where="mode='%s' and fixed = False" % database_f.escape(query_filter), orderby="timestamp DESC", ) output = [] if len(error_dict) > 0: user_dict = common_q.get_all(cursor, user.User) user_dict[-1] = user.User(username="******") output.append( """<table border="0" cellspacing="0" cellpadding="5" style="width:100%;"> <tr class="row2"> <th>Date</th> <th>Mode</th> <th>Func Call</th> <th>Type</th> <th>User</th> <th colspan="2"> </th> </tr> """ ) i = 1 for error_id, the_error in error_dict.items(): i += 1 the_date = the_error.timestamp output.append( """ <tr class="row{row}" id="row{error_id}"> <td>{date}</td> <td>{mode}</td> <td>{function_call}</td> <td>{etype}</td> <td>{user}</td> <td class="block_cell"><a href="web.py?mode=edit_error&error={error_id}">View</a></td> <td class="block_cell"><a href="#" onclick="{onclick}">Fix</a></td> </tr>""".format( error_id=error_id, row=i % 2, etype=the_error.exception_type, date=common_f.display_date(the_date, "%d of %B at %H:%M"), mode="No mode specified" if the_error.mode == "" else the_error.mode, function_call="" if the_error.function_call == "" else the_error.function_call, user=user_dict[the_error.user_id].username, onclick="""$('#ajax_target').load('web.py', {'mode':'edit_error', 'error':'%d', 'sub_mode':'fix'}); $('#row%d').hide(); return false;""" % (error_id, error_id), ) ) output.append("</table>") else: output.append("<div style='padding:10px;'>No errors found</div>") modes = {} # Select all the groups possible query = """SELECT mode FROM errors GROUP BY mode""" try: cursor.execute(query) except Exception as e: raise Exception("Database error: %s\nQuery: %s" % (str(e.args[0]).replace("\n", ""), query)) for row in cursor: modes[row["mode"]] = row["mode"] page_data["Rows"] = len(error_dict) page_data["Title"] = "Error list (%d)" % len(error_dict) page_data[ "Filters" ] = """ <form action="web.py" method="get" accept-charset="utf-8" style="float: left;"> <a href="web.py?mode=list_errors">All errors</a> <a href="web.py?mode=list_errors&filter=today">Todays errors</a> <input type="hidden" name="mode" value="list_errors" /> {} <input type="submit" value="Sort by mode" /> </form> """.format( html_f.option_box("filter", modes, selected=query_filter) ) return "".join(output)