Python web.sqlquote函数代码示例

    def listAccounts(self, domain, cur_page=1):
        '''List all users.'''
        if not iredutils.isDomain(domain):
            return (False, 'INVALID_DOMAIN_NAME')

        self.domain = str(domain)

        # Pre-defined.
        self.total = 0

        try:
            resultOfTotal = self.conn.select(
                'mailbox',
                what='COUNT(username) AS total',
                where='domain=%s' % web.sqlquote(self.domain),
            )
            if len(resultOfTotal) == 1:
                self.total = resultOfTotal[0].total or 0

            resultOfRecords = self.conn.select(
                'mailbox',
                # Just query what we need to reduce memory use.
                what='username,name,quota,bytes,messages,employeeid,active,created',
                where='domain = %s' % web.sqlquote(self.domain),
                order='username ASC',
                limit=session['pageSizeLimit'],
                offset=(cur_page-1) * session['pageSizeLimit'],
            )

            return (True, self.total, list(resultOfRecords))
        except Exception, e:
            return (False, str(e))

        def proxyfunc(self, *args, **kw):
            if 'mail' in kw.keys() and iredutils.isEmail(kw.get('mail')):
                self.domain = web.safestr(kw['mail']).split('@')[-1]
            elif 'domain' in kw.keys() and iredutils.isDomain(kw.get('domain')):
                self.domain = web.safestr(kw['domain'])
            else:
                return False

            self.admin = session.get('username')
            if not iredutils.isEmail(self.admin):
                return False

            # Check domain global admin.
            if session.get('domainGlobalAdmin') is True:
                return func(self, *args, **kw)
            else:
                # Check whether is domain admin.
                try:
                    result = self.conn.select(
                        'domain_admins',
                        what='username',
                        where='''username=%s AND domain IN %s''' % (
                            web.sqlquote(self.admin),
                            web.sqlquote([self.domain, 'ALL']),
                        ),
                    )
                except Exception, e:
                    result = {}

                if len(result) != 1:
                    return func(self, *args, **kw)
                else:
                    return web.seeother('/users' + '?msg=PERMISSION_DENIED&domain=' + self.domain)

    def isDomainExists(self, domain):
        if not iredutils.isDomain(domain):
            return True

        try:
            result = self.conn.select(
                'domain',
                what='domain',
                where='domain = %s' % web.sqlquote(domain),
                limit=1,
            )

            if len(result) > 0:
                # Exists.
                return True

            result = self.conn.select(
                'alias_domain',
                what='alias_domain',
                where='alias_domain = %s' % web.sqlquote(domain),
                limit=1,
            )

            if len(result) > 0:
                # Alias domain exists.
                return True
            else:
                return False
        except:
            # Return True as exist to not allow to create new domain/account.
            return True

    def getManagedDomains(self, admin, domainNameOnly=False, listedOnly=False,):
        self.admin = web.safestr(admin)

        if not iredutils.isEmail(self.admin):
            return (False, 'INCORRECT_USERNAME')

        self.sql_where = ''
        self.sql_left_join = ''
        if listedOnly is True:
            self.sql_where = 'AND domain_admins.username=%s' % web.sqlquote(self.admin)
        else:
            self.sql_left_join = 'OR domain_admins.domain="ALL"' % web.sqlquote(self.admin)

        try:
            result = self.conn.query(
                """
                SELECT domain.domain
                FROM domain
                LEFT JOIN domain_admins ON (domain.domain=domain_admins.domain %s)
                WHERE domain_admins.username=%s %s
                ORDER BY domain_admins.domain
                """ % (self.sql_left_join, web.sqlquote(self.admin), self.sql_where)
            )

            if domainNameOnly is True:
                domains = []
                for i in result:
                    if iredutils.isDomain(i.domain):
                        domains += [str(i.domain).lower()]

                return (True, domains)
            else:
                return (True, list(result))
        except Exception, e:
            return (False, str(e))

def get_where(context='', query=None, user_id=None):
    where = 'status is NULL or status is not NULL'
    
    # if a context is entered
    if context == 'new':
        where = 'status is NULL and (referee_rating is not NULL or affiliated)'
    elif context == 'pending':
        where = 'referee_rating is NULL'
    elif context == 'admitted':
        where = 'status = "admitted"'
    elif context == 'rejected':
        where = 'status = "rejected"'
    elif user_id and context == 'reviewed':
        user_id = web.sqlquote(str(user_id))
        where = 'a.id = v.applicant_id and v.user_id = %s' % user_id 
#        (a.id = c.applicant_id and c.user_id = %s)''' % (user_id, user_id)
    
    # if in search mode
    if query:
        columns = 'first_name, last_name, a.email, affiliation, department, referee_name, status, \
        occupation, website, interests'.split(', ')
        query = web.sqlquote('%%' + query.encode('utf-8') + '%%')
        
        where = (' like %s or ' % query).join(columns)
        where += ' like %s' % query
        where += ' or concat(first_name, " ", last_name) like ' + query
    
    return where

def search_locations_of_project(search_query, tag_list, project=None, loctype='point'):

    project_id = project.id

    conds1 = "(" + " OR ".join(
        ["(" + _has_query_str(fieldname, search_query) + ")"
            for fieldname in ("title", )]) + ")"

    select1 = """(SELECT id, 2 as weight FROM locations
        WHERE type = """ + web.sqlquote(loctype) + """
            AND """ + conds1 + """)"""

    conds2 = "(" + " OR ".join(
        ["(" + _has_query_str(fieldname, search_query) + ")"
            for fieldname in ("text", )]) + ")"

    select2 = """(SELECT location_id as id, 2 as weight FROM notes
        WHERE """ + conds2 + """)"""

    select3 = """(SELECT locations_users_tags.location_id as id, 
                         4 as weight
            FROM tags, locations_users_tags
            WHERE  """ + _has_tags(tag_list) + """
                AND locations_users_tags.tag_id = tags.id
            )"""

    selects = (""
            + select1
            + "\nUNION " 
            + select2
            + "\nUNION "
            + select3
            )


    wq = """
        SELECT locations.*, 
            locids.weight, 
            0 as distance, 
            MAX(notes.added) as last_comment, 
            COUNT(notes.location_id) as comments_count,
            users.username as author
        FROM (""" + selects + """) AS locids
        LEFT JOIN locations ON (locations.id = locids.id)
        LEFT JOIN projects_points as pp ON 
            (pp.location_id = locations.id AND pp.visible = 1)
        LEFT JOIN notes ON (notes.visible = 1 AND notes.location_id = locids.id)
        LEFT JOIN users ON (users.id = locations.user_id)
        WHERE
            locations.visible = 1 
            AND pp.project_id = """ + str(int(project_id)) + """
            AND locations.type = """ + web.sqlquote(loctype) + """
        GROUP BY """ + LOCATIONS_GROUP_BY('locations') + """, locids.weight, users.username
        ORDER BY locids.weight DESC, last_comment DESC
    ;"""

    return web.query(wq)

def _has_tags(tag_list):
    """ Forms a condition to check for certain tags """
    query_str = ""
    for tag_namespace, tag in tag_list:
        anded = "(tags.tag_namespace = " + web.sqlquote(tag_namespace) + \
            " AND " + "tags.tag = " + web.sqlquote(tag) + ")"
        query_str += (query_str and (" OR " + anded) or anded)

    return query_str and ("(" + query_str + ")") or query_str

 def GET(self, tid=None):
     info = {}
     info['title'] = '给我留言'
     msgs = {}
     reply = []
     if tid:
         tid = tid[1:]
         msgs = list(_db.getLeavemsg(where='id='+web.sqlquote(tid)))[0]
         reply = _db.getLeavemsg(where='parent='+web.sqlquote(tid), order='time asc')
     left = unicode(render.message(msgs, list(reply)))
     return render.layout(info, left, self.sidebar())

 def insert_pac(pac):
     pac_id[0] += 1
     pa = {'id':pac_id[0]}  #@@ stable ids
     for z, val in pac.items():
         if z in lob_pac: pa[lob_pac[z]] = val
     db_pac = db.select('lob_pac', where='LOWER(name)='+web.sqlquote(pa['name'].lower()))
     if not db_pac:
         db_pac = db.select('lob_pac', where='name ilike '+web.sqlquote('%'+cleanPacName(pa['name'])+'%') )
     if not db_pac:
         db.insert('lob_pac', seqname=False, **pa)
     else:
         pa = db_pac[0]
     db.insert('lob_pac_filings',seqname=False, pac_id=pa['id'], filing_id=fil['id'])

    def auth(self, username, password, verifyPassword=False,):
        if not iredutils.isEmail(username):
            return (False, 'INVALID_USERNAME')

        if len(password) == 0:
            return (False, 'EMPTY_PASSWORD')

        # Query admin.
        result = self.conn.select(
            'admin',
            where="username=%s AND active=1" % web.sqlquote(username),
            limit=1,
        )

        if len(result) == 1:
            # It's a valid admin.
            record = result[0]

            # Get salt string from password which stored in SQL.
            tmpsalt = str(record.password).split('$')
            tmpsalt[-1] = ''
            salt = '$'.join(tmpsalt)

            # Compare passwords.
            if md5crypt.md5crypt(password, salt) == str(record.password):
                if verifyPassword is not True:
                    session['username'] = username
                    session['logged'] = True
                    # Set preferred language.
                    session['lang'] = str(record.language) or 'en_US'

                    # Set session['domainGlobalAdmin']
                    try:
                        result = self.conn.select(
                            'domain_admins',
                            what='domain',
                            where='''username=%s AND domain="ALL"''' % web.sqlquote(username),
                            limit=1,
                        )
                        if len(result) == 1:
                            session['domainGlobalAdmin'] = True
                    except:
                        pass

                return (True,)
            else:
                return (False, 'INVALID_CREDENTIALS')
        else:
            return (False, 'INVALID_CREDENTIALS')

def search(query, offset=0, limit=10):
    query = get_nice_query(query)

    if not query:
        return [], False

    def sqlands(left, lst):
        return left + (" and %s " % left).join(lst)

    q = [str(web.sqlquote(w)) for w in query.split()]
    tag_query = web.sqlors("tag = ", q)

    q = [str(web.sqlquote("%%" + w + "%%")) for w in query.split()]
    where = []
    for c in ["title", "url", "description", "author"]:
        where.append(sqlands("%s like " % c, q))
    text_query = " or ".join(where)

    params = {
        "tag_query": tag_query,
        "text_query": text_query,
        "offset": offset,
        "limit": limit + 1,
        "size": len(query),
    }

    m = list(
        db.query(
            "\
    (select distinct m.id, title, url, description, author, screenshot, \
        calculated_vote as votes, m.datetime_created as dates \
        from modules as m left join tags as t on m.id = t.module_id \
        where %(tag_query)s \
        group by t.module_id \
        having count(t.module_id) >= %(size)d) \
    union \
    (select distinct m.id, title, url, description, author, screenshot, \
        calculated_vote as votes, m.datetime_created as dates \
        from modules as m \
        where %(text_query)s \
        order by calculated_vote desc, datetime_created desc) \
    order by votes desc, dates desc limit %(limit)d offset %(offset)d"
            % params
        )
    )

    has_next = len(m) > limit

    return m[:limit], has_next

    def profile(self, domain):
        self.domain = web.safestr(domain)

        if not iredutils.isDomain(self.domain):
            return (False, "INVALID_DOMAIN_NAME")

        try:
            qr = self.conn.query(
                """
                SELECT
                    domain.*,
                    sbcc.bcc_address AS sbcc_addr,
                    sbcc.active AS sbcc_active,
                    rbcc.bcc_address AS rbcc_addr,
                    rbcc.active AS rbcc_active,
                    alias.goto AS catchall,
                    alias.active AS catchall_active,
                    COUNT(DISTINCT mailbox.username) AS mailbox_count,
                    COUNT(DISTINCT alias.address) AS alias_count
                FROM domain
                LEFT JOIN sender_bcc_domain AS sbcc ON (sbcc.domain=domain.domain)
                LEFT JOIN recipient_bcc_domain AS rbcc ON (rbcc.domain=domain.domain)
                LEFT JOIN domain_admins ON (domain.domain = domain_admins.domain)
                LEFT JOIN mailbox ON (domain.domain = mailbox.domain)
                LEFT JOIN alias ON (
                    domain.domain = alias.domain
                    AND alias.address <> alias.goto
                    AND alias.address <> %s
                    )
                WHERE domain.domain=%s
                GROUP BY
                    domain.domain, domain.description, domain.aliases,
                    domain.mailboxes, domain.maxquota, domain.quota,
                    domain.transport, domain.backupmx, domain.created,
                    domain.active
                ORDER BY domain.domain
                LIMIT 1
                """
                % (web.sqlquote(self.domain), web.sqlquote(self.domain))
            )

            if len(qr) == 1:
                # Return first list element.
                return (True, list(qr)[0])
            else:
                return (False, "NO_SUCH_OBJECT")
        except Exception, e:
            return (False, str(e))

    def update(self, domain, profile_type, data):
        self.profile_type = str(profile_type)
        self.domain = str(domain)

        # Pre-defined update key:value.
        updates = {"modified": iredutils.sqlNOW}

        if self.profile_type == "general":
            # Get name
            self.cn = data.get("cn", "")
            updates["description"] = self.cn

            # Get default quota for new user.
            self.defaultQuota = str(data.get("defaultQuota"))
            if self.defaultQuota.isdigit():
                updates["defaultuserquota"] = int(self.defaultQuota)

            if session.get("domainGlobalAdmin") is True:
                # Get account status
                if "accountStatus" in data.keys():
                    updates["active"] = 1
                else:
                    updates["active"] = 0

                updates["maxquota"] = 0

                # Update SQL db with columns: maxquota, active.
                try:
                    self.conn.update("domain", where="domain=%s" % web.sqlquote(self.domain), **updates)
                except Exception, e:
                    return (False, str(e))

                # Get list of domain admins.
                domainAdmins = [str(v).lower() for v in data.get("domainAdmin", []) if iredutils.isEmail(str(v))]

                try:
                    # Delete all records first.
                    self.conn.delete("domain_admins", where="domain=%s" % web.sqlquote(self.domain))

                    # Add new admins.
                    if len(domainAdmins) > 0:
                        v = []
                        for adm in domainAdmins:
                            v += [{"username": adm, "domain": self.domain, "created": iredutils.sqlNOW, "active": 1}]

                        self.conn.multiple_insert("domain_admins", values=v)
                except Exception, e:
                    return (False, str(e))

def _has_query_str(fieldname, qs):
    query_str = ""
    for q in [WORDCHARS.sub('', c.strip()) for c in qs.split()]:
        qq = '[[:<:]]%s[[:>:]]' % q
        cond = REGEXQ % (fieldname, web.sqlquote(qq))
        query_str += query_str and (" OR " + cond) or cond
    return query_str

    def isEmailExists(self, mail):
        # Return True if account is invalid or exist.
        self.mail = web.safestr(mail)

        if not iredutils.isEmail(mail):
            return True

        self.sqlMail = web.sqlquote(self.mail)

        try:
            resultOfAlias = self.conn.select(
                'alias',
                what='address',
                where='address=%s' % self.sqlMail,
                limit=1,
            )

            resultOfMailbox = self.conn.select(
                'mailbox',
                what='username',
                where='username=%s' % self.sqlMail,
                limit=1,
            )

            if len(resultOfAlias) == 1 or len(resultOfMailbox) == 1:
                return True
            else:
                return False

        except Exception, e:
            return True