Python Invalid parameter type when trying to execute insert query

Question

I'm trying to execute this query however I'm getting the error and I'm using Microsoft SQL Server as database.

import urllib.request as urllib
import socket
import pyodbc
from datetime import datetime
import ssl
import OpenSSL
import re


#Timestamp for undersøgelse
timestamp = datetime.now().strftime('%d-%m-%Y %H:%M:%S')



#Responseheader request
request = urllib.Request('http://ucn.dk')
request.add_header('User-Agent', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36')
response = urllib.urlopen(request)
rdata = response.info()
ipaddr = socket.gethostbyname(request.origin_req_host)


def get_certificate(host, port=443):
    context = ssl.create_default_context()
    conn = socket.create_connection((host, port))
    sock = context.wrap_socket(conn, server_hostname=host)
    der_cert = sock.getpeercert(True)
    sock.close()
    return ssl.DER_cert_to_PEM_cert(der_cert)


certificate = get_certificate(request.origin_req_host)
x509 = OpenSSL.crypto.load_certificate(OpenSSL.crypto.FILETYPE_PEM, certificate)
Pubkeyobj = x509.get_pubkey()
pubKeyString = OpenSSL.crypto.dump_publickey(OpenSSL.crypto.FILETYPE_PEM, Pubkeyobj)
pubkey = b'\n'.join(pubKeyString.splitlines()[1:-1])
keylength = Pubkeyobj.bits()
certvalidfrom = datetime.strptime(x509.get_notBefore().decode('ascii'), '%Y%m%d%H%M%SZ')
certvalidtill = datetime.strptime(x509.get_notAfter().decode('ascii'), '%Y%m%d%H%M%SZ')
extensions = (x509.get_extension(i) for i in range(x509.get_extension_count()))
extension_data = {e.get_short_name(): str(e) for e in extensions}


subj = x509.get_subject()
subjCN = x509.get_subject().CN
subjAlt = extension_data[b'subjectAltName']
serial = x509.get_serial_number()
issuerCN = x509.get_issuer().CN
sign = x509.get_signature_algorithm()





#SQL Connection til local database
con = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
                      'Server=DESKTOP-THV2IDL;'
                      'Database=host;'
                      'Trusted_Connection=yes;')

cursor = con.cursor()


con.execute('INSERT INTO host.dbo.certificate (host, Subject, CommonName, AlternativeNames, PublicKey, KeyLength(BITS), SerialNumber, Validfrom, Validtill, Issuer(CN), CSA, Timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', 
           (request.full_url, subj, subjCN, subjAlt, pubkey, keylength, serial, certvalidfrom, certvalidtill, issuerCN, sign, timestamp))

con.commit()

I get this error:

File "c:/Users/Farzad/Desktop/Python/Webscraping/Responseheaderinfo.py", line 76, in
(request.full_url, subj, subjCN, subjAlt, pubkey, keylength, serial, certvalidfrom, certvalidtill, issuerCN, sign, timestamp))
pyodbc.ProgrammingError: ('Invalid parameter type. param-index=1 param-type=X509Name', 'HY105')

score 0 · Accepted Answer · answered Oct 19 '19 at 19:23

The doc says that X509Req's get_subject() method returns a X509Name object. This object can't be used as Subject field of your query; because they're Python objects and your MSSQL Server can't understand that.

Maybe you have designed this field to be a VARCHAR or something; so you need to use one of methods of that X509Name object to get the actual value you want to store (A string, maybe?) in your database. Take a look at X509Name's characteristics here.

Or you may want to convert the whole of this object into byte array using Python's pickle.dumps() method and save it to your DB as binary.

Yes thank you! I figured out I have to give the params new string objects and now it works! — fazzerking, Oct 19 '19 at 19:37

Python Invalid parameter type when trying to execute insert query

1 Answers1