4
\$\begingroup\$

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

\$\endgroup\$
6
  • \$\begingroup\$ 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. \$\endgroup\$
    – J_H
    Jan 31 at 18:49
  • 2
    \$\begingroup\$ @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. \$\endgroup\$
    – Mast
    Jan 31 at 19:07
  • \$\begingroup\$ 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? \$\endgroup\$
    – Mast
    Jan 31 at 19:09
  • 2
    \$\begingroup\$ @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. \$\endgroup\$ Jan 31 at 19:10
  • 1
    \$\begingroup\$ 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). \$\endgroup\$
    – Reinderien
    Feb 1 at 6:14

1 Answer 1

5
\$\begingroup\$

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

\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$ Feb 1 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.