Python SQL insert to MSAccess with VBScript

Question

I have a business use case that required running SQL insert queries to a local MSAccess database with a Python script. The actual script does many other things and the SQL function is just one of them, but it is a critical one. However, due to limitations outside my control, all I had to work with was Python 3.6 64 bit and MSAccess with 32 bit drivers. I acknowledge that MSAccess is not the database software of choice, but it is what I had to use.

Due to the bitness mismatch between python and MSAccess, the python script could not access the driver to interface with the database directly, so I wrote the function to call a subprocess that passes the SQL insert values as an argument to a VBScript which conducts the SQL insert query.

Prior to learning how to pass the string as an argument to the VBScript, I initially had the python script write the string to a text file, then call the VBScript which would read the string from the text file and conduct the insert query. Passing it as an argument to VBScript directly worked much better.

The following code does what is intended. However, I would like to know of any alternate, cleaner, or more efficient ways to implement this code. I was also limited to what additional third party libraries I could install, although I don't know if any libraries that handle SQL would overcome the bitness mismatch issue.

Python Script

def insertSQL(insertstring):
 ret = subprocess.call(['C:\Windows\SysWOW64\cscript.exe', 'filepath\\insert.vbs', insertstring], shell=True)
 if ret != 0:
 print("<error message>")
 return
if __name__ == "__main__":
 insertSQL("('value1', 'value2', 'value3')")

VB Script

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dir = objFSO.GetParentFolderName(WScript.ScriptFullName)
Dim Arg, var1
Set Arg = WScript.Arguments
var1 = Arg(0)
Set Arg = Nothing
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=" & Dir & "\filepath\database.accdb"
On Error Resume Next
Err.Clear
Set ors = objConn.Execute("INSERT INTO Table1 ([Field1], [Field2], [Field3]) VALUES " & var1 & ";")
If Err.Number <> 0 Then
 objConn.Close
 Set objFSO = Nothing
 Set Dir = Nothing
 Set ors = Nothing
 Set objConn = Nothing
 WScript.Quit(1)
End If
objConn.Close
Set objFSO = Nothing
Set Dir = Nothing
Set ors = Nothing
Set objConn = Nothing

Database after insert

Database Table

• J_H: I reject the premise; I’m unwilling to believe that a business use case would really be best served by accepting that set of constraints. 3.6 was EOL 2018, last security patch 2021. // All python versions come with SQLite "batteries included". Solve your DB problem that way. Then worry about CSV table exports or some such to move processed rows to the DB that needs them.
• Mast: @J_H I've had to work with Python 2.x long after it was considered dead. Sometimes there are software limitations you have to work around with.
• Mast: Your code seem anonymized. While I understand you can't tell us everything due to how business like to keep things to themselves, are variable names like Arg, var1 really what you use or did those get anonymized as well?
• spaghetticode: @Mast, Arg, var1, Dir, and insertstring are the actual variable names. I just anonymized the filepath and filenames, tablename, field names, and values. The actual insert string contains more values.
• Reinderien: Can you not accomplish the same thing in your inner script with PowerShell? It has an x86 binary, and will preserve more of your dignity. Or better yet - can you not just run a purpose-installed 32-bit Python and dispense with the inner script? Python installations are cheap(ish).

Answer 1

I'm stuck in the same situation (managing a database with MS Access frontend), so I can't comment on the technologies used here.

However, for the VB Script itself, I highly recommend parameterizing the query. As written above, your query is vulnerable to SQL injection. This is especially problematic if the inserted values are coming from an user input.

Here is an example of how to parameterize an ADODB query, taken from this link.

' Open Connection Conn 
set ccmd = CreateObject("ADODB.Command") 
ccmd.Activeconnection= Conn 
ccmd.CommandText="SPWithParam" 
ccmd.commandType = 4 'adCmdStoredProc 
ccmd.parameters.Append ccmd.CreateParameter(, adInteger, adParamReturnValue, , NULL) ' return value 
ccmd.parameters.Append ccmd.CreateParameter("InParam", adVarChar, adParamInput, 20, "hello world") ' input parameter 
ccmd.parameters.Append ccmd.CreateParameter("OutParam", adVarChar, adParamOutput, 20, NULL) ' output parameter 
ccmd.execute() 
' Access ccmd.parameters(0) as return value of this stored procedure 
' Access ccmd.parameters("OutParam") as the output parameter of this stored procedure.

For your case note that ccmd.CommandText will take the form "INSERT INTO Table1 ([Field1], [Field2], [Field3]) VALUES (?,?,?);". Also be sure to append parameters in order of appearance in the SQL query (Field1 first).

• spaghetticode: Thank you this is helpful and I will work on implementing this. While at the current scale and environment, SQL injection is an extremely low risk. It would actually be easier to just delete the database. But on general principle I agree this is better, especially if its scaled up. The python program, running a tkinter GUI, is used by a handful of employees entering data that writes records to the locally hosted shared database, among other things. Prior to the python program, it was an excel macro and prior to that it was hand entry.