Inserting formatted string with single quotes with Pypyodbc
<p>I'm trying to insert a network path as a string value using Pypyodbc:</p>

<pre><code>    def insert_new_result(self, low, medium, high, reportpath):
    reportpath = "'" + reportpath + "'"
    self.insertsql = (
        """INSERT INTO [dbo].[Results] ([low],[medium], [high], [date]) VALUES
        ({low},{medium},{high}, GETDATE(),{reportpath})"""
        .format(low=low, medium=medium, high=high, reportpath=reportpath))
    self.conection.cursor().execute(self.insertsql).commit()
</code></pre>

<p>This is evaluating to</p>

<pre><code>'INSERT INTO [dbo].[Results] ([low],[medium], [high], [date]) VALUES
            (0,2,2, GETDATE(),\\'\\\\share\\dev-temp\\report_10b29ef6-7436-11e6-ab96-534e57000000.html\\')'
</code></pre>

<p>Notice the extra single quote at the start of the share path, causing an invalid sql error. I have tried a few things like .format(), building the string and escaping however it keeps including the single quote <em>after</em> the first \\. </p>

<p>How can I get self.insertsql to evaluate to </p>

<pre><code>'INSERT INTO [dbo].[Results] 
     ([low],[medium], [high], [date]) 
 VALUES 
     (0,2,2, GETDATE(),'\\\\share\dev-temp\report_10b29ef6-7436-11e6-ab96-534e57000000.html\')'
</code></pre>

<p>If you are using <code>string.format()</code> then you are creating <em>dynamic SQL</em> which leaves you open to all the indignities of <em>SQL injection</em>, like messing with string delimiting and escaping. You should simply use a <em>parameterized query</em>, something like this:</p>

<pre class="lang-python prettyprint-override"><code>self.insertsql = """\
INSERT INTO [dbo].[Results] ([low],[medium], [high], [date], [reportpath]) 
    VALUES (?, ?, ?, GETDATE(), ?)
"""
self.params = (low, medium, high, reportpath, )
self.conection.cursor().execute(self.insertsql, self.params).commit()
</code></pre>

<p><strong>edit re: comment</strong></p>

<p>As a simplified, self-contained test, the code</p>

<pre class="lang-python prettyprint-override"><code>conn = pypyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\    
CREATE TABLE #Results 
    (
        id INT IDENTITY PRIMARY KEY, 
        reportpath NVARCHAR(255)
    )
"""
crsr.execute(sql)

# test data
reportpath = r"\\share\dev-temp\report_10b29ef6-7436-11e6-ab96-534e57000000.html"

sql = "INSERT INTO #Results (reportpath) VALUES (?)"
params = (reportpath, )
crsr.execute(sql, params)

crsr.execute("SELECT * FROM #Results")
row = crsr.fetchone()
print(row)
</code></pre>

<p>displays this</p>

<pre class="lang-none prettyprint-override"><code>(1, u'\\\\share\\dev-temp\\report_10b29ef6-7436-11e6-ab96-534e57000000.html')
</code></pre>

python sql-server pypyodbc