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