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

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