def getGlobalFromId(id: int) -> str: sql = 'select globalid from tasks where id="' + str(id) + '"' mydb.execute(sql) row = mydb.fetchone() if not (row is None): return str(row['globalid']) return ""
def getIdFromGlobal(global_id: str) -> int: sql = 'select id from tasks where globalid="' + global_id + '"' mydb.execute(sql) row = mydb.fetchone() if not (row is None): return int(row['id']) return 0
def setTaskTag(tid: int, tag: str): tag = utils.removeDoubleSpaces( utils.removeQuotes( utils.removeNonUTF(utils.stripTags(tag.replace(',', ''))))).title()[:50] tag_id = 0 sql = 'select id from tags where name="' + tag + '"' try: mydb.execute(sql) except Exception: pass row = mydb.fetchone() str_time = str(int(time.time() * 1000)) if row is None: sql = 'insert into tags (name,created_user,created) values ("' + tag + '",' + str( auth.user_id) + ',' + str_time + ')' try: mydb.execute(sql) except Exception: pass tag_id = mydb_connection.insert_id() else: tag_id = int(row['id']) if (tag_id is None) or (tag_id < 1): return 0 sql = 'insert into tasks_tags set taskid=' + str(tid) + ', tagid=' + str( tag_id) + ', created=' + str_time try: mydb.execute(sql) except Exception: pass return tag_id
def getDefaultDevice(user_id: int) -> int: sql = 'select id from devices where uid=' + str( user_id) + ' order by `default` desc,id limit 1' mydb.execute(sql) row = mydb.fetchone() if row is None: return 0 return int(row['id'])
def getTotalUsersCount() -> int: sql = 'select count(*) as users_count from users' sql_request_ignore_error(sql) row = mydb.fetchone() count = 0 if (row is not None) and ('users_count' in row) and (row['users_count'] is not None): try: count = int(row['users_count']) except Exception: count = 0 return count
def checkOneTaskRestoreAccessPermission(global_id: str, uid: int) -> int: sql = 'select t.id,d.uid from tasks as t'\ ' left join devices as d on d.id=t.devid and d.uid=' + str(uid) +\ ' where t.globalid="' + global_id + '"' sql_request(sql) row = mydb.fetchone() if (row is None): return 0 if 'uid' not in row: return -1 if row['uid'] is None: return -1 if int(row['uid']) != uid: return -1 if 'id' not in row: return 0 if row['id'] is None: return 0 return int(row['id'])
def duplicateTask(tid: int, devid: int) -> bool: if devid < 1: return False sql_request('select * from tasks where id=' + str(tid)) row = mydb.fetchone() if row is None: return False gid = row['globalid'] if '&' in gid: g_arr = gid.split('&', 2) gid = g_arr[0] gid = gid + '&' + str(devid) row['globalid'] = gid row['id'] = 0 row.pop('id', None) row['devid'] = devid newtid = saveTask(row) sql_request('select tagid from tasks_tags where taskid=' + str(tid)) rows = mydb.fetchall() for row in rows: __setTaskTagId(newtid, row['tagid']) return False
badExit(6) jsonpost['device'] = utils.clearUserLogin(jsonpost['device'])[:50] if len(jsonpost['device']) < 1: badExit(7) jsonpost['login'] = utils.clearUserLogin(jsonpost['login']) jsonpost['password'] = hashlib.md5( (jsonpost['password']).encode('utf-8')).hexdigest().lower() auth.user_some_state = 0 auth.user_id = 0 mydb.execute( 'select id,login,fail_login_counter,fail_login_timestamp,password,state from users where login="******"') usr = mydb.fetchone() if usr is None: # Need to create new record mydb.execute('insert into users set login="******", password="******", state=1, created=' + timestamp_string) auth.user_id = mydb_connection.insert_id() if auth.user_id > 0: mydb.execute('insert into devices set `default`=1, uid=' + str(auth.user_id) + ', name="Server", state=1, created=' + timestamp_string + ',sync0=0,sync1=1,sync2=2,sync3=3' + ', lastconnect=' + timestamp_string) _mobile.log('New user registered id:' + str(auth.user_id)) else: # user exists, need to check permissions if usr['fail_login_timestamp'] is None: usr['fail_login_timestamp'] = 0
jsonpost['device'] = '' if (jsonpost['remember'] > 1) or (jsonpost['remember'] < 0): badExit(6) jsonpost['device'] = utils.clearUserLogin(jsonpost['device'])[:50] jsonpost['login'] = utils.clearUserLogin(jsonpost['login']) jsonpost['password'] = hashlib.md5( (jsonpost['password']).encode('utf-8')).hexdigest().lower() auth.user_some_state = 0 auth.user_id = 0 mydb.execute( 'select id,login,fail_login_counter,fail_login_timestamp,password,state from users where login="******" and state>0') usr = mydb.fetchone() if usr is None: wrongCred() if usr['fail_login_timestamp'] is None: usr['fail_login_timestamp'] = 0 if usr['fail_login_counter'] is None: usr['fail_login_counter'] = 0 timestamp_int = int(time.time() * 1000) if (abs(timestamp_int - int(usr['fail_login_timestamp'])) < 60 * 1000) and (int(usr['fail_login_counter']) > 5): auth.credentials = auth.buildCredentials(0, '', '', 0, 0) headers.jsonAPI(False) time.sleep(1) headers.errorResponse('@str.wait_1_min', '@str.attention', 403)
def clearDatabaseGarbage(): date_limit = str( int((time.time() - (_settings.keep_user_data_month * 31 * 24 * 60 * 60)) * 1000)) sql = 'select group_concat(u.id separator ",") as ids from users as u '\ ' left join ('\ ' select uid,max(lastconnect) as lastconnect, max(created) as created '\ ' from devices group by uid'\ ' ) as d on d.uid=u.id '\ ' where '\ ' ('\ ' ('\ ' d.lastconnect<' + date_limit +\ ' and d.created<' + date_limit +\ ' )'\ ' or d.uid is NULL'\ ' ) '\ ' and u.lastlogin<' + date_limit +\ ' and u.created<' + date_limit +\ ' limit 350' sql_request_ignore_error(sql) row = mydb.fetchone() if (row is not None) and ('ids' in row) and (row['ids'] is not None) and ( len(row['ids']) > 0): ids = row['ids'] sql = 'delete from users where id in (' + ids + ')' sql_request_ignore_error(sql) sql = 'delete from devices where uid in (' + ids + ')' sql_request_ignore_error(sql) sql = 'delete devices '\ ' from devices '\ ' left join users on users.id=devices.uid '\ ' where users.id is Null' sql_request_ignore_error(sql) sql = 'delete sync_devices '\ ' from sync_devices '\ ' left join devices on devices.id=sync_devices.src '\ ' where devices.id is Null' sql_request_ignore_error(sql) sql = 'delete sync_devices '\ ' from sync_devices '\ ' left join devices on devices.id=sync_devices.dst '\ ' where devices.id is Null' sql_request_ignore_error(sql) sql = 'delete sync_tasks '\ ' from sync_tasks '\ ' left join devices on devices.id=sync_tasks.dst '\ ' where devices.id is Null' sql_request_ignore_error(sql) sql = 'delete tasks '\ ' from tasks '\ ' left join devices on devices.id=tasks.devid '\ ' where devices.id is Null' sql_request_ignore_error(sql) sql = 'delete tasks_tags '\ ' from tasks_tags '\ ' left join tasks on tasks.id=tasks_tags.taskid '\ ' where tasks.id is Null' sql_request_ignore_error(sql) sql = 'delete tags '\ ' from tags '\ ' left join (select tagid from tasks_tags group by tagid) as tagger on tags.id=tagger.tagid '\ ' where tagger.tagid is Null' sql_request_ignore_error(sql)
if ('tasks' in auth._POST) and not (auth._POST['tasks'] is None): tasks = utils.clearGlobalIds(auth._POST['tasks']) out_arr = [] your_arr = [] tasks_arr = [] tasks = getTotalIdsString(user_id=auth.user_id, devid=auth.user_some_state, cross=tasks, extendType=0)['info']['ids'] if len(tasks) < 1: headers.errorResponse('Please sync your device') sql = "select group_concat(id,',') as int_tasks from tasks where globalid in ('" + "','".join( tasks.split(',')) + "')" sql_request(sql) tasks_row = mydb.fetchone() if tasks_row is None: headers.errorResponse('Permission denied') tasks = str(tasks_row['int_tasks']).strip(',') if len(out) > 0: out_arr = list(set((int(x)) for x in out.split(','))) if len(your) > 0: your_arr = list(set((int(x)) for x in your.split(','))) if len(tasks) > 0: tasks_arr = list(set(str(x) for x in tasks.split(','))) all_devices = list(set().union(out_arr, your_arr)) # integers def_id = db.getDefaultDevice(auth.user_id) if (len(all_devices) < 1) or len(tasks) < 1: headers.errorResponse("Error, nothing to do")
currentdir = os.path.dirname( os.path.abspath(inspect.getfile(inspect.currentframe()))) sys.path.insert(0, os.path.dirname(os.path.dirname(currentdir))) from _common.api._settings import mydb from _common.api import auth from _common.api import headers from _common.api import utils from _common.api import translation from mobile_service.apiv1._mobile import sql_request headers.jsonAPI() jsonpost = auth._POST if (jsonpost is None) or ('invite' not in jsonpost): headers.errorResponse('Bad request') invite = utils.clearStringHard(str(jsonpost['invite']))[:7].lower() if len(invite) < 3: headers.errorResponse('Too short invite') sql_request('select id from sync_devices where (dst=' + str(auth.user_some_state) + ') and invite="' + invite + '"') row = mydb.fetchone() id = 0 if row is None: headers.errorResponse('Not found') id = int(row['id']) if id < 1: headers.errorResponse('Not found') sql_request('update sync_devices set invite="", state=1 where id=' + str(id)) headers.goodResponse({'state': True}, translation.getValue('confirm_invite'))