def getFields(self, query):
"""
Take in input a query string and return its fields (columns) and
more details.
Example:
Input: SELECT user, password FROM mysql.user
Output: user,password
@param query: query to be processed
@type query: C{str}
@return: query fields (columns) and more details
@rtype: C{str}
"""
prefixRegex = r"(?:\s+(?:FIRST|SKIP)\s+\d+)*"
fieldsSelectTop = re.search(r"\ASELECT\s+TOP\s+[\d]+\s+(.+?)\s+FROM", query, re.I)
fieldsSelectRownum = re.search(r"\ASELECT\s+([^()]+?),\s*ROWNUM AS LIMIT FROM", query, re.I)
fieldsSelectDistinct = re.search(r"\ASELECT%s\s+DISTINCT\((.+?)\)\s+FROM" % prefixRegex, query, re.I)
fieldsSelectCase = re.search(r"\ASELECT%s\s+(\(CASE WHEN\s+.+\s+END\))" % prefixRegex, query, re.I)
fieldsSelectFrom = re.search(r"\ASELECT%s\s+(.+?)\s+FROM " % prefixRegex, query, re.I)
fieldsExists = re.search(r"EXISTS\(([^)]*)\)\Z", query, re.I)
fieldsSelect = re.search(r"\ASELECT%s\s+(.*)" % prefixRegex, query, re.I)
fieldsSubstr = re.search(r"\A(SUBSTR|MID\()", query, re.I)
fieldsMinMaxstr = re.search(r"(?:MIN|MAX)\(([^\(\)]+)\)", query, re.I)
fieldsNoSelect = query
if fieldsSubstr:
fieldsToCastStr = query
elif fieldsMinMaxstr:
fieldsToCastStr = fieldsMinMaxstr.groups()[0]
elif fieldsExists:
fieldsToCastStr = fieldsSelect.groups()[0]
elif fieldsSelectTop:
fieldsToCastStr = fieldsSelectTop.groups()[0]
elif fieldsSelectRownum:
fieldsToCastStr = fieldsSelectRownum.groups()[0]
elif fieldsSelectDistinct:
fieldsToCastStr = fieldsSelectDistinct.groups()[0]
elif fieldsSelectCase:
fieldsToCastStr = fieldsSelectCase.groups()[0]
elif fieldsSelectFrom:
_ = zeroDepthSearch(query, " FROM ")
fieldsToCastStr = query[:unArrayizeValue(_)] if _ else query
fieldsToCastStr = re.sub(r"\ASELECT%s\s+" % prefixRegex, "", fieldsToCastStr)
elif fieldsSelect:
fieldsToCastStr = fieldsSelect.groups()[0]
else:
fieldsToCastStr = fieldsNoSelect
# Function
if re.search("\A\w+\(.*\)", fieldsToCastStr, re.I) or (fieldsSelectCase and "WHEN use" not in query) or fieldsSubstr:
fieldsToCastList = [fieldsToCastStr]
else:
fieldsToCastList = splitFields(fieldsToCastStr)
return fieldsSelectFrom, fieldsSelect, fieldsNoSelect, fieldsSelectTop, fieldsSelectCase, fieldsToCastList, fieldsToCastStr, fieldsExists
def nullCastConcatFields(self, fields):
"""
Take in input a sequence of fields string and return its processed
nulled, casted and concatenated fields string.
Examples:
MySQL input: user,password
MySQL output: IFNULL(CAST(user AS CHAR(10000)), ' '),'UWciUe',IFNULL(CAST(password AS CHAR(10000)), ' ')
MySQL scope: SELECT user, password FROM mysql.user
PostgreSQL input: usename,passwd
PostgreSQL output: COALESCE(CAST(usename AS CHARACTER(10000)), ' ')||'xRBcZW'||COALESCE(CAST(passwd AS CHARACTER(10000)), ' ')
PostgreSQL scope: SELECT usename, passwd FROM pg_shadow
Oracle input: COLUMN_NAME,DATA_TYPE
Oracle output: NVL(CAST(COLUMN_NAME AS VARCHAR(4000)), ' ')||'UUlHUa'||NVL(CAST(DATA_TYPE AS VARCHAR(4000)), ' ')
Oracle scope: SELECT COLUMN_NAME, DATA_TYPE FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME='%s'
Microsoft SQL Server input: name,master.dbo.fn_varbintohexstr(password)
Microsoft SQL Server output: ISNULL(CAST(name AS VARCHAR(8000)), ' ')+'nTBdow'+ISNULL(CAST(master.dbo.fn_varbintohexstr(password) AS VARCHAR(8000)), ' ')
Microsoft SQL Server scope: SELECT name, master.dbo.fn_varbintohexstr(password) FROM master..sysxlogins
@param fields: fields string to be processed
@type fields: C{str}
@return: fields string nulled, casted and concatened
@rtype: C{str}
"""
if not Backend.getDbms():
return fields
if (
fields.startswith("(CASE")
or fields.startswith("(IIF")
or fields.startswith("SUBSTR")
or fields.startswith("MID(")
or re.search(r"\A'[^']+'\Z", fields)
):
nulledCastedConcatFields = fields
else:
fieldsSplitted = splitFields(fields)
dbmsDelimiter = queries[Backend.getIdentifiedDbms()].delimiter.query
nulledCastedFields = []
for field in fieldsSplitted:
nulledCastedFields.append(self.nullAndCastField(field))
delimiterStr = "%s'%s'%s" % (dbmsDelimiter, kb.chars.delimiter, dbmsDelimiter)
nulledCastedConcatFields = delimiterStr.join(field for field in nulledCastedFields)
return nulledCastedConcatFields
def nullCastConcatFields(self, fields):
"""
Take in input a sequence of fields string and return its processed
nulled, casted and concatenated fields string.
Examples:
MySQL input: user,password
MySQL output: IFNULL(CAST(user AS CHAR(10000)), ' '),'UWciUe',IFNULL(CAST(password AS CHAR(10000)), ' ')
MySQL scope: SELECT user, password FROM mysql.user
PostgreSQL input: usename,passwd
PostgreSQL output: COALESCE(CAST(usename AS CHARACTER(10000)), ' ')||'xRBcZW'||COALESCE(CAST(passwd AS CHARACTER(10000)), ' ')
PostgreSQL scope: SELECT usename, passwd FROM pg_shadow
Oracle input: COLUMN_NAME,DATA_TYPE
Oracle output: NVL(CAST(COLUMN_NAME AS VARCHAR(4000)), ' ')||'UUlHUa'||NVL(CAST(DATA_TYPE AS VARCHAR(4000)), ' ')
Oracle scope: SELECT COLUMN_NAME, DATA_TYPE FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME='%s'
Microsoft SQL Server input: name,master.dbo.fn_varbintohexstr(password)
Microsoft SQL Server output: ISNULL(CAST(name AS VARCHAR(8000)), ' ')+'nTBdow'+ISNULL(CAST(master.dbo.fn_varbintohexstr(password) AS VARCHAR(8000)), ' ')
Microsoft SQL Server scope: SELECT name, master.dbo.fn_varbintohexstr(password) FROM master..sysxlogins
@param fields: fields string to be processed
@type fields: C{str}
@return: fields string nulled, casted and concatened
@rtype: C{str}
"""
if not Backend.getDbms():
return fields
if fields.startswith("(CASE") or fields.startswith("(IIF") or fields.startswith("SUBSTR") or fields.startswith("MID(") or re.search(r"\A'[^']+'\Z", fields):
nulledCastedConcatFields = fields
else:
fieldsSplitted = splitFields(fields)
dbmsDelimiter = queries[Backend.getIdentifiedDbms()].delimiter.query
nulledCastedFields = []
for field in fieldsSplitted:
nulledCastedFields.append(self.nullAndCastField(field))
delimiterStr = "%s'%s'%s" % (dbmsDelimiter, kb.chars.delimiter, dbmsDelimiter)
nulledCastedConcatFields = delimiterStr.join(field for field in nulledCastedFields)
return nulledCastedConcatFields
def getFields(self, query):
"""
Take in input a query string and return its fields (columns) and
more details.
Example:
Input: SELECT user, password FROM mysql.user
Output: user,password
@param query: query to be processed
@type query: C{str}
@return: query fields (columns) and more details
@rtype: C{str}
"""
prefixRegex = r"(?:\s+(?:FIRST|SKIP)\s+\d+)*"
fieldsSelectTop = re.search(r"\ASELECT\s+TOP\s+[\d]+\s+(.+?)\s+FROM",
query, re.I)
fieldsSelectRownum = re.search(
r"\ASELECT\s+([^()]+?),\s*ROWNUM AS LIMIT FROM", query, re.I)
fieldsSelectDistinct = re.search(
r"\ASELECT%s\s+DISTINCT\((.+?)\)\s+FROM" % prefixRegex, query,
re.I)
fieldsSelectCase = re.search(
r"\ASELECT%s\s+(\(CASE WHEN\s+.+\s+END\))" % prefixRegex, query,
re.I)
fieldsSelectFrom = re.search(
r"\ASELECT%s\s+(.+?)\s+FROM " % prefixRegex, query, re.I)
fieldsExists = re.search(r"EXISTS\(([^)]*)\)\Z", query, re.I)
fieldsSelect = re.search(r"\ASELECT%s\s+(.*)" % prefixRegex, query,
re.I)
fieldsSubstr = re.search(r"\A(SUBSTR|MID\()", query, re.I)
fieldsMinMaxstr = re.search(r"(?:MIN|MAX)\(([^\(\)]+)\)", query, re.I)
fieldsNoSelect = query
_ = zeroDepthSearch(query, " FROM ")
if not _:
fieldsSelectFrom = None
if fieldsSubstr:
fieldsToCastStr = query
elif fieldsMinMaxstr:
fieldsToCastStr = fieldsMinMaxstr.groups()[0]
elif fieldsExists:
fieldsToCastStr = fieldsSelect.groups()[0]
elif fieldsSelectTop:
fieldsToCastStr = fieldsSelectTop.groups()[0]
elif fieldsSelectRownum:
fieldsToCastStr = fieldsSelectRownum.groups()[0]
elif fieldsSelectDistinct:
fieldsToCastStr = fieldsSelectDistinct.groups()[0]
elif fieldsSelectCase:
fieldsToCastStr = fieldsSelectCase.groups()[0]
elif fieldsSelectFrom:
fieldsToCastStr = query[:unArrayizeValue(_)] if _ else query
fieldsToCastStr = re.sub(r"\ASELECT%s\s+" % prefixRegex, "",
fieldsToCastStr)
elif fieldsSelect:
fieldsToCastStr = fieldsSelect.groups()[0]
else:
fieldsToCastStr = fieldsNoSelect
# Function
if re.search("\A\w+\(.*\)", fieldsToCastStr,
re.I) or (fieldsSelectCase
and "WHEN use" not in query) or fieldsSubstr:
fieldsToCastList = [fieldsToCastStr]
else:
fieldsToCastList = splitFields(fieldsToCastStr)
return fieldsSelectFrom, fieldsSelect, fieldsNoSelect, fieldsSelectTop, fieldsSelectCase, fieldsToCastList, fieldsToCastStr, fieldsExists