Python DBmysqlの例

コード例 #1

def checkLocal(domain):
    global userid
    log.log(3, "local check")

    try:
        log.log(
            2, "started for dn %s and username %s" %
            (os.environ["tls_id_0"], os.environ["username"]))

        db = DBmysql(config.databaseUserName, config.databasePassword,
                     config.databaseName)
        sql = "SELECT us.id, us.name FROM users AS us JOIN configurations AS co ON us.confid = co.id WHERE us.name = '%s' AND us.password = SHA1('%s') AND co.dn = '%s'" % (
            os.environ["username"], os.environ["password"], domain)
        result = db.querySQL(sql)
        log.log(3, sql)
        log.log(3, "result length %s " % len(result))

        if len(result):
            userid = result[0]["id"]
            return True
        else:
            return False
    except Exception, e:
        log.log(2, "%s: %s" % (type(e), e))
        sys.exit(1)

コード例 #2

ファイル: auth.py プロジェクト: harveyzh/tunnelking

def logLastLogin(id):
	# insert session key
	try:
		db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
		sql = "UPDATE users SET lastlogin = NOW() WHERE id = %s" % id
		db.execSQL(sql)
	except Exception, e:
		log.log(2, "%s: %s" % (type(e), e))
		sys.exit(1)

コード例 #3

def logLastLogin(id):
    # insert session key
    try:
        db = DBmysql(config.databaseUserName, config.databasePassword,
                     config.databaseName)
        sql = "UPDATE users SET lastlogin = NOW() WHERE id = %s" % id
        db.execSQL(sql)
    except Exception, e:
        log.log(2, "%s: %s" % (type(e), e))
        sys.exit(1)

コード例 #4

ファイル: learnaddress.py プロジェクト: harveyzh/tunnelking

	def learn(self, rip, lip, id):
		try:
			db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
			
			sql = "INSERT INTO connections (lip, rip, userid) VALUES('%s', '%s', %s) ON DUPLICATE KEY UPDATE rip = '%s', userid = %s" % (lip, rip, id, rip, id)
			log.log(3, "sql: %s" % (sql))
			
			db.execSQL(sql)
		except Exception, e:
			log.log(2, "learn %s: %s" % (type(e), e))
			sys.exit(1)

コード例 #5

ファイル: learnaddress.py プロジェクト: harveyzh/tunnelking

	def checkConnection(self, lip, rip):
		try:
			db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
			
			sql = "SELECT `trusted` FROM `keys` WHERE rip = '%s' AND userid = %s AND `expiretime` > NOW() ORDER BY expiretime DESC LIMIT 1" % (rip, self.userid)
			log.log(3, "sql: %s" % (sql))
			
			result = db.querySQL(sql)
		except Exception, e:
			log.log(2, "%s: %s" % (type(e), e))
			sys.exit(1)

コード例 #6

def getUserApps(id):
	try:
		db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
		
		sql = "SELECT ap.appname FROM apps_users ap WHERE ap.userid = %s" % id
		log.log(3, "sql: %s" % (sql))
		
		result = db.querySQL(sql)
#		log.log(3, "res: %s" % (result))
	except Exception, e:
		log.log(3, "%s: %s" % (type(e), e))
		sys.exit(1)

コード例 #7

def getUser(id):
		try:
			db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
			
			sql = "SELECT us.id, us.name FROM users AS us WHERE us.id = %s" % id
			log.log(3, "sql: %s" % (sql))
			
			result = db.querySQL(sql)
			log.log(3, "res: %s" % (result))
		except Exception, e:
			log.log(2, "GETUSER %s: %s" % (type(e), e))
			sys.exit(1)

コード例 #8

ファイル: learnaddress.py プロジェクト: harveyzh/tunnelking

	def checkOTP(self, username, domain):
		try:
			db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
			
			sql = "SELECT us.id, us.otpRecipient FROM users AS us JOIN configurations AS co ON us.confid = co.id WHERE us.name = '%s' AND co.dn = '%s'" % (username, domain)
			log.log(3, "sql: %s" % (sql))
			
			result = db.querySQL(sql)
			log.log(3, "res: %s" % (result))
		except Exception, e:
			log.log(2, "%s: %s" % (type(e), e))
			sys.exit(1)

コード例 #9

    def learn(self, rip, lip, id):
        try:
            db = DBmysql(config.databaseUserName, config.databasePassword,
                         config.databaseName)

            sql = "INSERT INTO connections (lip, rip, userid) VALUES('%s', '%s', %s) ON DUPLICATE KEY UPDATE rip = '%s', userid = %s" % (
                lip, rip, id, rip, id)
            log.log(3, "sql: %s" % (sql))

            db.execSQL(sql)
        except Exception, e:
            log.log(2, "learn %s: %s" % (type(e), e))
            sys.exit(1)

コード例 #10

    def checkConnection(self, lip, rip):
        try:
            db = DBmysql(config.databaseUserName, config.databasePassword,
                         config.databaseName)

            sql = "SELECT `trusted` FROM `keys` WHERE rip = '%s' AND userid = %s AND `expiretime` > NOW() ORDER BY expiretime DESC LIMIT 1" % (
                rip, self.userid)
            log.log(3, "sql: %s" % (sql))

            result = db.querySQL(sql)
        except Exception, e:
            log.log(2, "%s: %s" % (type(e), e))
            sys.exit(1)

コード例 #11

ファイル: auth.py プロジェクト: harveyzh/tunnelking

def getAuthType(domain):
	try:
		db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
		sql = "SELECT co.*, opt.value AS ldap FROM configurations AS co JOIN options AS opt ON co.id = opt.confid WHERE co.dn = '%s' AND opt.name = 'ldap'" % (domain)
		result = db.querySQL(sql)
		log.log(3, "%s" % result[0]["ldap"])
		if result[0]["ldap"] == "True":
			return "LDAP"
		else:
			return "LOCAL"
	except Exception, e:
		log.log(2, "%s: %s" % (type(e), e))
		sys.exit(1)

コード例 #12

    def checkOTP(self, username, domain):
        try:
            db = DBmysql(config.databaseUserName, config.databasePassword,
                         config.databaseName)

            sql = "SELECT us.id, us.otpRecipient FROM users AS us JOIN configurations AS co ON us.confid = co.id WHERE us.name = '%s' AND co.dn = '%s'" % (
                username, domain)
            log.log(3, "sql: %s" % (sql))

            result = db.querySQL(sql)
            log.log(3, "res: %s" % (result))
        except Exception, e:
            log.log(2, "%s: %s" % (type(e), e))
            sys.exit(1)

コード例 #13

ファイル: auth.py プロジェクト: harveyzh/tunnelking

def checkLdap(domain):
	global userid
	log.log(3, "ldap check")
	ip, dn = sys.argv[1:3]
	
	try:
		db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
		sql = "SELECT us.id, us.name FROM users AS us JOIN configurations AS co ON us.confid = co.id WHERE us.name = '%s' AND co.dn = '%s'" % (os.environ["username"], domain)
		log.log(3, sql)
		result = db.querySQL(sql)
		log.log(3, "result length %s " % len(result))
	except Exception, e:
		log.log(2, "%s: %s" % (type(e), e))
		sys.exit(1)

コード例 #14

def getAuthType(domain):
    try:
        db = DBmysql(config.databaseUserName, config.databasePassword,
                     config.databaseName)
        sql = "SELECT co.*, opt.value AS ldap FROM configurations AS co JOIN options AS opt ON co.id = opt.confid WHERE co.dn = '%s' AND opt.name = 'ldap'" % (
            domain)
        result = db.querySQL(sql)
        log.log(3, "%s" % result[0]["ldap"])
        if result[0]["ldap"] == "True":
            return "LDAP"
        else:
            return "LOCAL"
    except Exception, e:
        log.log(2, "%s: %s" % (type(e), e))
        sys.exit(1)

コード例 #15

def checkLdap(domain):
    global userid
    log.log(3, "ldap check")
    ip, dn = sys.argv[1:3]

    try:
        db = DBmysql(config.databaseUserName, config.databasePassword,
                     config.databaseName)
        sql = "SELECT us.id, us.name FROM users AS us JOIN configurations AS co ON us.confid = co.id WHERE us.name = '%s' AND co.dn = '%s'" % (
            os.environ["username"], domain)
        log.log(3, sql)
        result = db.querySQL(sql)
        log.log(3, "result length %s " % len(result))
    except Exception, e:
        log.log(2, "%s: %s" % (type(e), e))
        sys.exit(1)

コード例 #16

ファイル: auth.py プロジェクト: harveyzh/tunnelking

def checkSerial():
	sn = os.environ["tls_id_0"][os.environ["tls_id_0"].find("SN=")+3:]
	cn = os.environ["tls_id_0"][os.environ["tls_id_0"].find("CN=")+3:os.environ["tls_id_0"].find('/', 2)]
	
	try:
		db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
		sql = "SELECT cn, serial FROM `ssl` WHERE cn = '%s' AND serial = '%s'" % (cn, sn)
		log.log(3, sql)
		
		result = db.querySQL(sql)
		log.log(3, sql)
		if len(result):
			return True
		else:
			return False
	except Exception, e:
		log.log(2, "%s: %s" % (type(e), e))
		sys.exit(1)

コード例 #17

ファイル: auth.py プロジェクト: harveyzh/tunnelking

def checkLocal(domain):
	global userid
	log.log(3, "local check")
	
	try:
		log.log(2, "started for dn %s and username %s" % (os.environ["tls_id_0"], os.environ["username"]))
		
		db = DBmysql(config.databaseUserName, config.databasePassword, config.databaseName)
		sql = "SELECT us.id, us.name FROM users AS us JOIN configurations AS co ON us.confid = co.id WHERE us.name = '%s' AND us.password = SHA1('%s') AND co.dn = '%s'" % (os.environ["username"], os.environ["password"], domain)
		result = db.querySQL(sql)
		log.log(3, sql)
		log.log(3, "result length %s " % len(result))
		
		if len(result):
			userid = result[0]["id"]
			return True
		else:
			return False
	except Exception, e:
		log.log(2, "%s: %s" % (type(e), e))
		sys.exit(1)

コード例 #18

def checkSerial():
    sn = os.environ["tls_id_0"][os.environ["tls_id_0"].find("SN=") + 3:]
    cn = os.environ["tls_id_0"][os.environ["tls_id_0"].find("CN=") +
                                3:os.environ["tls_id_0"].find('/', 2)]

    try:
        db = DBmysql(config.databaseUserName, config.databasePassword,
                     config.databaseName)
        sql = "SELECT cn, serial FROM `ssl` WHERE cn = '%s' AND serial = '%s'" % (
            cn, sn)
        log.log(3, sql)

        result = db.querySQL(sql)
        log.log(3, sql)
        if len(result):
            return True
        else:
            return False
    except Exception, e:
        log.log(2, "%s: %s" % (type(e), e))
        sys.exit(1)