1
# IVLE - Informatics Virtual Learning Environment
2
# Copyright (C) 2007-2008 The University of Melbourne
4
# This program is free software; you can redistribute it and/or modify
5
# it under the terms of the GNU General Public License as published by
6
# the Free Software Foundation; either version 2 of the License, or
7
# (at your option) any later version.
9
# This program is distributed in the hope that it will be useful,
10
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12
# GNU General Public License for more details.
14
# You should have received a copy of the GNU General Public License
15
# along with this program; if not, write to the Free Software
16
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
22
# Code to talk to the PostgreSQL database.
23
# (This is the Data Access Layer).
24
# All DB code should be in this module to ensure portability if we want to
25
# change the DB implementation.
26
# This means no SQL strings should be outside of this module. Add functions
27
# here to perform the activities needed, and place the SQL code for those
30
# CAUTION to editors of this module.
31
# All string inputs must be sanitized by calling _escape before being
32
# formatted into an SQL query string.
39
"""Wrapper around pg.escape_string. Escapes the string for use in SQL, and
40
also quotes it to make sure that every string used in a query is quoted.
42
# "E'" is postgres's way of making "escape" strings.
43
# Such strings allow backslashes to escape things. Since escape_string
44
# converts a single backslash into two backslashes, it needs to be fed
46
# Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
47
# WARNING: PostgreSQL-specific code
48
return "E'" + pg.escape_string(str) + "'"
50
def _passhash(password):
51
return md5.md5(password).hexdigest()
53
class DBException(Exception):
54
"""A DBException is for bad conditions in the database or bad input to
55
these methods. If Postgres throws an exception it does not get rebadged.
56
This is only for additional exceptions."""
60
"""An IVLE database object. This object provides an interface to
61
interacting with the IVLE database without using any external SQL.
63
Most methods of this class have an optional dry argument. If true, they
64
will return the SQL query string and NOT actually execute it. (For
67
Methods may throw db.DBException, or any of the pg exceptions as well.
68
(In general, be prepared to catch exceptions!)
71
"""Connects to the database and creates a DB object.
72
Takes no parameters - gets all the DB info from the configuration."""
73
self.db = pg.connect(dbname=conf.db_dbname, host=conf.db_host,
74
port=conf.db_port, user=conf.db_user, passwd=conf.db_password)
76
# USER MANAGEMENT FUNCTIONS #
78
def create_user(self, login, password, nick, fullname, rolenm, studentid,
80
"""Creates a user login entry in the database.
81
Arguments are the same as those in the "login" table of the schema.
82
The exception is "password", which is a cleartext password. makeuser
83
will hash the password.
85
passhash = _passhash(password)
86
query = ("INSERT INTO login (login, passhash, nick, fullname, "
87
"rolenm, studentid) VALUES (%s, %s, %s, %s, %s, %s);" %
88
(_escape(login), _escape(passhash), _escape(nick),
89
_escape(fullname), _escape(rolenm), _escape(studentid)))
93
def update_user(self, login, password=None, nick=None,
94
fullname=None, rolenm=None, dry=False):
95
"""Updates fields of a particular user. login is the name of the user
96
to update. The other arguments are optional fields which may be
97
modified. If None or omitted, they do not get modified. login and
98
studentid may not be modified.
100
Note that no checking is done. It is expected this function is called
101
by a trusted source. In particular, it allows the password to be
102
changed without knowing the old password. The caller should check
103
that the user knows the existing password before calling this function
106
# Make a list of SQL fragments of the form "field = 'new value'"
107
# These fragments are ALREADY-ESCAPED
109
if password is not None:
110
setlist.append("passhash = " + _escape(_passhash(password)))
112
setlist.append("nick = " + _escape(nick))
113
if fullname is not None:
114
setlist.append("fullname = " + _escape(fullname))
115
if rolenm is not None:
116
setlist.append("rolenm = " + _escape(rolenm))
117
if len(setlist) == 0:
119
# Join the fragments into a comma-separated string
120
setstring = ', '.join(setlist)
121
# Build the whole query as an UPDATE statement
122
query = ("UPDATE login SET %s WHERE login = %s;"
123
% (setstring, _escape(login)))
127
def delete_user(self, login, dry=False):
128
"""Deletes a user login entry from the database."""
129
query = "DELETE FROM login WHERE login = %s;" % _escape(login)
133
def get_user(self, login, dry=False):
134
"""Given a login, returns a dictionary of the user's DB fields,
135
excluding the passhash field.
137
Raises a DBException if the login is not found in the DB.
139
query = ("SELECT login, nick, fullname, rolenm, studentid FROM login "
140
"WHERE login = %s;" % _escape(login))
142
result = self.db.query(query)
143
# Expecting exactly one
144
if result.ntuples() != 1:
145
# It should not be possible for ntuples to be greater than 1
146
assert (result.ntuples() < 1)
147
raise DBException("get_user: No user with that login name")
148
# Return as a dictionary
149
return result.dictresult()[0]
151
def user_authenticate(self, login, password, dry=False):
152
"""Performs a password authentication on a user. Returns True if
153
"password" is the correct password for the given login, False
154
otherwise. "password" is cleartext.
155
Also returns False if the login does not exist (so if you want to
156
differentiate these cases, use get_user and catch an exception).
158
query = ("SELECT login FROM login "
159
"WHERE login = '%s' AND passhash = %s;"
160
% (login, _escape(_passhash(password))))
162
result = self.db.query(query)
163
# If one row was returned, succeed.
164
# Otherwise, fail to authenticate.
165
return result.ntuples() == 1
168
"""Close the DB connection. Do not call any other functions after
169
this. (The behaviour of doing so is undefined).
added
removed
www/common/db.py
