def __init__(self,
db1,
db2,
u1="postgres",
u2="postgres",
p1=None,
p2=None,
h1="localhost",
h2="localhost",
sql=0):
self._db1name = db1
self._db2name = db2
self._db1user = u1
self._db2user = u2
self._db1pass = p1
self._db2pass = p2
self._db1host = h1
self._db2host = h2
self._db1 = pg.DB(dbname=self._db1name,
host=self._db1host,
user=self._db1user)
self._db2 = pg.DB(dbname=self._db2name,
host=self._db2host,
user=self._db2user)
self._sql = sql
def db_connect(pwd):
mysql.connector.connect(host='localhost', user='******',
password='') # Noncompliant
# ^^^^^^^^^^^
mysql.connector.connect(host='localhost', password='',
user='******') # Noncompliant
mysql.connector.connect('localhost', 'sonarsource', '') # Noncompliant
# ^^
mysql.connector.connect('localhost', 'sonarsource', 'hello') # OK
mysql.connector.connect('localhost', 'sonarsource', password='******') # OK
mysql.connector.connect('localhost', 'sonarsource', password=pwd) # OK
mysql.connector.connect('localhost', 'sonarsource', pwd) # OK
mysql.connector.connect('localhost', 'sonarsource') # OK
mysql.connector.connect('localhost', 'sonarsource', **dict) # OK
mysql.connector.connection.MySQLConnection(host='localhost',
user='******',
password='') # Noncompliant
pymysql.connect(host='localhost', user='******',
password='') # Noncompliant
pymysql.connections.Connection(host='localhost',
user='******',
password='') # Noncompliant
psycopg2.connect(host='localhost', user='******',
password='') # Noncompliant
pgdb.connect(host='localhost', user='******',
password='') # Noncompliant
pg.DB(host='localhost', user='******', passwd='') # Noncompliant
pg.DB('dbname', 'localhost', 5432, 'opt', 'postgres', '') # Noncompliant
pg.connect(host='localhost', user='******', passwd='') # Noncompliant
pg.DB(host='localhost', user='******', passwd=pwd) # Compliant
pg.DB('dbname', 'localhost', 5432, 'opt', 'postgres', pwd) # Compliant
def InsertEmployeePerson(Name, MiddleName, LastName, IdentityDoc, IdAddress, Status, IdJob, IdStore, HireDate):
connection = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root')
encabezado = "SELECT InsertEmployee"
cuerpo = str(Name) , str(MiddleName), str(LastName) , str(IdentityDoc),IdAddress, Status,IdJob,IdStore,str(HireDate)
final = encabezado + str(cuerpo) + ";"
print( connection.query(str(final)))
connection.close()
connection = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root')
data = connection.query("SELECT IdPerson FROM Person ORDER BY IdPerson DESC LIMIT 1;")
connection = mysql.connector.connect(host='localhost',
database= db + str(IdStore),
user='******',
password='******')
cursor = connection.cursor()
query = "INSERT INTO Person VALUES "
query += str((data[0][0], Name, MiddleName, LastName, IdentityDoc, IdAddress)) + ";"
print(query)
cursor.execute(query)
connection.commit()
connection.close()
def setUp(self):
# Call superclass setUp in case this does something in the future
dbapi20.DatabaseAPI20Test.setUp(self)
try:
con = self._connect()
con.close()
except pgdb.Error: # try to create a missing database
import pg
try: # first try to log in as superuser
db = pg.DB('postgres',
dbhost or None,
dbport or -1,
user='******')
except Exception: # then try to log in as current user
db = pg.DB('postgres', dbhost or None, dbport or -1)
db.query('create database ' + dbname)
def parse_topics(self):
self.db = pg.DB(dbname=self.config['db_name'],
host=self.config['db_host'],
port=self.config['db_port'],
user=self.config['db_user'],
passwd=self.config['db_password'])
self.db.begin()
for i, topic_id in enumerate(self.get_next_topic_id_to_parse()):
print('Parsing topic ' + str(topic_id))
post_pages = self.get_post_pages(topic_id)
posts = self.parse_topic(post_pages)
if not posts:
print('Only empty posts in topic, mark it as invalid')
self.mark_topic_as_invalid(topic_id)
else:
posts = self.add_topic_id_post_numbers(topic_id, posts)
self.save_posts(posts)
if not (i + 1) % self.config['batch_size']:
print('Parsed batch of topics: commit')
self.db.commit()
self.db.begin()
self.db.close()
def main(arguments):
'Main entry point'
parser = argparse.ArgumentParser(description='''
Extracts test run data into csv files. Saves runs to run-N.csv.
The default behavior is to only extract the most recent run.
''')
parser.add_argument('runs',
metavar='N',
type=int,
nargs='*',
help='A run to extract to csv. Saved as run-N.csv')
args = parser.parse_args(args=arguments)
pgdb = pg.DB(dbname='qfp', host='localhost', user='******', passwd='qfp123')
runs = args.runs
if len(runs) == 0:
query_runs = pgdb.query('select index from runs;')
idx = query_runs.dictresult()[-1]['index']
runs.append(idx)
for run in runs:
filename = 'run-{0:02}.csv'.format(run)
sys.stdout.write('writing ' + filename + ' ...')
sys.stdout.flush()
query_tests = pgdb.query('select * from tests where run = $1;', run)
query_results_to_file(filename, query_tests)
sys.stdout.write(' done\n')
sys.stdout.flush()
def scrape_topics(config, subforum_id, first_page, last_page):
db = pg.DB(dbname=config['db_name'],
host=config['db_host'],
port=config['db_port'],
user=config['db_user'],
passwd=config['db_password'])
subforum_name = get_subforum_name(db, subforum_id)
for page in range(first_page, last_page + 1):
topics = get_topics(page, subforum_name)
new_topic_count = len(topics)
if not topics:
print('Reached end of {} on page {}'.format(subforum_name, page))
break
for topic in topics:
topic['subforum_id'] = subforum_id
try:
db.begin()
db.insert('topics', topic)
except pg.IntegrityError:
new_topic_count -= 1
finally:
db.commit()
print('Jellied {} new topics from subforum id {} page {}'.format(
new_topic_count, subforum_id, page + 1))
db.close()
def phoneApp(db_name):
try:
db = pg.DB(dbname=db_name)
# db.debug = True
# print '\nDatabase opened...'
except:
print 'No phonebook database exists!'
exit(0)
if 'public.phonebook' in db.get_tables():
# print 'Phonebook accessed!'
phonebook(db)
else:
try:
db.query("""
CREATE TABLE phonebook (
id serial PRIMARY KEY,
name varchar NOT NULL UNIQUE,
phone varchar,
email varchar UNIQUE
)""")
print 'Phonebook created!'
phonebook(db)
except:
print 'Phonebook table could not be created!'
phonebook(db)
def deactivateAdmin(idEmployee):
idStore = getAdminStore(idEmployee)
newAdmin = getNonAdminsForStore(idStore, idEmployee)
if newAdmin == -1:
deactivateStore(idStore)
else:
connection = pg.DB(host='localhost',
user='******',
passwd='root',
dbname='datawarehouse')
query = "UPDATE Employee SET Status = 0 WHERE IdPerson = " + str(
idEmployee) + ";"
connection.query(query)
query = "UPDATE Store SET IdAdmin = " + str(newAdmin) + " "
query += "WHERE IdStore = " + str(idStore) + ";"
connection.query(query)
query = "UPDATE EmployeeJob SET IdJob = 1 WHERE IdPerson = " + str(
newAdmin) + ";"
connection.query(query)
print(query)
connection.close()
def activateItem(idItem):
query = "UPDATE Item SET Status = 1 WHERE IdItem = " + str(idItem) + ";"
connectionpsql = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root')
connectionpsql.query(query)
connectionpsql.close()
storeList = getIdStoreList()
for i in storeList:
try:
connection = mysql.connector.connect(host='localhost',
database=db + str(i),
user='******',
password='******')
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
connection.close()
except:
continue
def __init__(self):
status = False
maximumRetry = 3
count = 0
self.conn = None
while count < maximumRetry and self.conn is None:
try:
count += 1
self.conn = pg.DB(
host="localhost",
user="******",
passwd="PASSWORD",
dbname="DBNAME",
)
if self.conn is not None:
status = True
break
except pg.InternalError:
print("Trying to reconnect with the database")
except Exception as err:
print(err)
if status:
print("Connected with the database successfully.")
result = self.conn.query("SELECT fname, lname FROM employee")
for firstname, lastname in result.getresult():
print(firstname, lastname)
self.conn.close()
else:
print("Connection with the database was Unsucessful!")
def fragState(idStore):
connection = pg.DB(host='localhost',
user='******',
passwd='root',
dbname='datawarehouse')
data = connection.query("SELECT * FROM State;")
connection.close()
connection = mysql.connector.connect(host='localhost',
database=db + str(idStore),
user='******',
password='******')
cursor = connection.cursor()
for line in data:
query = "INSERT INTO State (IdState, IdCountry, Name) VALUES "
query += str( line ) + ";"
cursor.execute(query)
connection.commit()
connection.close()
def fragBrand(idStore):
connection = pg.DB(host='localhost',
user='******',
passwd='root',
dbname='testpsql')
data = connection.query("SELECT * FROM Brand;")
connection.close()
connection = mysql.connector.connect(host='localhost',
database=db + str(idStore),
user='******',
password='******')
cursor = connection.cursor()
for line in data:
query = "INSERT INTO Brand VALUES "
query += str( line ) + ";"
cursor.execute(query)
connection.commit()
connection.close()
def getOwners():
db = pg.DB(host=DBHOST, user=DBUSER, passwd=DBPASS, dbname=DBNAME)
query = db.query("SELECT * FROM owners order by yearstarted asc")
result_list = query.namedresult()
db.close()
print result_list
return result_list
def fragJob(idStore):
connection = pg.DB(host='localhost',
user='******',
passwd='root',
dbname='testpsql')
data = connection.query("SELECT * FROM Job;")
connection.close()
connection = mysql.connector.connect(host='localhost',
database=db + str(idStore),
user='******',
password='******')
cursor = connection.cursor()
for line in data:
query = "INSERT INTO Job (IdJob, Job, Salary) VALUES "
query += str( (line[0], line[1], line[2]) ) + ";"
cursor.execute(query)
connection.commit()
connection.close()
def fragAddress(idStore):
fragCountry(idStore)
fragState(idStore)
fragCity(idStore)
connection = pg.DB(host='localhost',
user='******',
passwd='root',
dbname='testpsql')
data = connection.query("SELECT * FROM Address;")
connection.close()
connection = mysql.connector.connect(host='localhost',
database=db + str(idStore),
user='******',
password='******')
cursor = connection.cursor()
for line in data:
query = "INSERT INTO Address VALUES "
query += str( line ) + ";"
cursor.execute(query)
connection.commit()
connection.close()
def generateWarehouseRequest():
connection = pg.DB(dbname='testpsql', host='127.0.0.1', port = 5432, user='******', passwd='root')
data = getItemsWarehouse()
idRequest = connection.query("SELECT IdRequest FROM WarehouseRequest ORDER BY IdRequest DESC;")
try:
idRequest = idRequest[0][0] + 1
except:
idRequest = 1
today = date.today()
requestDate = today.strftime("%Y-%m-%d")
connection.query("INSERT INTO WarehouseRequest VALUES " + str( (idRequest, requestDate) ) + ";")
for i in data:
query = "INSERT INTO WarehouseRequestItem VALUES "
query += str( (idRequest, i, 1) ) + ";"
connection.query(query)
connection.close()
restockWarehouse(data)
def fragPerson(idStore):
connection = pg.DB(host='localhost',
user='******',
passwd='root',
dbname='testpsql')
data = connection.query("SELECT * FROM Person;")
connection.close()
connection = mysql.connector.connect(host='localhost',
database=db + str(idStore),
user='******',
password='******')
cursor = connection.cursor()
for line in data:
query = "INSERT INTO Person (IdPerson, FirstName, MiddleName, LastName, IdentityDoc, IdAddress) VALUES "
if str(line[2]) == "None":
query += "(" + str(line[0]) + ", '" + str(line[1]) + "', NULL, '" + str(line[3]) + "', '"
query += str(line[4]) + "', " + str(line[5]) + ");"
else:
query += str( (line[0], line[1], line[2], line[3], line[4], line[5]) ) + ";"
cursor.execute(query)
connection.commit()
connection.close()
def fragItems(idStore):
fragCategory(idStore)
fragBrand(idStore)
connection = pg.DB(host='localhost',
user='******',
passwd='root',
dbname='datawarehouse')
data = connection.query("SELECT * FROM Item;")
connection.close()
connection = mysql.connector.connect(host='localhost',
database=db + str(idStore),
user='******',
password='******')
cursor = connection.cursor()
for line in data:
query = "INSERT INTO Item VALUES "
query += str( line ) + ";"
cursor.execute(query)
connection.commit()
connection.close()
def _connect_to_postgres(self):
db = pg.DB(dbname=POSTGRES_DB,
host=self._POSTGRES_HOST,
port=POSTGRES_PORT,
user=POSTGRES_USERNAME,
passwd=POSTGRES_PASSWORD)
return db
def insertWithCeroStock(idItem, idStore):
connectionpsql = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root')
query = "INSERT INTO ItemStore VALUES "
query += str( (idStore,idItem,0) ) + ";"
connectionpsql.query(query)
def InsertItem(idBrand, description, idCategory, price, stores):
idItem = code = getNextItem()
today = date.today()
entryDate = today.strftime("%Y-%m-%d")
query = "INSERT INTO Item VALUES "
query += str( (idItem,code,idBrand,description,idCategory,price,1,entryDate) ) + ";"
connectionpsql = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root')
connectionpsql.query(query)
connectionpsql.close()
storeIds = getIdStoreList()
for id in storeIds:
try:
insertItemStore(query, id)
except:
continue
for id in stores:
try:
insertWithCeroStock(idItem, id)
insertWithCeroStockToStore(idItem, id)
except:
continue
insertWithCeroStockToWarehouse(idItem)
def isAdmin(idEmployee):
query = "SELECT E.IdPerson FROM Employee E "
query += "INNER JOIN EmployeeJob EJ ON EJ.IdPerson = E.IdPerson "
query += "WHERE EJ.IdJob = 1 AND E.Status = 1;"
connection = pg.DB(host='localhost',
user='******',
passwd='root',
dbname='datawarehouse')
data = connection.query(query)
connection.close()
try:
for line in data:
if line[0] == idEmployee:
return True
except:
return False
return False
def db(self, pwd):
mysql.connector.connect(host='localhost', user='******', password='******') # Noncompliant
mysql.connector.connection.MySQLConnection(host='localhost', user='******', password='******') # OK (avoid FPs)
mysql.connector.connect(host='localhost', user='******', password=pwd) # OK
mysql.connector.connection.MySQLConnection(host='localhost', user='******', password=pwd) # OK
mysql.connector.connection.MySQLConnection(host='localhost', user='******', password='') # OK
mysql.connector.connection.MySQLConnection(host='localhost', user='******', "") # OK
pymysql.connect(host='localhost', user='******', password='******') # Noncompliant
pymysql.connect('localhost', 'root', 'password') # Noncompliant {{Review this potentially hard-coded credential.}}
# ^^^^^^^^^^
pymysql.connections.Connection(host='localhost', user='******', password='******') # OK (avoid FPs)
pymysql.connections.Connection('localhost', 'root', 'password') # Noncompliant
pymysql.connect(host='localhost', user='******', password=pwd) # OK
pymysql.connect('localhost', 'root', pwd) # OK
pymysql.connections.Connection(host='localhost', user='******', password=pwd) # OK
pymysql.connections.Connection('localhost', 'root', pwd) # OK
pymysql.connect('localhost', 'root', '') # Compliant
pymysql.connect(host='localhost', user='******', password='') # Compliant
pymysql.connections.Connection(host='localhost', user='******', password='') # Compliant
pymysql.connections.Connection('localhost', 'root', '') # Compliant
psycopg2.connect(host='localhost', user='******', password='******') # Noncompliant
psycopg2.connect(host='localhost', user='******', password=pwd,) # OK
pgdb.connect(host='localhost', user='******', password='******') # Noncompliant
pgdb.connect('localhost', 'postgres', 'password') # Noncompliant
pgdb.connect(host='localhost', user='******', password=pwd) # OK
pgdb.connect('localhost', 'postgres', pwd) # OK
pg.DB(host='localhost', user='******', passwd='Azerty123') # Noncompliant
pg.DB(None, 'localhost', 5432, None, 'postgres', 'password') # Noncompliant
pg.DB(host='localhost', user='******', passwd=pwd) # OK
pg.DB(None, 'localhost', 5432, None, 'postgres', pwd) # OK
pg.connect(host='localhost', user='******', passwd='Azerty123') # Noncompliant
pg.connect(None, 'localhost', 5432, None, 'postgres', 'password') # Noncompliant
pg.connect(host='localhost', user='******', passwd=pwd) # OK
pg.connect(None, 'localhost', 5432, None, 'postgres', pwd) # OK
pg.connect(host='localhost', user='******', passwd='') # Compliant
pg.connect(None, 'localhost', 5432, None, 'postgres', '') # Compliant
random.call(None, password = 42) # OK
random.call(None, password = "******") # Noncompliant
random.call(None, password = "") # OK
pg.connect(*unpack, 'localhost', 5432, None, 'postgres', pwd) # OK
def ConsultStore(IdStore):
connection = pg.DB(dbname='sk8database', host='127.0.0.1', port = 5432, user='******', passwd='root')
query = "SELECT * FROM ConsultStore(" + str(IdStore) + ");"
print( connection.query(query))
connection.close()
def getPromoStore(IdStore):
connection = pg.DB(dbname='testpsql', host='127.0.0.1', port = 5432, user='******', passwd='root')
query = "SELECT * FROM getPromoStore(" + str(IdStore) + ");"
print( connection.query(query))
connection.close()
def get_connection():
"""
Sets up the postgreSQL connection. Uses settings from the config file.
"""
return pg.DB(host=os.environ.get('DBHOST', True),
user=os.environ.get('DBUSER', True),
passwd=os.environ.get('DBPASS', True),
dbname=os.environ.get('DBNAME', True))
def ConsultEmployee(IdEmpleado):
connection = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root')
query = "SELECT * FROM ConsultEmployee(" + str(IdEmpleado) + ");"
print( connection.query(query))
connection.close()
def get_connection():
"""
Sets up the postgreSQL connection by loading in db settings from config
"""
return pg.DB(host=config.DBHOST,
user=config.DBUSER,
passwd=config.DBPASS,
dbname=config.DBNAME)
def InsertEmployee_aux(idperson):
connection = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root')
query = "INSERT INTO Employee VALUES ( " + str(idperson) + ", 1 );"
data = connection.query(query)
connection.close()
