Python crashes when more than one parameter is passed to an SQL query

415 Views Asked by At

When using adodbapi 2.6 package on Windows 7, Python will crash (generate a "Python has stopped working" popup window) when more than one parameter is passed to a SQL query. I am trying to pass parameters to a ADO connection cursor to query an SQL Server Compact database (.sdf).

I have also tried normal string formatting which works fine but is a security flaw according to many stackoverflow discussions.

My code is as follows:

import adodbapi as ado

#connection provider
provider = "Microsoft.SQLSERVER.CE.OLEDB.4.0"
#database location path
filePath = "results.sdf"
#max size of database file in MB
maxSize = "4000"

#combine into connection string
connectionString = ("Provider = {}; Data Source = {}; SSCe:Max Database Size = {}"
                         .format(provider, filePath, maxSize))

#create connection
dbConnection = ado.connect(connectionString)

#create cursor
cursor = dbConnection.cursor()

cursor.execute("--THE QUERIES DESCRIBED BELOW--")  #**** location of fault****

print(cursor.fetchall().ado_results)

cursor.close()

Passing one parameter works fine, eg:

cursor.execute("SELECT MeasurementId, Decimals FROM Compound WHERE MeasurementId >= ?", (11774,))

And performing a non-parameterized query works fine, eg:

cursor.execute("SELECT MeasurementId, Decimals FROM Compound WHERE (MeasurementId >= 11774 AND MeasurementId <= 11779)")

But when the EXACT same query is attempted with two parameters, it crashes python with no error message generated in the editor, eg:

cursor.execute("SELECT MeasurementId, Decimals FROM Compound WHERE (MeasurementId >= ? AND MeasurementId <= ?)", (11774, 11779))

When running the file from the command prompt (in Windows 7), this generates a popup window claiming "Python has stopped working" with a bunch of info including:

Problem Event Name      BEX64 
Application Name:       python.exe
Fault Module Name:      MSVCR90.dll
Fault Module Version:   9.0.30729.6161
Exception Code:         c0000417
OS Version:             6.1.7601.2.1.0.256.4

And then the proccess quits (when tried on JupyterLabs/VSCode it freezes the kernel)

I have tried passing the parameters as a list, using different paramastyles (format & named although I think adodbapi converts this all back to standard qmark formatting), changing the cursor setting, and different multi-parameter queries, all with the same result.

Is this proper use of parameter passing? I know you are not supposed to pass tables or column names but this seems like a correct query that should execute fine.

0

There are 0 best solutions below