0

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-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")
M. Barbieri
  • 512
  • 2
  • 13
  • 27
  • 1
    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, ... – Dilettant Jun 16 '16 at 14:01
  • Like instead of "%s" just put the "?" @Dilettant – M. Barbieri Jun 16 '16 at 14:12
  • Yes :-) something pypyodbc as setup / initialized expects. – Dilettant Jun 16 '16 at 14:20
  • 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" – M. Barbieri Jun 16 '16 at 14:34
  • 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 ... – Dilettant Jun 16 '16 at 14:40
  • I updated the question, I replaced the concatenation with "?" and this is the error that I get – M. Barbieri Jun 16 '16 at 15:52
  • Not sure, but maybe try `INSERT INTO HP_DATA_LOG('dateRead', ...` if the conversion from string to date fails or it is in the payload (as I suggested in the former comment) to try literals coming out of the python dateteim method calls ... – Dilettant Jun 16 '16 at 16:01
  • Right, I agree, however, the data is dynamic, so I can't just add a "\" to every input, because it's dynamically taken from a text file – M. Barbieri Jun 16 '16 at 16:17
  • Multi-language stacks are sometimes mind boggling, sorry, what I meant was not what I typed: The `VALUES(?, ...)` might need either changing into `VALUES('?', ...)` or if this is not sufficient, then you may need `VALUES(CAST('?' AS DATE), ...)` or something like this. Sorry again for not being sync'ed brain-hand-wise ;-) – Dilettant Jun 16 '16 at 16:55
  • Yeah, I tried all of those, no luck.... – M. Barbieri Jun 16 '16 at 16:57
  • Only the last statement failing? I guess no, But the last statement shouldn't that be (deriving the convert call syntax from your sample): `INSERT INTO HP_DATA_LOGIN_DETAIL(uid, attackDate, gmtOffset) VALUES(?, CONVERT(DATETIME, '?', 126), ?);` (I do not know at this second, what 126 in that convert function is for, but as long as it is not 42 ... ? – Dilettant Jun 16 '16 at 17:02
  • What is the data type of the `attackDate` column in SQL Server? – Bryan Jun 16 '16 at 17:17
  • The attackDate is DATETIME @Bryan – M. Barbieri Jun 16 '16 at 17:21

1 Answers1

3

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()
Bryan
  • 17,112
  • 7
  • 57
  • 80
  • 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? – M. Barbieri Jun 16 '16 at 18:28
  • Likely still using `attack_datetime[0]` for the parameter collection, instead of `attack_datetime`. See this explanation of [subscriptable](http://stackoverflow.com/questions/216972/in-python-what-does-it-mean-if-an-object-is-subscriptable-or-not). – Bryan Jun 16 '16 at 18:32
  • I mean, attack_datetime = attack_date[0] + "T" + timestamp[0], however I do not explicitly write "strptime(attack_datetime[0])" – M. Barbieri Jun 16 '16 at 18:44
  • It worked, I changed a little ".*" on the regex end for the attackDate and the formatting went through. Thanks for all the help! – M. Barbieri Jun 17 '16 at 02:52