def registration_page():
"""Page where new user accounts are made."""
try:
message = None
if request.method == 'POST':
username = request.form['username']
password = sha256_crypt.encrypt(
(str(request.form['password']))) # taking password and hashing it for protection
c, conn = connection()
if is_username_exist(escape_string(username)) > 0: # checking if user exists
message = 'That username is already taken, please choose anot\
her'
return render_template('UserAccount/register.html',
message=message)
else:
c.execute("INSERT INTO users (username, password) VALUES \
(%s, %s)", (escape_string(username), escape_string
(password))) # creating new user
conn.commit()
c.close()
conn.close()
session['logged_in'] = True
session['username'] = username
return redirect(url_for('logged_in'))
return render_template('UserAccount/register.html')
except Exception as e:
return(str(e))
def logged_in():
"""Logged in page that appears afte succesful authentication and shows bookmarks."""
try:
c, conn = connection()
if request.method == 'POST':
title = request.form['name'] # taking input for storing bookmark
url = request.form['url']
c.execute("SELECT uid FROM users WHERE username = (%s)",
session['username']) # storing bookmark under the user who created it
c.execute("INSERT INTO bookmarks (title, url, uid) VALUES \
(%s, %s, %s)", (escape_string(title), escape_string
(url), c.fetchone()[0]))
conn.commit() # database commiting changes and closing of the connection
c.close()
conn.close()
return redirect(url_for('logged_in', message="message"))
else:
c.execute("SELECT * FROM users WHERE username = (%s)",
session['username'])
c.execute("SELECT * FROM bookmarks WHERE uid = (%s)",
c.fetchone()[0])
a = list(c.fetchall()) # if get request then query the list of bookmarks and display them
return render_template('UserAccount/logged_in.html',
data=a)
except Exception, e:
return render_template('UserAccount/login.html', message=e)
def title(self, id):
ratingsform = widgets.RemoteForm(fields=RatingFields(),
submit_text="Rate it!")
# Title.sqlmeta.addJoin(MultipleJoin('Rating',joinMethodName='ratings'))
thetitle = Title.get(id)
thetitle.ratings = []
same_author = []
for author in thetitle.author:
titles = Title.select(
"""
title.id!=%s AND
book.title_id=title.id AND
book.status ='STOCK' AND
author.id=author_title.author_id AND
author_title.title_id=title.id AND author.author_name='%s'
""" % (escape_string(
"%s" % thetitle.id), escape_string(author.author_name)),
orderBy="booktitle",
clauseTables=['book', 'author', 'author_title'],
distinct=True)
for title in titles:
same_author.append(title)
if thetitle.copies_in_status("STOCK") == 0:
raise HTTPError(404)
return dict(thetitle=thetitle,
authorswidget=AuthorsWidget(),
ratingsform=ratingsform,
titlelistwidget=TitleListWidget(),
same_author=same_author)
def transactions(self, **args):
self.common()
begin_date = args.get('begin_date', '')
end_date = args.get('end_date', '')
what = args.get('what', '')
action = args.get('action', 'SALE')
deleteid = args.get('deleteid', '0')
if int(deleteid) > 0:
Transaction.delete(deleteid)
self._transactionstemplate.begin_date = begin_date
self._transactionstemplate.end_date = end_date
self._transactionstemplate.what = what
self._transactionstemplate.action = action
self._transactionstemplate.transactions = []
if begin_date and end_date:
self._transactionstemplate.transactions = list(
Transaction.select("""
transactionLog.date >= '%s' AND
transactionLog.date <= ADDDATE('%s',INTERVAL 1 DAY) AND
transactionLog.info LIKE '%%%s%%' AND
transactionLog.action LIKE '%%%s%%'
""" % (escape_string(begin_date), escape_string(end_date),
escape_string(what), escape_string(action))))
return self._transactionstemplate.respond()
def reinventory(self, isbn="", author="", title=""):
searchform = widgets.TableForm(fields=PrivateSearchFields(),
submit_text="Search!")
if author == "" and title == "" and isbn == "":
the_titles = False
else:
the_titles = Title.select("""
title.isbn LIKE '%%%s%%' AND
author.id=author_title.author_id AND
author_title.title_id=title.id AND author.author_name LIKE '%%%s%%' AND title.booktitle LIKE '%%%s%%'
""" % (escape_string(isbn), escape_string(author),
escape_string(title)),
orderBy="booktitle",
clauseTables=['author', 'author_title'],
distinct=True)
title_count = 0
try:
title_count = the_titles.count()
except:
pass
if title_count > 0:
if the_titles.count() == 1:
return self.title(the_titles[0].id,
searchvalues=dict(author=author,
title=title,
isbn=isbn))
return dict(the_titles=the_titles,
authorswidget=PrivateAuthorsWidget(),
titlelistwidget=PrivateTitleListWidget(),
searchform=searchform,
values=dict(author=author, title=title, isbn=isbn))
def edit_profile():
username = session['username']
c, conn = connection()
c.execute('SELECT * FROM users WHERE user_username = %s;',
(escape_string(username), ))
acct = c.fetchone()
print(acct)
if not acct:
return redirect(url_for("profile"))
id = acct[0]
name = acct[1]
abt = acct[5]
if request.method == 'POST' and 'name' in request.form and 'about' in request.form:
newName = request.form['name']
newAbout = request.form['about']
if not newName or not newAbout:
return redirect(url_for("profile"))
else:
c.execute(
'UPDATE users SET user_name = %s, user_about = %s WHERE user_id = %s AND user_username = %s;',
(newName, newAbout, id, escape_string(username)))
conn.commit()
return redirect(url_for("profile"))
return render_template('edit_profile.html', name=name, about=abt)
def train(self, clas, docs):
"""
Trains the Bayesian Classifier with the given input data.
:param clas: string representing the class
:param docs: list of docs, each of which is a list of words (w/ repeats)
"""
cur = self.conn.cursor()
clas = escape_string(clas)
self._setup_db(clas)
#Adds documents to database.
for doc in docs:
counts = Counter(doc)
for term, count in counts.iteritems():
cur.execute("SELECT count from {} WHERE term = ?;".format(clas), (escape_string(term),))
currCount = cur.fetchone()
if currCount != None:
count = currCount[0] + count
self.conn.execute("UPDATE {} SET count = ? WHERE term = ?;".format(clas), (count, escape_string(term)))
else:
self.conn.execute("INSERT INTO {} VALUES(?, ?);".format(clas), (escape_string(term), count))
#Update doc nums.
cur.execute("SELECT count FROM doc_nums WHERE class = ?", (clas,))
num = cur.fetchone()[0]
self.conn.execute("UPDATE doc_nums SET count = ? WHERE class = ?;", (num+1, clas))
self.conn.commit()
def update_user_tracking():
try:
completed = str(request.args["completed"])
if completed in str(DASHBOARD_CONTENTS.values()):
client_name, settings, tracking, rank = user_info()
if tracking == None:
tracking = completed
else:
if completed not in tracking:
tracking = tracking + "," + completed
c, conn = connection()
c.execute("update users set tracking = %s where username = %s",
(escape_string(tracking), escape_string(client_name)))
conn.commit()
c.close()
conn.close()
#gc.collect()
client_name, settings, tracking, ranking = user_info()
else:
pass
except Exception as e:
pass
def insert_book(name, author, translator, publish, pub_date, instrution, price,
pages, isbn, douban_id):
"""
"""
time = _time_stamp()
sql = """ SELECT id FROM books_book WHERE isbn='%s'""" % isbn
name = escape_string(name)
author = escape_string(author)
translator = escape_string(translator)
publish = escape_string(publish)
instrution = escape_string(instrution)
if db.query_one(sql):
sql = """ UPDATE books_book SET name='%s',author='%s',translator='%s',publish='%s',pub_date='%s',instrution='%s',price='%s',pages=%d,douban_id=%d WHERE isbn='%s'""" % (
name, author, translator, publish, pub_date, instrution, price,
pages, douban_id, isbn)
else:
sql = """ INSERT INTO books_book (name,author,translator,publish,pub_date,instrution,price,pages,isbn,douban_id,category_id)
VALUES ('%s','%s','%s','%s','%s','%s','%s',%d,%d,%d,%d)""" % (
name, author, translator, publish, pub_date, instrution, price,
int(pages), int(isbn), douban_id, 1)
#print sql
return db.execute(sql)
def transactions(self,**args):
self.common()
begin_date=args.get('begin_date','')
end_date=args.get('end_date','')
what=args.get('what','')
action=args.get('action','SALE')
deleteid=args.get('deleteid','0')
if int(deleteid) >0:
Transaction.delete(deleteid)
self._transactionstemplate.begin_date=begin_date
self._transactionstemplate.end_date=end_date
self._transactionstemplate.what=what
self._transactionstemplate.action=action
self._transactionstemplate.transactions=[]
if begin_date and end_date:
self._transactionstemplate.transactions=list(Transaction.select("""
transactionLog.date >= '%s' AND
transactionLog.date <= ADDDATE('%s',INTERVAL 1 DAY) AND
transactionLog.info LIKE '%%%s%%' AND
transactionLog.action LIKE '%%%s%%'
""" % (escape_string(begin_date),escape_string(end_date),escape_string(what),escape_string(action))))
return self._transactionstemplate.respond()
def sanitize_json(json):
if isinstance(json, basestring):
# Escape all strings
return escape_string(json)
elif isinstance(json, list):
return [sanitize_json(item) for item in json]
elif isinstance(json, dict):
return {escape_string(key):sanitize_json(value) for key,value in json.items()}
else: # Int, float, True, False, None don't need to be sanitized
return json
def page(thread_id):
message = ''
thread_id = thread_id
c, conn = connection()
c.execute("SELECT * FROM posts WHERE post_id = %s", (thread_id, ))
thread = c.fetchone()
c.execute(
"SELECT * FROM comments WHERE comment_thread_id = %s ORDER BY comment_posted DESC;",
(thread_id, ))
comments = c.fetchall()
users = [c[2] for c in comments]
dates = [c[4].strftime("%m/%d/%y %H:%M:%S") for c in comments]
comments = [c[3] for c in comments]
data = zip(users, comments, dates)
c.execute("SELECT * FROM users WHERE user_username = %s;", (thread[1], ))
user_id = c.fetchone()[0]
if request.method == "GET":
return render_template('thread_detail.html',
id=thread[0],
username=thread[1],
bodytext=thread[2],
date_posted=thread[3],
data=data,
message=message,
user_id=user_id)
elif request.method == "POST":
username = session['username']
post = request.form['post']
if not post:
message = 'Please enter a comment before submit!'
return render_template('thread_detail.html',
id=thread[0],
username=thread[1],
bodytext=thread[2],
date_posted=thread[3],
data=data,
message=message,
user_id=user_id)
c.execute(
"INSERT INTO comments (comment_thread_id, comment_username, comment_bodytext) VALUES (%s, %s, %s)",
(
thread_id,
escape_string(username),
escape_string(post),
))
conn.commit()
return redirect(f"/threads/{thread_id}")
elif request.method == 'POST':
message = 'Missing information'
return redirect(f"/threads/{thread_id}")
def updateVariableDescriptionTable(self):
self.memoryCode = self.fastLookupTableIfNecessary()
code = (
"""DELETE FROM masterVariableTable WHERE dbname="%(field)s";
INSERT INTO masterVariableTable
(dbname, name, type, tablename, anchor, alias, status,description)
VALUES
('%(field)s','%(field)s','%(type)s','%(finalTable)s','%(anchor)s','%(alias)s','%(status)s','') """
% self.__dict__
)
self.dbToPutIn.query(code)
if not self.unique:
code = self.fastSQLTable()
try:
parentTab = self.dbToPutIn.query(
"""
SELECT tablename FROM masterVariableTable
WHERE dbname='%s'"""
% self.fastAnchor
).fetchall()[0][0]
except:
parentTab = "fastcat"
self.dbToPutIn.query(
'DELETE FROM masterTableTable WHERE masterTableTable.tablename="%s";' % (self.field + "heap")
)
self.dbToPutIn.query(
"INSERT INTO masterTableTable VALUES ('%s','%s','%s')"
% (self.field + "heap", parentTab, escape_string(code))
)
if self.datatype == "categorical":
# Variable Info
code = (
"""
DELETE FROM masterVariableTable WHERE dbname='%(field)s__id';
INSERT IGNORE INTO masterVariableTable
(dbname, name, type, tablename,
anchor, alias, status,description)
VALUES
('%(field)s__id','%(field)s','lookup','%(fasttab)s',
'%(anchor)s','%(alias)s','hidden','') """
% self.__dict__
)
self.dbToPutIn.query(code)
# Separate Table Info
code = self.fastLookupTableIfNecessary()
self.dbToPutIn.query(
'DELETE FROM masterTableTable WHERE masterTableTable.tablename="%s";' % (self.field + "Lookup")
)
self.dbToPutIn.query(
"INSERT INTO masterTableTable VALUES ('%s','%s','%s')"
% (self.field + "Lookup", self.fasttab, escape_string(code))
)
def profile():
username = session['username']
c, conn = connection()
c.execute("SELECT * FROM users WHERE user_username = % s",
(escape_string(username), ))
acct = c.fetchone()
name = acct[1]
if name is None:
name = username
c.execute("UPDATE users SET user_name = %s WHERE user_username = %s",
(escape_string(name), escape_string(username)))
return render_template('profile.html', name=name, username=username)
def escape(self, value):
""" 转义MySQL """
if isinstance(value, (tuple, list)):
return [self.escape(v) for v in value]
elif isinstance(value, str):
return escape_string(value)
elif isinstance(value, unicode):
return escape_string(value.encode('utf-8'))
elif isinstance(value, (int, long, float)):
return str(value)
else:
return value
def search_cards(cls, own_open_id, name, company, resources_label,
resources_key, page, count_of_page):
"""
搜索标签
:param own_open_id: 自己的openid
:param name: 搜索的名字
:param company: 搜索的公司名
:param resources_label: 搜索的资源标签
:param resources_key: 搜索的自定义标签
:param page: 页数
:param count_of_page: 每页记录数
:return: 数据列表（list），总记录数（int），页数（int）
"""
if type(page) != int or type(count_of_page) != int:
raise Exception('argv type error!')
# 处理异常数据
if page <= 0:
page = 1
# 计算偏移量
index = (page - 1) * count_of_page
sql = """
select * from %s
where status = 0 AND open_id != '%s' AND name like '%%%s%%'
AND company like '%%%s%%' AND resources_label like '%%%s%%'
AND resources_key like '%%%s%%'
order by update_time desc
limit %d, %d;
""" % (cls.table_name, escape_string(own_open_id), escape_string(name),
escape_string(company), escape_string(resources_label),
escape_string(resources_key), index, count_of_page)
objs = cls.get_db_lib().query_for_list(sql)
objs = map(cls.handle_data_use_in_json, objs)
sql = """
select count(*) as nums from %s
where status = 0 AND open_id != '%s' AND name like '%%%s%%'
AND company like '%%%s%%' AND resources_label like '%%%s%%'
AND resources_key like '%%%s%%'
order by update_time desc
limit 1;
""" % (cls.table_name, escape_string(own_open_id), escape_string(name),
escape_string(company), escape_string(resources_label),
escape_string(resources_key))
obj = cls.get_db_lib().query_for_dict(sql)
max_page = obj['nums'] / count_of_page
if obj['nums'] % count_of_page:
max_page += 1
return objs, obj['nums'], max_page
def search(self,author="",title=""):
searchform = widgets.TableForm(fields=SearchFields(), submit_text="Search!")
if author == "" and title=="":
the_titles=False
else:
the_titles=Title.select("""
book.title_id=title.id AND
book.status ='STOCK' AND
author.id=author_title.author_id AND
author_title.title_id=title.id AND author.author_name LIKE '%%%s%%' AND title.booktitle LIKE '%%%s%%'
""" % (escape_string(author),escape_string(title)),orderBy="booktitle",clauseTables=['book','author','author_title'],distinct=True)
return dict(the_titles=the_titles,authorswidget=AuthorsWidget(),titlelistwidget=TitleListWidget(),searchform=searchform,values=dict(author=author,title=title))
def get_all_card_in_a_group(cls, group_token, page, count_of_page,
search_key, industry, role):
"""
获取一个群的所有名片
:param group_token: 群凭证
:return:
"""
if type(page) != int or type(count_of_page) != int:
raise Exception("args type error!")
if page <= 0:
page = 1
index = (page - 1) * count_of_page
limit_str = " limit %d, %d" % (index, count_of_page)
search_str = """
AND (card_business_card.name like '%%%s%%' OR card_business_card.company like '%%%s%%'
OR card_business_card.redundancy_labels like '%%%s%%') AND card_business_card.industry like '%%%s%%'
AND card_business_card.role like '%%%s%%'
""" % (escape_string(search_key), escape_string(search_key),
escape_string(search_key), escape_string(industry),
escape_string(role))
search_str = search_str.replace('%', '%%')
sql = """
select card_business_card.* from card_group_record
left join card_business_card on card_group_record.open_id = card_business_card.open_id
WHERE card_group_record.group_token = %s AND card_business_card.status = 0
""" + search_str + """
order by card_business_card.name_pinyin ASC
""" + limit_str
objs = cls.db.query_for_list(sql, [group_token])
objs = map(cls.handle_data_use_in_json, objs)
sql = """
select count(*) as nums from card_group_record
left join card_business_card on card_group_record.open_id = card_business_card.open_id
""" + search_str + """
WHERE card_group_record.group_token = %s AND card_business_card.status = 0
limit 1
"""
obj = cls.db.query_for_dict(sql, [group_token])
max_page = obj['nums'] / count_of_page
if obj['nums'] % count_of_page:
max_page += 1
return {
"objs": objs,
"total_nums": obj['nums'],
"total_page": max_page
}
def sanitize_json(json):
if isinstance(json, basestring):
# Escape all strings
return escape_string(json)
elif isinstance(json, list):
return [sanitize_json(item) for item in json]
elif isinstance(json, dict):
return {
escape_string(key): sanitize_json(value)
for key, value in json.items()
}
else: # Int, float, True, False, None don't need to be sanitized
return json
def insert_table(datas):
"""
Just MySQL Insert function
"""
sql = "INSERT INTO %s (name, link, categories, price, time_capt) \
values('%s', '%s', '%s', '%s', NOW())" % (
SQL_TABLE, escape_string(datas['item_name']),
escape_string(datas['item_link']), escape_string(
datas['item_category']), escape_string(datas['item_price']))
# print sql
if cursor.execute(sql):
return True
else:
print "Something wrong"
def insert_table(datas):
sql = "INSERT INTO %s (Hash, item_name, item_price, item_link, item_category) \
values('%s', '%s', '%s', '%s', '%s')" % (SQL_TABLE,
hashlib.sha224(datas['item_name']).hexdigest(),
escape_string(datas['item_name']),
escape_string(datas['item_price']),
escape_string(datas['item_link']),
escape_string(datas['item_category'])
)
# print sql
if cursor.execute(sql):
print "Inserted"
else:
print "Something wrong"
def user_info():
try:
client_name = session["username"]
guest = False
except:
guest = True
client_name = "Guest"
if not guest:
try:
#user = User()
#user_one = user.query.filter_by(username=session["username"]).first()
c, conn = connection()
c.execute("select * from users where username = (%s)",
[(escape_string(client_name))])
data = c.fetchone()
print(data) # for debugging purposes.
settings = data[4]
tracking = data[5]
rank = data[6]
except Exception as e:
pass
else:
settings = [0, 0]
tracking = [0, 0]
rank = [0, 0]
return client_name, settings, tracking, rank
def login():
try:
c, conn = connection()
error = None
if request.method == "POST":
data = c.execute("select * from users where username = (%s)",
[escape_string(request.form["username"])])
data = c.fetchone()[2]
if pbkdf2_sha256.verify(request.form["password"], data):
flash("You are Logged in.", "success")
session["logged_in"] = True
session["username"] = request.form["username"]
# for keeping track of the users and admins.
# session["rank"] = 2
return redirect(url_for("dashboard"))
else:
error = "Invalid credentials. Try again."
gc.collect()
return render_template("login.html", error=error)
except Exception as e:
error = "Invalid credentials. Try again."
return render_template("login.html", error=error)
return render_template("login.html", error=error)
def login_page():
"""Home page of the application where the user can log in."""
if 'logged_in' and 'username' in session: # redirect user if already logged in
return redirect(url_for('logged_in'))
try:
message = None
c, conn = connection()
if request.method == 'POST':
data = c.execute("SELECT * FROM users WHERE username = (%s)",
escape_string(request.form['username'])) # extracting user
data = c.fetchone()[2]
if sha256_crypt.verify(request.form['password'], data): # verifying hash password
session['logged_in'] = True # setting session values helps keep logged in
session['username'] = request.form['username']
return redirect(url_for('logged_in', message=message))
else:
message = 'Invalid credentials. Try again'
return render_template('UserAccount/login.html', message=message) # for get method
except Exception: # catching any errors that occured in above code
message = 'Invalid credentials. Try again'
return redirect(url_for('/', message=message))
def gen_solution(cur, td, num, p_id):
# import pdb
# pdb.set_trace()
global testcase_id
global testcase_crawled
if num == 0:
column_name = 'java'
elif num == 1:
column_name = 'cpp'
elif num == 2:
column_name = 'csharp'
else:
column_name = 'VB'
cur.execute('select %s from problem where id = %d' % (column_name, p_id))
if cur.fetchall()[0][0] != None:
return
p = compile('"/stat\?c=problem_solution.*?"')
l = p.findall(td)
if len(l) == 1:
url = topcoder_site_url + unescape(l[0][1:-1])
try:
page = topcoder.get_page(url)
except Exception, e:
print url, e
return
p = compile(
'<TD CLASS="problemText" COLSPAN="8" VALIGN="middle" ALIGN="left">[\d\D]*?</TD>'
)
try:
code = escape_string(p.findall(page)[0])
except Exception, e:
print 'No code found:', url, e
return
def unconfirmed(self, letter=""):
all_titles = []
if letter == "":
all_titles = Title.select("kind_id=1", orderBy='booktitle')
else:
all_titles = Title.select(
"""kind_id=1 and booktitle LIKE '%s%%'""" %
(escape_string(letter)),
orderBy='booktitle')
unconfirmed_titles = []
for t in all_titles:
if t.copies_in_status(
'STOCK') > 0: # or t.copies_in_status('UNKNOWN')>0:
t.unconfirmed_books = []
for b in [
b for b in t.books
if (b.status == 'STOCK' or b.status == 'UNKNOWN')
]:
tags = b.get_tags(
'inventory',
'confirmation11') #needs a date eventually!
if len(tags) == 0:
t.unconfirmed_books.append(b)
if len(t.unconfirmed_books) > 0:
unconfirmed_titles.append(t)
return dict(titles=unconfirmed_titles)
def insert_table(datas):
"""
Just MySQL Insert function
"""
sql = "INSERT INTO %s (name, link, categories, price, time_capt) \
values('%s', '%s', '%s', '%s', NOW())" % (SQL_TABLE,
escape_string(datas['item_name']),
escape_string(datas['item_link']),
escape_string(datas['item_category']),
escape_string(datas['item_price'])
)
# print sql
if cursor.execute(sql):
return True
else:
print "Something wrong"
def insert(db,table, v, step=1000, update=False,debug=False):
if not v: return
v = list(v)
keys = v[0].keys()
update_keys = ','.join(( '`%s`=values(`%s`)'%(e,e) for e in keys ))
sql_base ="insert into `%s`(`%s`) values ('%%s')"%(table,'`,`'.join(keys))
for i in xrange(step):
rs = v[i::step]
if not rs: continue
vv =([escape_string('%s'%e.get(k, 'None')) for k in keys] for e in rs)
vv =( "','".join(e) for e in vv)
vv = "'),('".join(vv)
vv = vv.replace("'None'", "NULL")
vv = sql_base%vv
if bool(update):
vv = '%s ON DUPLICATE KEY UPDATE %s' %(vv,update_keys)
try:
if debug:
log.msg( '>>>sql_insert:%s'%(vv,), log.DEBUG)
db.execute( vv )
except:
db.reset()
def setBucket(bucket, userid):
'''creates a new empty bucket'''
MAX_BUCKETS_PER_USER = 100
conn = Connection()
#Validate the bucket
try:
_verifyBucket(conn, bucket, False, userid)
#Check if user has too many buckets
query = "SELECT bucket FROM bucket WHERE userid = %s"
result = conn.executeStatement(query, (int(userid)))
if len(result) >= MAX_BUCKETS_PER_USER:
raise BadRequestException.TooManyBucketsException()
#Write bucket to database and filesystem
query = "INSERT INTO bucket (bucket, userid, bucket_creation_time) VALUES (%s, %s, NOW())"
conn.executeStatement(query, (escape_string(str(bucket)), int(userid)))
path = Config.get('common','filesystem_path')
path += str(bucket)
os.mkdir(path)
except:
conn.cancelAndClose()
raise
else:
conn.close()
def user_list(user_id):
c, conn = connection()
c.execute("SELECT * FROM users WHERE user_id = %s", (user_id, ))
acct = c.fetchone()
if not acct:
return redirect(url_for("profile"))
else:
priv = acct[7]
if priv == 1:
username = acct[2]
c, conn = connection()
c.execute(
"SELECT * FROM betlists WHERE username = %s ORDER BY bet_id DESC;",
(escape_string(username), ))
data = c.fetchall()
bet_ids = [d[0] for d in data]
bets = []
for bet_id in bet_ids:
c.execute(
"SELECT team1, team2, odd FROM allbets WHERE bet_id = %s;",
(bet_id, ))
team1, team2, odd = c.fetchone()
odd = odd.replace('[[', '').replace(']]', '')
odd = odd.split('], [')
bets.append([team1, team2, odd])
return render_template('user_list.html',
username=username,
bets=bets)
else:
return render_template('private.html')
def term(self, field_name, operator, data):
model, name = self.model.get_class_from_field_name(field_name)
target_type, target_name = model.get_target_from_name(name)
target_model, field = model.get_field_from_target_name(target_name)
db_column = target_model._meta.db_table + '.' + field.column
# Handle case-insensitive queries.
if target_type == 'LCSTR':
data = data.lower()
name = 'LOWER(' + db_column + ')'
# Generate the LIKE or "=" statement.
if operator == 'equals':
result = db_column + "='{}'"
elif operator == 'startswith':
result = db_column + " LIKE '{}%%'"
elif operator == 'endswith':
result = db_column + " LIKE '%%{}'"
elif operator == 'contains':
result = db_column + " LIKE '%%{}%%'"
elif operator == 'regex':
result = db_column + " REGEXP '%%{}%%'"
elif operator == 'any':
result = '1'
else:
raise Exception('unsupported operator:' + str(operator))
# Yes, I would prefer to use a prepared statement, but collecting arguments
# and passing them back along the string everywhere would be awful design,
# and if you prefer that, you are a bad software developer.
# (Also, I didn't find any API from Django to generate a prepared statement
# without already executing it, e.g. django.db.connection.execute())
return result.format(escape_string(data))
def register_page():
form = RegistrationForm(request.form)
try:
if request.method == 'POST' and form.validate():
username = form.username.data
email = form.email.data
password = form.password.data
c = mysql.connection.cursor()
x = c.execute('SELECT * FROM users WHERE username = ("%s");' %
escape_string(username))
if int(x) > 0:
flash('That username is already taken, please choose another')
return render_template('register.html', form=form)
else:
cur = mysql.connection.cursor()
print(username)
cur.execute(
"INSERT INTO users VALUES (username, email, password)")
mysql.connection.commit()
cur.close()
flash('Thanks for registering!')
session['logged_in'] = True
session['username'] = username
return redirect(url_for('favourites_page'))
return render_template('register.html', form=form)
except Exception as e:
return render_template('register.html', form=form)
def insertInDatabase(table, **args):
"""
returns valid SQL Code for insertion
"""
fields = ','.join(args.keys())
values = ','.join(['"%s"' % escape_string(i) for i in args.values()])
return "INSERT INTO %s (%s) VALUES (%s)" % (table, fields, values)
def titles(self, letter=None):
alphabet = [
"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m",
"n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"
]
the_titles = False
if letter != None:
if letter in alphabet:
the_titles = Title.select("""
title.booktitle LIKE "%s%%" AND
book.title_id=title.id AND
book.status ='STOCK' """ % (escape_string(letter)),
orderBy="booktitle",
clauseTables=['book'],
distinct=True)
else:
the_titles = Title.select("""
title.booktitle RLIKE "^[0-9]" AND
book.title_id=title.id AND
book.status ='STOCK' """,
orderBy="booktitle",
clauseTables=['book'],
distinct=True)
return dict(authorswidget=AuthorsWidget(),
titlelistwidget=TitleListWidget(),
the_titles=the_titles,
the_letter=letter,
alphabet=alphabet)
else:
return dict(authorswidget=AuthorsWidget(),
titlelistwidget=TitleListWidget(),
the_titles=False,
the_letter=letter,
alphabet=alphabet)
def query(self, sql, *params):
cur = self.db.cursor()
sql= escape_string(sql)
cur.execute(sql, *params)
r = ResultSet(cur.fetchall())
cur.close()
return r
def insert(db, table, v, step=1000, update=False, debug=False):
if not v: return
v = list(v)
keys = v[0].keys()
update_keys = ','.join(('`%s`=values(`%s`)' % (e, e) for e in keys))
sql_base = "insert into `%s`(`%s`) values ('%%s')" % (table,
'`,`'.join(keys))
for i in xrange(step):
rs = v[i::step]
if not rs: continue
vv = ([escape_string('%s' % e.get(k, 'None')) for k in keys]
for e in rs)
vv = ("','".join(e) for e in vv)
vv = "'),('".join(vv)
vv = vv.replace("'None'", "NULL")
vv = sql_base % vv
if bool(update):
vv = '%s ON DUPLICATE KEY UPDATE %s' % (vv, update_keys)
try:
if debug:
log.msg('>>>sql_insert:%s' % (vv, ), log.DEBUG)
db.execute(vv)
except:
db.reset()
def get_old_message_from_user(cls, open_id, page, count_of_page, last_message_id=None):
"""
获取一页资源小助手信息
:param open_id: 用户openid
:param page: 页数
:param count_of_page: 每页记录数
:param last_message_id: 在那条记录之前
:return:
"""
cache_key = 'sign_resource_helper_' + open_id
# 记录下当前阅读时间
redis_client.set(cache_key, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
able_look_time = model_manager['BusinessCardModel'].get_user_create_time(open_id)
if not able_look_time:
able_look_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
open_id = escape_string(open_id)
where_str = "(((target_open_id = '%s' OR target_open_id = '') AND send_or_receive = 0) OR (source_open_id = '%s') ) AND create_time >= '%s' AND status = 0" % (open_id, open_id, able_look_time)
if last_message_id is not None:
where_str += (" AND id < %d" % int(last_message_id))
return cls.get_one_page_of_table(cls.table_name, where_str, page, count_of_page, order_by="create_time desc")
def updateMasterVariableTable(self):
"""
All the categorical variables get a lookup table;
we store the create code in the databse;
"""
for variable in self.variables:
variable.updateVariableDescriptionTable()
inCatalog = self.uniques()
if len(inCatalog) > 0 and self.tableName != "catalog":
#catalog has separate rules handled in CreateDatabase.py; so this builds
#the big rectangular table otherwise.
#It will fail if masterTableTable doesn't exister.
fileCommand = self.uniqueVariableFastSetup()
try:
parentTab = self.db.query("""
SELECT tablename FROM masterVariableTable
WHERE dbname='%s'""" % self.fastAnchor).fetchall()[0][0]
except:
if self.fastAnchor == "bookid":
parentTab = "fastcat"
else:
print(
"Unable to find a table to join the anchor (%s) against"
% self.fastAnchor)
raise
self.db.query(
'DELETE FROM masterTableTable WHERE masterTableTable.tablename="%s";'
% self.fastName)
self.db.query(
"INSERT IGNORE INTO masterTableTable VALUES ('%s','%s','%s')" %
(self.fastName, parentTab, escape_string(fileCommand)))
def updateMasterVariableTable(self):
"""
All the categorical variables get a lookup table;
we store the create code in the databse;
"""
for variable in self.variables:
# Make sure the variables know who their parent is
variable.fastAnchor = self.fastAnchor
# Update the referents for everything
variable.updateVariableDescriptionTable()
inCatalog = self.uniques()
if len(inCatalog) > 0 and self.tableName != "catalog":
# catalog has separate rules handled in CreateDatabase.py; so this builds
# the big rectangular table otherwise.
# It will fail if masterTableTable doesn't exister.
fileCommand = self.uniqueVariableFastSetup()
try:
parentTab = self.db.query(
"""
SELECT tablename FROM masterVariableTable
WHERE dbname='%s'"""
% self.fastAnchor
).fetchall()[0][0]
except:
if self.fastAnchor == "bookid":
parentTab = "fastcat"
else:
logging.error("Unable to find a table to join the anchor (%s) against" % self.fastAnchor)
raise
self.db.query('DELETE FROM masterTableTable WHERE masterTableTable.tablename="%s";' % self.fastName)
self.db.query(
"INSERT INTO masterTableTable VALUES ('%s','%s','%s')"
% (self.fastName, parentTab, escape_string(fileCommand))
)
def _attrs_and_values(self, entity):
values = []
attrs = []
# map/reduce bool changes first, then if there are changes,
# continue stringifying values.
for attr in self.properties:
value = getattr(entity, attr)
# generally if a value is None it isn't required
# by the index, but this is a bad assumption...
if value is None:
continue
if isinstance(value, bool):
value = str(int(value))
if isinstance(value, datetime.datetime) or \
isinstance(value, datetime.date):
value = str(value)
if isinstance(value, basestring):
value = "'%s'" % escape_string(value)
values.append(value)
attrs.append(attr)
return (attrs, values)
def gen_solution(cur, td, num, p_id):
# import pdb
# pdb.set_trace()
global testcase_id
global testcase_crawled
if num == 0:
column_name = 'java'
elif num == 1:
column_name = 'cpp'
elif num == 2:
column_name = 'csharp'
else:
column_name = 'VB'
cur.execute('select %s from problem where id = %d' % (column_name, p_id))
if cur.fetchall()[0][0] != None:
return
p = compile('"/stat\?c=problem_solution.*?"')
l = p.findall(td)
if len(l) == 1:
url = topcoder_site_url + unescape(l[0][1:-1])
try:
page = topcoder.get_page(url)
except Exception, e:
print url, e
return
p = compile('<TD CLASS="problemText" COLSPAN="8" VALIGN="middle" ALIGN="left">[\d\D]*?</TD>')
try:
code = escape_string(p.findall(page)[0])
except Exception, e:
print 'No code found:',url,e
return
def update_index_sql(self, entity):
"""Only generates SQL for changed index values"""
if not entity._has_changed:
# immediately return if this entity hasn't even changed.
return None
attrs, values = self._attrs_and_values(entity)
if not attrs:
return None
def values_have_changed(entity):
def _mapper(attr):
ent_value = getattr(entity, attr)
orig_value = entity._original_attrs.get(attr)
# True if changed, False if equal
return ent_value != orig_value
return _mapper
bools = map(values_have_changed(entity), attrs)
res = reduce(lambda x, y: x or y, bools)
if not res: return
updates = ['%s=%s' % (attr, value)
for attr, value in zip(attrs, values)]
sql = "UPDATE %s" % self.table
sql+= " SET %s" % ', '.join(updates)
sql+= " WHERE entity_id = '%s'" % escape_string(entity.id)
return sql
def index_sql(self, entity):
"""
Indexes are strict about only indexing entities that have
all the properties required for indexing.
Example:
(Just an example, the actual indexer doesn't index dict objs)
user_id_index = Index(table="index_user_id",
properties=["user_id")
ent = Entity()
ent['id'] = 'foo'
ent['user_id'] = 'bar'
sql = user_id_index.index_sql(ent)
sql:
INSERT INTO index_user_id (entity_id, user_id) VALUES (
'foo', 'bar'
)
"""
attrs, values = self._attrs_and_values(entity)
if not attrs:
return None
values = ["'%s'" % escape_string(entity.id)] + values
sql = "INSERT INTO %s (entity_id, %s) VALUES (%s)" % (
self.table,
', '.join(attrs),
', '.join(['%s' % str(v) for v in values])
)
return sql
def test(url):
import pdb
pdb.set_trace()
page = topcoder.get_page(url)
p = compile('<a name="problem_stats"></a>[\d\D]*?</table>')
divs = p.findall(page)
for i in xrange(len(divs)):
div = divs[i]
p = compile('<tr>[\d\D]*?</tr>')
trs = p.findall(div)
for tr in trs:
if tr.find('/stat?c=problem_statement&pm=') == -1:
continue
p = compile('/stat\?c=problem_statement&pm=[0-9]*&rd=[0-9]*')
url = topcoder_site_url + p.findall(tr)[0]
p = compile('pm=[0-9]*')
p_id = p.findall(url)[0][3:]
p = compile('>.*?<')
name = p.findall(tr)[2][1:-1]
print p_id, url, name
page = topcoder.get_page(url)
p = compile('<\!-- BEGIN BODY -->[\d\D]*?<\!-- END BODY -->')
description = p.findall(page)[0]
try:
cur.execute(insert_template %
(p_id, name, escape_string(description), url))
except IntegrityError:
print p_id, 'Duplicate!'
print
def index():
error = ''
c, conn = connection()
try:
if request.method == "POST":
input_username = request.form['username']
input_password = str(request.form['pswrd'])
c.execute("SELECT * FROM users WHERE user_username = % s",
(escape_string(input_username), ))
data = c.fetchone()
if not data:
error = 'Invalid credential! Please try again.'
return render_template("login.html", error=error)
if input_password == data[3]:
session['loggedin'] = True
session['username'] = input_username
return redirect(url_for("threads"))
else:
error = "Invalid credential! Please try again."
gc.collect()
return render_template("login.html", error=error)
except Exception as e:
error = e
return render_template("login.html")
def _save_hashtbl(tbl_name, data, dbi=None):
"""
將 data 存入列表，接受 body, [body...] 或 [{'body':'body'}...] 的格式
數量可能很大，因此硬寫不快取
"""
from MySQLdb import escape_string
from lib.util.text import md5
if (type(data) is str):
_data = {'md5': md5(data), 'body': data}
elif (type(data) is dict):
if 'md5' not in data:
data['md5'] = md5(data['body'])
_data = data
elif (type(data[0]) is str):
_data = [{'md5': md5(x), 'body': x} for x in data]
else:
_data = [{
'md5': x['md5'] if 'md5' in x else md5(x['body']),
'body': x['body']
} for x in data]
sql = "INSERT IGNORE INTO `%s` (`body`, `hash`) VALUES " % escape_string(
tbl_name)
sql += "(%(body)s, UNHEX(%(md5)s))"
DB.execute(sql, _data, dbi=dbi)
def search_cards(cls, own_open_id, industry, role, search_key, page, count_of_page):
"""
搜索标签
:param own_open_id: 自己的openid
:param industry: 搜索的行业
:param role: 搜索的职能
:param page: 页数
:param count_of_page: 每页记录数
:return: 数据列表（list），总记录数（int），页数（int）
"""
if type(page) != int or type(count_of_page) != int:
raise Exception('argv type error!')
# 处理异常数据
if page <= 0:
page = 1
# 计算偏移量
index = (page - 1) * count_of_page
sql = """
select * from %s
where status = 0 AND open_id != '%s' AND (redundancy_labels like '%%%s%%'
OR name like '%%%s%%' OR company like '%%%s%%')
AND industry like '%%%s%%' AND role like '%%%s%%'
order by update_time desc
limit %d, %d;
""" % (cls.table_name, escape_string(own_open_id), escape_string(search_key), escape_string(search_key), escape_string(search_key), escape_string(industry), escape_string(role), index, count_of_page)
objs = cls.get_db_lib().query_for_list(sql)
objs = map(cls.handle_data_use_in_json, objs)
sql = """
select count(*) as nums from %s
where status = 0 AND open_id != '%s' AND (redundancy_labels like '%%%s%%'
OR name like '%%%s%%' OR company like '%%%s%%')
AND industry like '%%%s%%' AND role like '%%%s%%'
order by update_time desc
limit 1;
""" % (cls.table_name, escape_string(own_open_id), escape_string(search_key), escape_string(search_key), escape_string(search_key), escape_string(industry), escape_string(role),)
obj = cls.get_db_lib().query_for_dict(sql)
max_page = obj['nums'] / count_of_page
if obj['nums'] % count_of_page:
max_page += 1
return objs, obj['nums'], max_page
def __build_wheres(self, conditions):
if len(conditions) == 0:
return ''
else:
i = 0
string = ' WHERE'
for field, condition in conditions.items():
if i > 0:
field, pre_op = self.__get_precondition_tuple(field)
else:
pre_op = ''
field, operator = self.__get_condition_tuple(field)
made_pre = False
made_pst = False
for letter_map, tbl_name in self.table_map.items():
tbl_name_len = len(tbl_name)
if not made_pre:
if field[:tbl_name_len] == tbl_name:
field = self.__protect_input(letter_map)+'.'+self.__protect_input(field[tbl_name_len:])
made_pre = True
elif field[:2] == letter_map+'.':
field = self.__protect_input(letter_map)+'.'+self.__protect_input(field[2:])
made_pre = True
if not made_pst:
if condition[:tbl_name_len] == tbl_name:
condition = self.__protect_input(letter_map)+'.'+self.__protect_input(condition[tbl_name_len:])
made_pst = True
elif condition[:2] == letter_map+'.':
condition = self.__protect_input(letter_map)+'.'+self.__protect_input(condition[2:])
made_pst = True
if made_pre and made_pst:
break
else:
if not made_pre:
if self.append_letter:
field = '`a`.'+self.__protect_input(field)
else:
field = self.__protect_input(field)
if not made_pst:
condition = '\''+escape_string(condition)+'\''
string = string+' '+pre_op+field
string = string+operator+condition
i = i + 1
return string
def extract_log(self, raw):
log = {}
try:
fields = raw.split('\t')
# convert to time and back to make time valid
logtime = time.strptime(fields[0] + ' ' + fields[1].split(',')[0], TIME_FORMAT)
log['logtime'] = time.strftime( TIME_FORMAT, logtime)
if fields[2] in LOG_LEVELS:
log['level'] = escape_string(fields[2])
else:
raise ValueError()
log['class'] = escape_string(fields[3])
log['msg'] = escape_string(' '.join([m for m in fields[4:] if m != "NULL"]))
except Exception as e:
# print(e.message)
return None
return log
def get_uid_by_username_or_email(username_email):
result = yield model.MatrixDB.query(
"select user_id from user where username='******' or email = '{0}'".
format(escape_string(username_email)))
if len(result) == 0:
raise tornado.gen.Return(None)
else:
raise tornado.gen.Return(result[0])
def escape(self, value):
""" 转义MySQL """
if isinstance(value, (tuple, list)):
return [self.escape(v) for v in value]
elif isinstance(value, (str, unicode)):
return escape_string(value)
else:
return value
def uncategorized(self,letter=""):
all_titles=[]
if letter=="":
all_titles=Title.select("kind_id=1",orderBy='booktitle')
else:
all_titles=Title.select("""kind_id=1 and booktitle LIKE '%s%%'""" % (escape_string(letter)),orderBy='booktitle')
return dict(titles=[t for t in all_titles if len(list(t.sections))==0])
def outofstock(self,letter=""):
all_titles=[]
if letter=="":
all_titles=Title.select("kind_id=1",orderBy='booktitle')
else:
all_titles=Title.select("""kind_id=1 and booktitle LIKE '%s%%'""" % (escape_string(letter)),orderBy='booktitle')
return dict(titles=[t for t in all_titles if t.copies_in_status("STOCK")==0])
def get_all_card_in_a_group(cls, group_token, page, count_of_page, search_key, industry, role):
"""
获取一个群的所有名片
:param group_token: 群凭证
:return:
"""
if type(page) != int or type(count_of_page) != int:
raise Exception("args type error!")
if page <= 0:
page = 1
index = (page - 1) * count_of_page
limit_str = " limit %d, %d" % (index, count_of_page)
search_str = """
AND (card_business_card.name like '%%%s%%' OR card_business_card.company like '%%%s%%'
OR card_business_card.redundancy_labels like '%%%s%%') AND card_business_card.industry like '%%%s%%'
AND card_business_card.role like '%%%s%%'
""" % (escape_string(search_key), escape_string(search_key), escape_string(search_key), escape_string(industry),
escape_string(role))
search_str = search_str.replace('%', '%%')
sql = """
select card_business_card.* from card_group_record
left join card_business_card on card_group_record.open_id = card_business_card.open_id
WHERE card_group_record.group_token = %s AND card_business_card.status = 0
""" + search_str + """
order by card_business_card.name_pinyin ASC
""" + limit_str
objs = cls.db.query_for_list(sql, [group_token])
objs = map(cls.handle_data_use_in_json, objs)
sql = """
select count(*) as nums from card_group_record
left join card_business_card on card_group_record.open_id = card_business_card.open_id
""" + search_str + """
WHERE card_group_record.group_token = %s AND card_business_card.status = 0
limit 1
"""
obj = cls.db.query_for_dict(sql, [group_token])
max_page = obj['nums'] / count_of_page
if obj['nums'] % count_of_page:
max_page += 1
return {"objs": objs, "total_nums": obj['nums'], "total_page": max_page}
def get_all(self, datastore, **kwargs):
offset = kwargs.pop('offset', None)
limit = kwargs.pop('limit', None)
order_by = kwargs.pop('order', None)
# unquote_plus because inserting url-encoded strings is a bad
# idea... python treats them as format strings. the reason
# to do it here instead of all escape_string calls is because
# get_all is generally used with arguments passed from the url
# or forms. general rule: if you're inserting user input in your
# sql, unquote and escape.
values = []
shard_on_value = kwargs.pop(self.shard_on, None)
if not shard_on_value:
raise Exception("Index doesn't have a value to shard on")
shard_on_value = escape_string(urllib.unquote_plus(shard_on_value))
values.append(shard_on_value)
where_clause = []
s = "%s=" % self.shard_on
s+= "%s"
where_clause.append(s)
for k, v in kwargs.iteritems():
if k in self.properties:
if isinstance(v, basestring):
s = "%s=" % k
s+= "%s"
where_clause.append(s)
values.append(v)
sql = "SELECT entity_id FROM %s" % self.table
if values:
sql += " WHERE "
sql += " AND ".join(where_clause)
if order_by:
sql += " ORDER BY %s " % order_by
if limit:
sql += " LIMIT %d " % limit
if offset:
sql += " OFFSET %d " % offset
shard = datastore.choose_shard(shard_on_value)
conn = datastore.get_connection(shard)
index_objs = conn.query(sql, *values)
if not index_objs:
return []
ids = [obj['entity_id'] for obj in index_objs]
return datastore.get_list(ids)
def do_str(_in):
'''deals with stringlike things'''
if type(_in) is type(None):
_in = 'NULL'
return _in
if type(_in) is not basestring:
_in = '%s' % _in
gg = escape_string(_in.decode('ascii',errors='replace'))
return ' \' %s \' ' % gg
def safe_string(value):
"""
Alias to :py:data:`MySQLdb.escape_string` to match with `safe_int`: Escapes the given string to be safe
to replace in SQL query
:param str value: SQL string to escape
:returns: Escaped string
"""
return escape_string(value)
def author(self,id):
the_author=Author.get(id)
the_titles=Title.select("""
book.title_id=title.id AND
book.status ='STOCK' AND
author.id=author_title.author_id AND
author_title.title_id=title.id AND author.author_name='%s'
""" % (escape_string(the_author.author_name)),orderBy="booktitle",clauseTables=['book','author','author_title'],distinct=True)
return dict(the_titles=the_titles,the_author=the_author,authorswidget=AuthorsWidget(),titlelistwidget=TitleListWidget())
def __extract_insert_data(self, insert_dict):
fields = []
values = []
for field, value in insert_dict.items():
fields.append(self.__protect_input(field))
values.append(escape_string(value))
return (fields, values)