Insert string with quotation mark into mssql with python

Question

I try to fill a table on an MS SQL server from the google analytics api. I tried using a method with string replacement as such:

INSERT INTO Reporting.analytics_temp (var1,var2,var3) VALUES ('%s','%s','%s')" %(var1,var2,var3))

This works fine as long as there are no special characters or quotation marks in the variables, but the code breaks when there is one (error message: "Unclosed quotation mark after the character string").

Upon doing some research I realise that the method I use is not so good and in fact dangerous due to sql injection risks so I am quite eager to get a better functioning method.

I find various methods, but none of them seems to work in MSSQL for instance this one that looks like:

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

But this gives the error message "The SQL contains 0 parameter markers, but 3 parameters were supplied"

Is the problem here that I don't understand string replacement well enough or is the code slightly different for MS SQL?

perhaps this will help: https://stackoverflow.com/questions/21911112/escaping-strings-containing-single-quotes-in-powershell-ready-for-sql-query — Tanner, Oct 24 '17 at 13:01
I am running the script in python, so I am not sure how to use powershell in this context — edgar piet, Oct 24 '17 at 13:52
the link was to indicate that you should use a parameterised query. — Tanner, Oct 24 '17 at 14:02
Okay, thanks, I don't really understand what parameterised means — edgar piet, Oct 30 '17 at 13:16

Insert string with quotation mark into mssql with python

0 Answers0