Commit: 793 - shibuya-trac (svn) - Shibuya.trac

OSDN > Finden Software > Shibuya.trac > SCM > SVN > Commit

R/O
SSH
HTTPS

shibuya-trac: Commit

Commit MetaInfo

Revision	793 (tree)
Zeit	2011-04-14 21:31:08
Autor	okamototk

Log Message

SQLインジェクションを修正。

Ändern Zusammenfassung

modified: plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_edit.py (diff)
modified: plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/__init__.py (diff)
modified: plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_answer.py (diff)
modified: plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete.py (diff)
modified: plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_title.py (diff)
modified: plugins/enqueteplugin/trunk/EnquetePlugin/src/setup.py (diff)
added: plugins/enqueteplugin/trunk/EnquetePlugin/src/setup.cfg (diff)

Diff

--- plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_edit.py (revision 792)

+++ plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_edit.py (revision 793)

		@@ -30,9 +30,9 @@
30	30	if actionName=='delete':
31	31	question_id = req.args.get('question_id')
32	32	try:
33		- sql = ("""delete from question where enquete_id = %d and question_id=%d""" ) % (int(enquete_id),int(question_id))
	33	+ sql = "delete from question where enquete_id = %s and question_id=%s"
34	34	_self.log.debug('%s' % sql)
35		- cursor.execute(sql)
	35	+ cursor.execute(sql, (int(enquete_id),int(question_id)))
36	36	except Exception,e:
37	37	_self.log.debug('%s' % e)
38	38	raise e

		@@ -39,23 +39,23 @@
39	39	elif actionName =='savesort':
40	40	listorder = req.args.get('listorder').splitlines()
41	41	for idx, question_id in enumerate(listorder):
42		- sql = ("""update question set sort_no=%d where enquete_id='%s' and question_id='%s' """ ) % (idx, enquete_id, question_id)
	42	+ sql = "update question set sort_no=%s where enquete_id=%s and question_id=%s "
43	43	_self.log.debug('%s' % sql)
44		- cursor.execute(sql)
	44	+ cursor.execute(sql, (idx, enquete_id, question_id))
45	45	db.commit()
46	46	message = '並び順を保存しました。'
47	47	#設問一覧検索
48	48	title = ""
49	49	try:
50		- sql = ("""SELECT title FROM enquete where enquete_id = %d""" % (int(enquete_id)))
	50	+ sql = "SELECT title FROM enquete where enquete_id = %s"
51	51	_self.log.debug('%s' % sql)
52		- cursor.execute(sql)
	52	+ cursor.execute(sql, (int(enquete_id),))
53	53	row = cursor.fetchone()
54	54	title = row[0]
55	55
56		- sql = ("""SELECT enquete_id, question_id, question_title, type, detail FROM question where enquete_id = %d order by sort_no""" % (int(enquete_id)))
	56	+ sql = "SELECT enquete_id, question_id, question_title, type, detail FROM question where enquete_id = %s order by sort_no"
57	57	_self.log.debug('%s' % sql)
58		- cursor.execute(sql)
	58	+ cursor.execute(sql, (int(enquete_id),))
59	59
60	60	except Exception,e:
61	61	_self.log.debug('%s' % e)

		@@ -91,9 +91,9 @@
91	91	if actionName=='edit':
92	92	question_id = req.args.get('question_id')
93	93	try:
94		- sql = ("""SELECT enquete_id, question_id, question_title, type, detail FROM question where enquete_id = %d and question_id=%d""" ) % (int(enquete_id),int(question_id))
	94	+ sql = "SELECT enquete_id, question_id, question_title, type, detail FROM question where enquete_id = %s and question_id=%s"
95	95	_self.log.debug('%s' % sql)
96		- cursor.execute(sql)
	96	+ cursor.execute(sql, (int(enquete_id),int(question_id)))
97	97	row = cursor.fetchone()
98	98	except Exception,e:
99	99	_self.log.debug('%s' % e)

		@@ -110,15 +110,15 @@
110	110
111	111	try:
112	112	if question_id:
113		- sql = ("""update question set question_title='%s',type=%d,detail='%s' where enquete_id = %d and question_id=%d""" ) % (question_title ,int(type), detail, int(enquete_id),int(question_id))
	113	+ sql = "update question set question_title=%s,type=%s,detail=%s where enquete_id = %s and question_id=%s"
114	114	_self.log.debug('%s' % sql)
115		- cursor.execute(sql)
	115	+ cursor.execute(sql, (question_title ,int(type), detail, int(enquete_id),int(question_id)))
116	116	else:
117	117	when = datetime.now(utc)
118	118	when_ts = to_timestamp(when)
119		- sql = ("""insert into question values(%d,%d,'%s',%d,'%s',%d)""" ) % (int(enquete_id) , when_ts, question_title, int(type), detail, 0)
	119	+ sql = """insert into question values(%s,%s,%s,%s,%s,%s)"""
120	120	_self.log.debug('%s' % sql)
121		- cursor.execute(sql)
	121	+ cursor.execute(sql, (int(enquete_id) , when_ts, question_title, int(type), detail, 0))
122	122	except Exception,e:
123	123	_self.log.debug('%s' % e)
124	124	raise e

--- plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/__init__.py (revision 792)

+++ plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/__init__.py (revision 793)

		@@ -1 +1,2 @@
	1	+# -- coding: utf-8 --
1	2	from enquete import *

--- plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_answer.py (revision 792)

+++ plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_answer.py (revision 793)

		@@ -46,14 +46,14 @@
46	46	when_ts = to_timestamp(when)
47	47	try:
48	48	sql = [
49		- ("""delete from answer where enquete_id = %d and user_id='%s'""" ) % (int(enquete_id),digest_user),
50		- ("""delete from answer_detail where enquete_id = %d and user_id='%s'""" ) % (int(enquete_id),digest_user),
51		- ("""insert into answer values( %d, '%s' , %d, '%s')""") % (int(enquete_id),digest_user, 0, when)
	49	+ ["delete from answer where enquete_id = %s and user_id=%s",(int(enquete_id),digest_user)],
	50	+ ["delete from answer_detail where enquete_id = %s and user_id=%s" ,(int(enquete_id),digest_user)],
	51	+ ["insert into answer values( %s, %s , %s, %s)" ,(int(enquete_id),digest_user, 0, when)]
52	52	]
53	53	for s in sql:
54	54	try:
55	55	_self.log.debug('%s' % s)
56		- cursor.execute(s)
	56	+ cursor.execute(s[0],s[1])
57	57	except Exception,e:
58	58	_self.log.debug('%s' % e)
59	59	None

		@@ -70,13 +70,13 @@
70	70
71	71	if isinstance(answer,list):
72	72	for ans in answer:
73		- sql = ("""insert into answer_detail values( %d,'%s',%d,'%s')""" % (int(enquete_id),digest_user,int(q_id),ans))
	73	+ sql = """insert into answer_detail values( %s,%s,%s,%s)"""
74	74	_self.log.debug('%s' % sql)
75		- cursor.execute(sql)
	75	+ cursor.execute(sql, (int(enquete_id),digest_user,int(q_id),ans))
76	76	else:
77		- sql = ("""insert into answer_detail values( %d,'%s',%d,'%s')""" % (int(enquete_id),digest_user,int(q_id),answer))
	77	+ sql = """insert into answer_detail values( %s,%s,%s,%s)"""
78	78	_self.log.debug('%s' % sql)
79		- cursor.execute(sql)
	79	+ cursor.execute(sql,(int(enquete_id),digest_user,int(q_id),answer))
80	80
81	81	count = count + 1
82	82	except Exception,e:

		@@ -98,16 +98,16 @@
98	98	chart_map={}
99	99	group_id = req.args.get('group_id')
100	100	try:
101		- sql = ("""SELECT title,status FROM enquete where enquete_id = %d""" % (int(enquete_id)))
	101	+ sql = "SELECT title,status FROM enquete where enquete_id = %s"
102	102	_self.log.debug('%s' % sql)
103		- cursor.execute(sql)
	103	+ cursor.execute(sql, (int(enquete_id),))
104	104	row = cursor.fetchone()
105	105	title = row[0]
106	106	status = row[1]
107	107
108		- sql = ("""SELECT enquete_id, question_id, question_title, type, detail FROM question where enquete_id = %d order by sort_no""" % (int(enquete_id)))
	108	+ sql = "SELECT enquete_id, question_id, question_title, type, detail FROM question where enquete_id = %s order by sort_no"
109	109	_self.log.debug('%s' % sql)
110		- cursor.execute(sql)
	110	+ cursor.execute(sql, (int(enquete_id),))
111	111
112	112	for enquete_id,question_id, question_title, type, detail in cursor:
113	113	question = {'enquete_id':enquete_id, 'question_id':question_id, 'question_title':question_title, 'type':type, 'detail':detail}

		@@ -117,9 +117,9 @@
117	117	#アンケート集計
118	118	if actionName=='count' or actionName=='group':
119	119	# アンケート結果
120		- sql = ("""SELECT enquete_id, question_id, answer ,count(answer) as cnt FROM answer_detail where enquete_id = %d group by question_id,answer order by cnt desc""" % (int(enquete_id)))
	120	+ sql = "SELECT enquete_id, question_id, answer ,count(answer) as cnt FROM answer_detail where enquete_id = %s group by question_id,answer order by cnt desc"
121	121	_self.log.debug('%s' % sql)
122		- cursor.execute(sql)
	122	+ cursor.execute(sql, (int(enquete_id),))
123	123	for enquete_id, question_id, answer, cnt in cursor:
124	124	al = []
125	125	if question_id in answer_map:

		@@ -139,9 +139,9 @@
139	139	chart_map[question_id] = url
140	140
141	141
142		- sql = ("""SELECT question_id, answer FROM answer_detail where enquete_id = %d and user_id='%s'""" % (int(enquete_id), digest_user))
	142	+ sql = "SELECT question_id, answer FROM answer_detail where enquete_id = %s and user_id=%s"
143	143	_self.log.debug('%s' % sql)
144		- cursor.execute(sql)
	144	+ cursor.execute(sql, (int(enquete_id), digest_user))
145	145
146	146	for question_id, answer in cursor:
147	147	answer = {'question_id':question_id, 'answer':answer}

--- plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete.py (revision 792)

+++ plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete.py (revision 793)

		@@ -124,16 +124,16 @@
124	124	#cursor.execute("""delete from answer where enquete_id = %d""" % (int(enquete_id)))
125	125	#cursor.execute("""delete from answer_detail where enquete_id = %d""" % (int(enquete_id)))
126	126	sql = [
127		- ("""delete from enquete where enquete_id = %d""") % (int(enquete_id)),
128		- ("""delete from question where enquete_id = %d""") % (int(enquete_id)),
129		- ("""delete from answer where enquete_id = %d""") % (int(enquete_id)),
130		- ("""delete from answer_detail where enquete_id = %d""") % (int(enquete_id))
	127	+ "delete from enquete where enquete_id = %s" ,
	128	+ "delete from question where enquete_id = %s" ,
	129	+ "delete from answer where enquete_id = %s" ,
	130	+ "delete from answer_detail where enquete_id = %s"
131	131	]
132	132	cursor = db.cursor()
133	133	for s in sql:
134	134	try:
135	135	self.log.debug('%s' % s)
136		- cursor.execute(s)
	136	+ cursor.execute(s ,(int(enquete_id),))
137	137	except Exception,e:
138	138	self.log.debug('%s' % e)
139	139	None

		@@ -156,19 +156,19 @@
156	156	try:
157	157	if actionName == 'open':
158	158	change_status = 1
159		- sql = ("""update enquete set status=%d,open_date = '%s' where enquete_id = %d""" ) % (change_status,when,int(enquete_id))
	159	+ sql = "update enquete set status=%s,open_date = %s where enquete_id = %s"
160	160	self.log.debug('%s' % sql)
161		- cursor.execute(sql)
	161	+ cursor.execute(sql, (change_status,when,int(enquete_id)))
162	162	elif actionName == 'close':
163	163	change_status = 0
164		- sql = ("""update enquete set status=%d,open_date = '' where enquete_id = %d""" ) % (change_status,int(enquete_id))
	164	+ sql = "update enquete set status=%s,open_date = '' where enquete_id = %s"
165	165	self.log.debug('%s' % sql)
166		- cursor.execute(sql)
	166	+ cursor.execute(sql, (change_status,int(enquete_id)))
167	167	elif actionName == 'expire':
168	168	change_status = 2
169		- sql = ("""update enquete set status=%d where enquete_id = %d""" ) % (change_status,int(enquete_id))
	169	+ sql = """update enquete set status=%s where enquete_id = %s"""
170	170	self.log.debug('%s' % sql)
171		- cursor.execute(sql)
	171	+ cursor.execute(sql, (change_status,int(enquete_id)))
172	172
173	173	except Exception,e:
174	174	self.log.debug('%s' % e)

		@@ -178,18 +178,18 @@
178	178	error = "アンケートのオーナではありません。"
179	179	#アンケート一覧検索
180	180	try:
181		- sql = ("""SELECT e0.*,strftime('%Y/%m/%d %H:%M:%S',a2.update_date) as answer_date from (""")
182		- sql = sql +("""SELECT e.enquete_id as enquete_id, e.title as title, strftime('%Y/%m/%d %H:%M:%S',e.update_date) as upd_date, e.create_user,""")
183		- sql = sql + (""" strftime('%Y/%m/%d %H:%M:%S',e.open_date) as open_date, e.status as status,e.expire_date as expire_date, e.description as description, count(a.user_id) cnt FROM enquete e""")
184		- sql = sql + (""" left outer join answer a on a.enquete_id=e.enquete_id""")
	181	+ sql = """SELECT e0.*,strftime('%Y/%m/%d %H:%M:%S',a2.update_date) as answer_date from (
	182	+ SELECT e.enquete_id as enquete_id, e.title as title, strftime('%Y/%m/%d %H:%M:%S',e.update_date) as upd_date, e.create_user,
	183	+ strftime('%Y/%m/%d %H:%M:%S',e.open_date) as open_date, e.status as status,e.expire_date as expire_date, e.description as description, count(a.user_id) cnt FROM enquete e
	184	+ left outer join answer a on a.enquete_id=e.enquete_id"""
185	185	if req.authname != 'admin':
186	186	#admin以外はオーナが自分のアンケートか、公開受付中、受付終了のみ
187		- sql = sql + (""" where e.create_user='%s' or e.status in (1,2)""" ) % (req.authname)
188		- sql = sql + (""" group by e.enquete_id""")
	187	+ sql = sql + " where e.create_user='%s' or e.status in (1,2)" % req.authname
	188	+ sql = sql + " group by e.enquete_id"
189	189	digest_user = md5.new(req.authname).hexdigest()
190		- sql = sql + (""" ) e0 left outer join answer a2 on a2.enquete_id=e0.enquete_id and a2.user_id = '%s'""") % (digest_user)
	190	+ sql = sql + " ) e0 left outer join answer a2 on a2.enquete_id=e0.enquete_id and a2.user_id = %s"
191	191	self.log.debug('%s' % sql)
192		- cursor.execute(sql)
	192	+ cursor.execute(sql, (digest_user,))
193	193
194	194	except Exception,e:
195	195	self.log.debug('%s' % e)

		@@ -215,8 +215,8 @@
215	215
216	216	def checkOwner(self,db,user,enquete_id):
217	217	cursor = db.cursor()
218		- sql = ("""SELECT create_user,status FROM enquete where enquete_id = %d""" % (int(enquete_id)))
219		- cursor.execute(sql)
	218	+ sql = "SELECT create_user,status FROM enquete where enquete_id = %s"
	219	+ cursor.execute(sql, (int(enquete_id),))
220	220	row = cursor.fetchone()
221	221	if row[0] == user:
222	222	return True,row[1]

--- plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_title.py (revision 792)

+++ plugins/enqueteplugin/trunk/EnquetePlugin/src/enquete/enquete_title.py (revision 793)

		@@ -36,11 +36,13 @@
36	36	try:
37	37	sql = ''
38	38	if enquete_id == '':
39		- sql = ("""insert into enquete values(%d,'%s',%d, '','%s','%s','%s','%s')""") % (when_ts ,title, 0, when, req.authname , expire_date, description)
	39	+ sql = "insert into enquete values(%s,%s,%s, '',%s,%s,%s,%s)"
	40	+ _self.log.debug('%s' % sql)
	41	+ cursor.execute(sql, (when_ts ,title, 0, when, req.authname , expire_date, description))
40	42	else:
41		- sql = ("""update enquete set title='%s',expire_date='%s',update_date='%s',description='%s' where enquete_id='%s'""") % (title, expire_date, when , description, enquete_id)
42		- _self.log.debug('%s' % sql)
43		- cursor.execute(sql)
	43	+ sql = "update enquete set title=%s,expire_date=%s,update_date=%s,description=%s where enquete_id=%s"
	44	+ _self.log.debug('%s' % sql)
	45	+ cursor.execute(sql, (title, expire_date, when , description, enquete_id))
44	46	except Exception,e:
45	47	_self.log.debug('%s' % e)
46	48	raise e

		@@ -58,9 +60,9 @@
58	60	description = ""
59	61	try:
60	62	if actionName == 'update':
61		- sql = ("""SELECT title,expire_date,description FROM enquete where enquete_id = %d""" % (int(enquete_id)))
	63	+ sql = "SELECT title,expire_date,description FROM enquete where enquete_id = %s"
62	64	_self.log.debug('%s' % sql)
63		- cursor.execute(sql)
	65	+ cursor.execute(sql, (int(enquete_id),))
64	66	row = cursor.fetchone()
65	67	title = row[0]
66	68	expire_date = row[1]

--- plugins/enqueteplugin/trunk/EnquetePlugin/src/setup.py (revision 792)

+++ plugins/enqueteplugin/trunk/EnquetePlugin/src/setup.py (revision 793)

		@@ -1,7 +1,7 @@
1	1	from setuptools import find_packages, setup
2	2
3	3	setup(
4		- name='TracEnquetePlugin', version='0.3.1',
	4	+ name='TracEnquetePlugin', version='0.3.2',
5	5	packages=find_packages(exclude=['.tests']),
6	6	entry_points = {'trac.plugins':
7	7	['enquete.enquete = enquete.enquete',],},

Show on old repository browser