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-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")

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
– Dilettant, Commented Jun 16, 2016 at 14:01
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
– M. Barbieri, Commented Jun 16, 2016 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
– Dilettant, Commented Jun 16, 2016 at 14:40

Bryan · Accepted Answer · 2017-04-19 14:56:17Z

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()

edited Apr 19, 2017 at 14:56

answered Jun 16, 2016 at 18:03

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

M. Barbieri Over a year ago

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 Over a year ago

Likely still using attack_datetime[0] for the parameter collection, instead of attack_datetime. See this explanation of subscriptable.

M. Barbieri Over a year ago

I mean, attack_datetime = attack_date[0] + "T" + timestamp[0], however I do not explicitly write "strptime(attack_datetime[0])"

M. Barbieri Over a year ago

It worked, I changed a little ".*" on the regex end for the attackDate and the formatting went through. Thanks for all the help!

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related