SQL param in python script in SQL proc-CodePudding

I'm trying to use dynamic SQL passed to Python for execution, but cannot get the correct quote combination working for @key. Here is the proc:

    create procedure [dbo].[pr_select_to_html_table]
    (
        @key        varchar(100),
        @sql_query  nvarchar(max) = null
    )
    AS

    DECLARE @NewScript NVARCHAR(MAX) 
    SET @NewScript  = N'
    import pandas as pd
    import pyodbc

    def exec_sql (query) :
        # execute sql query with no return
        cnxn_p = pyodbc.connect("driver={odbc driver 17 for sql server};server=localhost;DATABASE=trader;trusted_connection=yes;")
        cursor_p = cnxn_p.cursor()
        try:
            cursor_p.execute(query)
        except pyodbc.Error as err:
                print(err)
        cnxn_p.commit()

    #Read Data From Input @input_data_1 
    df = my_input_data
    df.index = df.index 1

    result = df.to_html()
    sql = "insert into wrk.html_table (key,html_result) values ('''   @key   ''' ,''"   result   "'')"
    exec_sql(sql)
        '

        EXEC sp_execute_external_script
          @language = N'Python'
        , @Script = @NewScript
        , @input_data_1 = @sql_query
        , @input_data_1_name = N'my_input_data'

Python provides two errors, the first which I think may not apply is: UserWarning: Pandas doesn't allow columns to be created via a new attribute name - see https://pandas.pydata.org/pandas-docs/stable/indexing.html#attribute-access setattr(result, "var_info", var_info)

The second Python error is: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'key'. (156) (SQLExecDirectW)")

Triple single quotes does not work, nor does single double single.

(Yes I am passing SQL from SQL Server to Python, to then connect to SQL server and execute the SQL. The to_html() method is worth the effort).

CodePudding user response：

You are going to have a major SQL injection issue if you attempt what you are doing now.

The best solution here would be to pass back the HTML as an OUTPUT parameter:

create procedure [dbo].[pr_select_to_html_table]
(
    @key        varchar(100),
    @sql_query  nvarchar(max) = null
)
AS

DECLARE @NewScript NVARCHAR(MAX) 
SET @NewScript  = N'
import pandas as pd

#Read Data From Input @input_data_1 
df = my_input_data
df.index = df.index 1

html = df.to_html()
     '

DECLARE @html nvarchar(max);

EXEC sp_execute_external_script
       @language = N'Python'
     , @Script = @NewScript
     , @input_data_1 = @sql_query
     , @input_data_1_name = N'my_input_data'
     , @params = N'@html nvarchar(max) OUTPUT'
     , @html = @html OUTPUT;

insert into wrk.html_table (key, html_result)
values (@key, @html);

Your other option is to use parameters to do the insertion from the Python side. I feel this is less clean though, as scalar values can be returned through parameters as above, and dataframes can be returned through @output_data_1_name

create procedure [dbo].[pr_select_to_html_table]
(
    @key        varchar(100),
    @sql_query  nvarchar(max) = null
)
AS

DECLARE @NewScript NVARCHAR(MAX) 
SET @NewScript  = N'
import pandas as pd
import pyodbc

#Read Data From Input @input_data_1 
df = my_input_data
df.index = df.index 1

result = df.to_html()
sql = """
insert into wrk.html_table (key, html_result)
values (?, ?)"""

# execute sql query with no return
cnxn_p = pyodbc.connect("driver={odbc driver 17 for sql server};server=localhost;DATABASE=trader;trusted_connection=yes;")
cursor_p = cnxn_p.cursor()
try:
    cursor_p.execute(query, key, result)
except pyodbc.Error as err:
        print(err)
cnxn_p.commit()

exec_sql(sql)
     '

EXEC sp_execute_external_script
       @language = N'Python'
     , @Script = @NewScript
     , @input_data_1 = @sql_query
     , @input_data_1_name = N'my_input_data'
     , @params = N'@key varchar(100)'
     , @key = @key;