Crash on "cursor.execute" only when last column on a TVP argument is null

pedeeme commented 2 years ago

Environment

Python: 3.8.10
pyodbc: 4.0.32 and 4.0.33b6
OS: Ubuntu 20.04 LTS on WSL2 of Windows 10 Enterprise
DB: Microsoft SQL Server 2016 SP2
driver: Microsoft ODBC Driver 17.8.1.1

Also had issue on an alpine docker container with python 3.10 and pyodbc 4.0.32

Issue

Python crashes with segmentation fault when executing a stored procedure with a TVP argument on a non-default table schema, the TVP has 13 arguments and if the last column is not null python does not crashes

Setup database

Run the follow database commands to create the table, table type and stored procedure. It assumes the schema "some_schema" exists:

drop procedure some_schema.some_table_insert_many
go
drop table some_schema.some_table
go
drop type some_schema.typ_some_table
go

IF OBJECT_ID(N'some_schema.some_table', N'U') IS NULL
BEGIN
    CREATE TABLE some_schema.some_table
    (
            the_id INT IDENTITY(1, 1) NOT NULL CONSTRAINT some_table_PK PRIMARY KEY NONCLUSTERED,
            field_1 BIGINT NOT NULL ,
            field_2 INT NOT NULL,           
            field_3 BIGINT NULL,
            field_4 TINYINT NULL ,          
            field_5 BIGINT NULL,            
            field_6 INT NOT NULL,           
            field_7 SMALLINT NOT NULL,      
            field_8 DATETIME2(2) NOT NULL,      
            field_9 TINYINT NOT NULL,       
            field_10 INT NULL,          
            field_11 NVARCHAR(512) NULL,            
            field_12 SMALLINT NULL,             
            field_13 TINYINT NULL
    )
END;
GO

IF TYPE_ID('some_schema.typ_some_table') IS NULL
BEGIN
    CREATE TYPE some_schema.typ_some_table AS TABLE     
    (
        the_id INT NOT NULL, 
        field_1 BIGINT NOT NULL ,
        field_2 INT NOT NULL,           
        field_3 BIGINT NULL,
        field_4 TINYINT NULL ,          
        field_5 BIGINT NULL,            
        field_6 INT NOT NULL,           
        field_7 SMALLINT NOT NULL,      
        field_8 DATETIME2(2) NOT NULL,      
        field_9 TINYINT NOT NULL,       
        field_10 INT NULL,          
        field_11 NVARCHAR(512) NULL,            
        field_12 SMALLINT NULL,             
        field_13 TINYINT NULL

        INDEX typ_some_table_HX_The_Id NONCLUSTERED (the_id) 
    )
END;
GO

CREATE OR ALTER PROCEDURE some_schema.some_table_insert_many
(
    @arg1 some_schema.typ_some_table READONLY
) 
AS
BEGIN 
    SET NOCOUNT ON

    INSERT INTO some_schema.some_table (field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10, field_11, field_12, field_13)
                select field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10, field_11, field_12, field_13
                FROM @arg1
                where the_id > 0 

    --indicate everything is ok
    select 0, ''
    RETURN 0;
END;
GO

Code to reproduce problem

Run the following python script. In order to reproduce problem change the variable "do_you_want_to_crash" to True. In order to see a execution without crahes set variable to False. Also set database connection variables on line 11:

import platform, os, sys  

import pyodbc

class Database :

  def __init__(self):

      self.db_cnx = pyodbc.connect( driver = "ODBC Driver 17 for SQL Server", 
                              server = "localhost", 
                              database = "some_db", 
                              uid = "user",  
                              pwd = "pw")

      self.db_cnx.autocommit = True

  def exec(self, sql):
      self.db_cnx.execute(sql)      

  def execute_func_and_catch_error(self, i):

        sql =  ( "BEGIN\n"
                 "{ call [some_schema].[some_table_insert_many] (?) };\n"
                 "END\n" )

        try:
            cursor = self.db_cnx.cursor()
            tvp_arg = get_data(i)
            print(f"Exec data-set:{i} with {len(tvp_arg[0])-3} lines")
            cursor.execute( sql, tvp_arg)         

            res_per_sp = []
            while True:       
                res_per_sp.append( cursor.fetchone() )
                if not cursor.nextset():
                    break    

            cursor.close()
            return res_per_sp

        except Exception as err:
            print(f"Exception:{err}")
            print(f"sql =>\n {sql}")
            return []

        return []

def get_data(line):

    print(f"Getting line {line}")

    data = [
            'typ_some_table',
            'some_schema',
            [0, 1, 2, 3, 4, 5, 6, 7, '2008-08-08T08:08:08Z', 9, 10, '11',   12, 13 ]
           ]

    do_you_want_to_crash = True
    total_lines = 1

    start = line+1
    for cnt in range (1,total_lines+1):
       if do_you_want_to_crash:
          data.append( [cnt, (start*1000)+cnt, 1, (start*1100)+cnt, None, None, 0, 0, f'2021-{start}-{start}T14:50:11Z',
                         0, None, None, None, None ])
       else:
          data.append( [cnt, (start*1000)+cnt, 1, (start*1100)+cnt, None, None, 0, 0, f'2021-{start}-{start}T14:50:11Z',
                         0, None, None, None, 1 ])

    return [ data  ]

if __name__ == '__main__':

  print("Testing pyodbc on calling procedures with TVP arguments")

  print( f"os:      {platform.system()}\n"
         f"Current directory: {os.getcwd()}\n"
         f"sys path: {sys.path}\n" )

  print(f"python: {sys.version}")  
  print(f"pyodbc:  {pyodbc.version} {os.path.abspath(pyodbc.__file__)}" )

  db = Database()

  db.exec("delete from some_schema.some_table")

  print("----START ----\n")
  for i in  range (0,10):
    res = db.execute_func_and_catch_error(i)
    if res:
        print(f"res: {res}")
    print("---------------")

  print("Everything went A-OK and nothing crashed!")

Crash information

I tried debugging the problem and the memory corruption shifts whether I use debug messages or gdb. With gdb the crash is on a SQLAllocHandle called in Cursor_New

#0  malloc_consolidate (av=av@entry=0x7ffff7fb8b80 <main_arena>) at malloc.c:4500
#1  0x00007ffff7e67e03 in _int_malloc (av=av@entry=0x7ffff7fb8b80 <main_arena>, bytes=bytes@entry=1672) at malloc.c:3699
#2  0x00007ffff7e6bd15 in __libc_calloc (n=<optimized out>, elem_size=<optimized out>) at malloc.c:3428
#3  0x00007ffff741a29c in __alloc_stmt () at __handles.c:784
#4  0x00007ffff73e4545 in __SQLAllocHandle (handle_type=handle_type@entry=3, input_handle=0xa6b590,
        output_handle=output_handle@entry=0x7ffff71b4648, requested_version=requested_version@entry=0) at SQLAllocHandle.c:580
#5  0x00007ffff73e53ea in SQLAllocHandle (handle_type=handle_type@entry=3, input_handle=<optimized out>,
        output_handle=output_handle@entry=0x7ffff71b4648) at SQLAllocHandle.c:1304
#6  0x00007ffff7669f5e in Cursor_New (cnxn=0x7ffff6e711f0) at src/cursor.cpp:2556

But adding debug messges to 4.0.33b6 the crash moves to this line :

ret = SQLPutData(cur->hstmt, hasTvpRows ? (SQLPOINTER)1 : 0, hasTvpRows);

on the execute function.

I am just starting to use pyodbc so I am not familiar with the code so any help would be appreciated.

Best Regards

v-makouz commented 2 years ago

I tried to reproduce it but got a different error, "A TVP's rows must all be the same size.", it seems to think that a row being passed in is 11 columns instead of 14 for some reason.

I'm afraid I won't be able to look deeper into it until the new year, though.

pedeeme commented 2 years ago

Hello,

Tried running the code I actually posted and was able to reproduce again, this was my output with the flag set to not crash:

    Testing pyodbc on calling procedures with TVP arguments
    os:      Linux
    Current directory: /mnt/c/temp/pyodbc_crash
    sys path: ['/mnt/c/temp/pyodbc_crash', '/usr/lib/python38.zip', '/usr/lib/python3.8', '/usr/lib/python3.8/lib-dynload', '/home/pedro/.local/lib/python3.8/site-packages', '/usr/local/lib/python3.8/dist-packages', '/usr/local/lib/python3.8/dist-packages/pyodbc-4.0.33b6-py3.8-linux-x86_64.egg', '/usr/lib/python3/dist-packages']

    python: 3.8.10 (default, Sep 28 2021, 16:10:42)
    [GCC 9.3.0]
    pyodbc:  4.0.32 /home/pedro/.local/lib/python3.8/site-packages/pyodbc.cpython-38-x86_64-linux-gnu.so
    ----START ----

    Getting line 0
    Exec data-set:0 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 1
    Exec data-set:1 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 2
    Exec data-set:2 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 3
    Exec data-set:3 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 4
    Exec data-set:4 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 5
    Exec data-set:5 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 6
    Exec data-set:6 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 7
    Exec data-set:7 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 8
    Exec data-set:8 with 1 lines
    res: [(0, '')]
    ---------------
    Getting line 9
    Exec data-set:9 with 1 lines
    res: [(0, '')]
    ---------------
    Everything went A-OK and nothing crashed!

And on the database the data was inserted on all 14 columns

    the_id      field_1              field_2     field_3              field_4 field_5              field_6     field_7 field_8                     field_9 field_10    field_11                                                                                                                                                                                                                                                         field_12 field_13
    ----------- -------------------- ----------- -------------------- ------- -------------------- ----------- ------- --------------------------- ------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------
    39          1001                 1           1101                 NULL    NULL                 0           0       2021-01-01 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    40          2001                 1           2201                 NULL    NULL                 0           0       2021-02-02 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    41          3001                 1           3301                 NULL    NULL                 0           0       2021-03-03 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    42          4001                 1           4401                 NULL    NULL                 0           0       2021-04-04 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    43          5001                 1           5501                 NULL    NULL                 0           0       2021-05-05 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    44          6001                 1           6601                 NULL    NULL                 0           0       2021-06-06 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    45          7001                 1           7701                 NULL    NULL                 0           0       2021-07-07 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    46          8001                 1           8801                 NULL    NULL                 0           0       2021-08-08 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    47          9001                 1           9901                 NULL    NULL                 0           0       2021-09-09 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1
    48          10001                1           11001                NULL    NULL                 0           0       2021-10-10 14:50:11.00      0       NULL        NULL                                                                                                                                                                                                                                                             NULL     1

    (10 rows affected)

    Completion time: 2021-12-18T00:25:45.4587229+00:00

Settting the flag to crash would also crash python as expected

pedeeme commented 2 years ago

Hello, I forgot to mention something important this only happens on Linux, running this test on Windows ( in a powershell ) has no issues and does not crashes

v-chojas commented 2 years ago

Check unixODBC version, older versions (<2.3.5) buggy may cause this.

pedeeme commented 2 years ago

Hello v-chojas

My unixODBC version is 2.3.7

pedro@PT-PEDMARQU1:/usr/bin$ odbcinst --version
unixODBC 2.3.7

The 2.3.9 was already released but on apt-get 2.3.7 is still listed as the current version.

mkleehammer commented 1 year ago

I'm going to close this for inactivity. If someone could confirm with 2.3.9 it would be useful.

mkleehammer / pyodbc