I use pypyodbc to insert data into the database, and when I use the cursor.execute () command, I try to put the sql string and parameters, but I get the following error:
SELECT uid FROM HP_DATA WHERE (hpName =? AND processID =? AND ipAddress =? AND port =? AND usernameTried =? AND passwordTried =? AND fileID =?);
INSERT INTO HP_DATA_LOGIN_DETAIL (uid, attackDate, gmtOffset) VALUES (?, CONVERT (DATETIME,?, 126),?);
2016-04-19T05: 40: 58.000
('22007', '[22007] [Microsoft] [ODBC SQL Server Driver] [SQL Server] Conversion failed when converting date and / or time from character string.')
This is my code:
# Inserting the info of the file that is read into HP_DATA_LOG
# This is supposed to allow us to check in the future, what files are read / unread
print ("Inserting File data into HP_DATA_LOG ...")
log_file_date_read = datetime.datetime.today ()
log_file_date_added = datetime.datetime.fromtimestamp (os.path.getctime (path)). strftime ("% Y-% m-% d% H:% M:% S.% f")
file_size = os.path.getsize (path)
#log_sql = "INSERT INTO HP_DATA_LOG (dateRead, dateAdded, fileName, fileSize) VALUES ("
#log_sql + = "'" + str (log_file_date_read) + "',"
#log_sql + = "'" + str (log_file_date_added) + "',"
#log_sql + = "'" + path + "',"
#log_sql + = "" + str (file_size) + ");"
log_params = (log_file_date_read, log_file_date_added, file_name, file_size)
log_sql = '' 'INSERT INTO HP_DATA_LOG (dateRead, dateAdded, fileName, fileSize) VALUES (?,?,?,?);' ''
print (log_sql)
cursor.execute (log_sql, log_params)
# Getting the auto-generated fileID from the table
print ("Getting fileID ...")
#get_fileID_sql = "SELECT fileID FROM HP_DATA_LOG WHERE"
#get_fileID_sql + = "(dateRead = '" + str (log_file_date_read) + "'"
#get_fileID_sql + = "AND dateAdded = '" + str (log_file_date_added) + "'"
#get_fileID_sql + = "AND fileName = '" + path + "'"
#get_fileID_sql + = "AND fileSize = '" + str (file_size) + "');"
fileID_params = (log_file_date_read, log_file_date_added, file_name, file_size)
get_fileID_sql = '' 'SELECT fileID FROM HP_DATA_LOG WHERE (dateRead =? AND dateAdded =? AND fileName =? AND fileSize =?);' ''
print (get_fileID_sql)
cursor.execute (get_fileID_sql, fileID_params)
fileID = cursor.fetchone ()
# Logging the attack by Inserting the HoneyPot data into HP_DATA
hp_name = re.findall ('- \ d \ d: \ d \ d \ s (. *) \ ssshd', line)
pid = re.findall ('\ ssshd-22 \ [(\ d +) \]', line)
ip_add = re.findall ('\ sIP: \ s (\ d +. \ d +. \ d +. \ d +) \ s', line)
port = re.findall ('\ s. \ d + \ sPass (. *) Log \ s', line)
if port == "2222":
port = '2222'
else:
port = '22'
username = re.findall ('\ sUsername: \ s (. *) \ sPas', line)
password = re.findall ('\ sPassword: \ s (. *)', line)
#sql = "INSERT INTO HP_DATA (hpName, processID, ipAddress, port, usernameTried, passwordTried, fileID) VALUES ("
#sql + = "'" + hp_name [0] + "',"
#sql + = str (int (pid [0])) + ","
#sql + = "'" + ip_add [0] + "',"
#sql + = str (port) + ","
#sql + = "'" + username [0] + "',"
#sql + = "'" + password [0] + "',"
#sql + = str (list (fileID) [0]) + ");"
sql_params = (hp_name [0], pid [0], ip_add [0], port, username [0], password [0], fileID [0])
sql = '' 'INSERT INTO HP_DATA (hpName, processID, ipAddress, port, usernameTried, passwordTried, fileID) VALUES (?,?,?,?,?,?,?);' '' '
print (sql)
cursor.execute (sql, sql_params)
#
#user_sql = r "SELECT uid FROM HP_DATA WHERE ("
#user_sql + = "hpName = '" + hp_name [0] + "' AND"
#user_sql + = "processID =" + str (int (pid [0])) + "AND"
#user_sql + = "ipAddress = '" + ip_add [0] + "' AND"
#user_sql + = "port =" + str (port) + "AND"
#user_sql + = r "usernameTried = '" + username [0] + "' AND"
#user_sql + = r "passwordTried = '" + password [0] + "' AND"
#user_sql + = "fileID =" + str (list (fileID) [0]) + ");"
user_sql_params = (hp_name [0], pid [0], ip_add [0], port, username [0], password [0], fileID [0])
user_sql = '' 'SELECT uid FROM HP_DATA WHERE (hpName =? AND processID =? AND ipAddress =? AND port =? AND usernameTried =? AND passwordTried =? AND fileID =?);' '' '
print (user_sql)
cursor.execute (user_sql, user_sql_params)
uid = cursor.fetchone ()
# Inserting date and time information in order to prevent duplicates
attack_date = re.findall ('(\ d {4} - \ d \ d- \ d \ d) T', line)
timestamp = re.findall ('T (\ d \ d: \ d \ d: \ d \ d. *). * -. * sshd', line)
attack_datetime = attack_date [0] + "T" + timestamp [0] + ".000"
gmt_offset = re.findall ('\ d \ d: \ d \ d: \ d \ d. * - (\ d \ d: \ d \ d) \ s', line)
#hp_detail_sql = r "INSERT INTO HP_DATA_LOGIN_DETAIL (uid, attackDate, attackTime, gmtOffset) VALUES ("
#hp_detail_sql + = "" + str (uid [0]) + ","
#hp_detail_sql + = "'" + attackDate [0] + "',"
#hp_detail_sql + = "'" + timestamp [0] + "',"
#hp_detail_sql + = "'" + gmt_offset [0] + "');"
hp_detail_sql_params = (uid [0], attack_datetime [0], gmt_offset [0])
hp_detail_sql = '' 'INSERT INTO HP_DATA_LOGIN_DETAIL (uid, attackDate, gmtOffset) VALUES (?,?,?);' '' '
print (hp_detail_sql)
print (attack_datetime)
cursor.execute (hp_detail_sql, hp_detail_sql_params)
print ("Executed insert statements")