fix validations queries and other stuff

2 files changed, 45 insertions, 23 deletions

diff --git a/TODO b/TODO
index d681c03..f9ab3e9 100644
--- a/TODO
+++ b/TODO
@@ -1,4 +1,3 @@
 * mailing listen
-* upload backup and send links
 * send mail to dev@spline.de on errors
 * bugs, bugs, bugs
diff --git a/index.py b/index.py
index 8d4fe29..781cc43 100644
--- a/index.py
+++ b/index.py
@@ -18,7 +18,7 @@ import trac.config
 
 # import other common stuff
 from random import choice
-import base64, cracklib, sha, string, os
+import base64, cracklib, sha, string, os, re
 
 
 # render mail template and send it using local sendmail binary
@@ -110,7 +110,7 @@ def set_random_password(req, user_id, length):
 	result = req.cursor.fetchone()
 	email = result[0]
 
-	req.cursor.execute("SELECT login FROM user WHERE id = %s AND password NOT NULL", (user_id,))
+	req.cursor.execute("SELECT login FROM user WHERE id = %s AND password IS NOT NULL", (user_id,))
 	result = req.cursor.fetchone()
 
 	if not result:
@@ -170,7 +170,10 @@ def validate_and_register(req, login, email):
 
 	# empty login
 	if login == "":
-		return "please enter a username";
+		return "please enter a username"
+
+	if not re.match("^[a-z._]{3,25}$", login):
+		return "your login is either too long, too short, or contains other characters than [a-z] including . and _"
 
 	# no fu-berlin.de address
 	if not email.endswith('.fu-berlin.de'):
@@ -178,7 +181,7 @@ def validate_and_register(req, login, email):
 
 	# regex checker for valid email
 	if invalid(email):
-		return "please enter a valid email address";
+		return "please enter a valid email address"
 
 	req.cursor.execute("SELECT login FROM user WHERE login = %s", (login, ))
 	if req.cursor.fetchone():
@@ -220,7 +223,7 @@ def activate(req, **formdata):
 	(c, s, cursor) = session_start(req, True)
 
 	# check whether a (correct) hashcode is given in the url
-	if "hash" in formdata:
+	if "hash" in formdata and ('activated' not in s or not s['activated']):
 		cursor.execute("SELECT user_id FROM activation WHERE hash = %s", (formdata['hash'],))
 		result = cursor.fetchone()
 
@@ -235,12 +238,10 @@ def activate(req, **formdata):
 			c['error_msg'] = set_random_password(req, user_id, 8)
 
 			if not c['error_msg']:
+				s['activated'] = True
+				s.save()
 				c['info_msg'] = "your account has been activated. you will receive an email with a temporary password."
 
-	# no hash value given
-	else:
-		redirect(req, "index")
-
 	return render_to_string("index.html", c)
 
 
@@ -249,7 +250,7 @@ def login(req, **formdata):
 
 	if req.method == "POST":
 		pw_hash = generate_sha_base64(formdata['password'])
-		cursor.execute("SELECT * FROM user WHERE login = %s AND password = %s", (formdata['login'], pw_hash))
+		cursor.execute("SELECT * FROM user WHERE login = %s AND password = %s AND activated = 1", (formdata['login'], pw_hash))
 
 		if not cursor.fetchone():
 			c['error_msg'] = "Login failed. Sorry."
@@ -338,10 +339,16 @@ def projects(req, **formdata):
 
 	if "action" in formdata and "proj_name" in formdata:
 		if formdata['action'] == "leave":
-			cursor.execute("delete from member where user_id = (select id from user where login = %s) "
-				+ "and project_id = (select id from project where project_name = %s)", (s['login'], formdata['proj_name']))
-			req.dbc.commit()
-			c['info_msg'] = "you left project " + formdata['proj_name']
+			cursor.execute("select count(*) from member where project_id = (select id from project where project_name = %s)")
+			result = cursor.fetchone()
+			if result[0] == 1:
+				c['error_msg'] = "you cannot leave this project! you're its only member! maybe you want to delete it?"
+			else:
+				cursor.execute("delete from member where user_id = (select id from user where login = %s) "
+					+ "and project_id = (select id from project where project_name = %s)", (s['login'], formdata['proj_name']))
+				req.dbc.commit()
+				generate_users_file(req.cursor)
+				c['info_msg'] = "you left project " + formdata['proj_name']
 
 		elif formdata['action'] == "delete":
 			# check whether the person is member of the project he or she wants to delete
@@ -376,12 +383,23 @@ def new_project(req, **formdata):
 		c['desc'] = formdata['project_desc']
 		c['priv'] = formdata['priv']
 
-		# send message to dev.spline.de
-		sendmail("dev@spline.de", "new_project", c)
+		cursor.execute("SELECT email FROM user WHERE login = %s", (s['login'],))
+		c['email'] = cursor.fetchone()[0]
 
-		# confirm to user
-		c['info_msg'] = "your application has been sent to the dev.spline.de team. you'll receive a message shortly"
-		return render_to_string("index.html", c)
+		cursor.execute("SELECT id FROM project WHERE project_name = %s", (c['name'], ))
+		if cursor.fetchone():
+			c['error_msg'] = "project %s already exists." % c['name']
+		elif len(c['name']) < 3:
+			c['error_msg'] = "project name must be at least 3 chars long"
+		elif len(c['desc']) < 0:
+			c['error_msg'] = "project description may not be empty"
+		else:
+			# send message to dev.spline.de
+			sendmail("dev@spline.de", "new_project", c)
+
+			# confirm to user
+			c['info_msg'] = "your application has been sent to the dev.spline.de team. you'll receive a message shortly"
+			return render_to_string("index.html", c)
 
 	return render_to_string("new_project.html", c)
 
@@ -427,9 +445,14 @@ def members(req, **formdata):
 				c['info_msg'] = "you deleted " + who + " from project " + proj
 
 			elif what == "add":
-				cursor.execute("insert into member (user_id, project_id) values (%s, %s)", (user_id, project_id))
-				req.dbc.commit()
-				c['info_msg'] = "you added " + who + " to project " + proj
+				cursor.execute("select * from member where user_id = %s and project_id = %s", (user_id, project_id));
+				if cursor.fetchone() == None:
+					cursor.execute("insert into member (user_id, project_id) values (%s, %s)", (user_id, project_id))
+					req.dbc.commit()
+					generate_users_file(req.cursor)
+					c['info_msg'] = "you added " + who + " to project " + proj
+				else:
+					c['error_msg'] = who + " is already a member of project " + proj
 
 		else:
 			c['error_msg'] = "invalid user name"