PyODBC, cursor.execute() won't insert parameters into SQL String

Question

I am using pypyodbc to insert data into a database and when I use the cursor.execute() command I try to put the sql string and the 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月19日T05: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")

• Dilettant: The placeholder format are sometimes '?' sometimes ... to me it seems the pypyodbc should be queried (or source read) which param style it expects, then use that, ...
• M. Barbieri: Like instead of "%s" just put the "?" @Dilettant
• Dilettant: Yes :-) something pypyodbc as setup / initialized expects.
• M. Barbieri: That seems to be working, however, the reason I switched from ?/%s to simply concatenating to the SQL String was because the DATE and TIME fields were not converting from "Character String" so now I get this error: "The SQL contains 2 parameter markers, but 4 parameters were supplied"
• Dilettant: Do the formats and contents of the datetime.datetime.today()et al. call results match the expectation of the pypyodbc resp. database? Can you print some tresults and fill in verbatim these into an sql live query against the db to see if it works - maybe others spotting something obvious will jump in ...

Answer 1

Use datetime.strptime() to convert the attack_datetime value to a datetime object before passing the value to SQL Server.

For example, passing a datetime formatted string fails with the same error message you receive

...
# assumes connection and cursor objects initialized
create_date_str = "2016-06-16T01:23:45.67890"
sql = "select name, create_date from sys.databases where create_date = ?"
rows = cursor.execute(sql, create_date_str).fetchall()

Raises

Traceback (most recent call last): File "", line 1, in pyodbc.DataError: ('22007', '[22007] [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

While converting the datetime string to a datetime object succeeds

...
# convert datetime string to object, specifying input format
create_date = datetime.datetime.strptime(create_date_str, '%Y-%m-%dT%H:%M:%S.%f')
rows = cursor.execute(sql, create_date).fetchall()

• M. Barbieri: Thanks for the response, I tried that, however I am getting the error "'datetime.datetime' object is not subscriptable", any idea what that's from?
• Bryan: Likely still using attack_datetime[0] for the parameter collection, instead of attack_datetime. See this explanation of subscriptable.
• M. Barbieri: I mean, attack_datetime = attack_date[0] + "T" + timestamp[0], however I do not explicitly write "strptime(attack_datetime[0])"
• M. Barbieri: It worked, I changed a little ".*" on the regex end for the attackDate and the formatting went through. Thanks for all the help!