Bug #16064 Parameterized INSERT with big data causes Data Corruption and Syntax Error
Submitted: 29 Dec 2005 9:10 Modified: 27 Jul 2007 11:17
Reporter: Ken Resander Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12 OS:Windows (Windows 2000 Server)
Assigned to: CPU Architecture:Any

[29 Dec 2005 9:10] Ken Resander
Description:
Windows 2000 Server, ODBC 3.51.12, MySQL 5.0.17. compiler VC6.0

I have a program that migrates DDLs and data.
It works for SQL Server to Mimer SQL, but not for
SQL Server to MySQL.

The problems occurs for big data, blobs, clobs and nclobs.

Table declaration:

CREATE TABLE dbo.categories
   (categoryid INT NOT NULL,
   categoryname NVARCHAR(15) NOT NULL,
   description NVARCHAR(2000),
   picture BLOB,
   CONSTRAINT pk_categories PRIMARY KEY(categoryid));

Actually, the type for 'description' should be NCLOB, but
that is not supported by MySQL, so I used NVARCHAR(2000)
instead. However, it is still treated as 'big data' by
the program.

The program part that moves table content works like this:

  for each record in sql server table categories
      n=0
      for each field
        if it is big
          peek read by sqldata to get fieldlentgh;
          bigdata [n] = getspace(fieldlength);
          read by sqldata into buffer bigdata[n]
          if a blob
             sqltrgtp = SQL_BINARY
          else if a clob
             sqltrgtp = SQL_LONGVARCHAR
          else if an nclob
            sqltrgtp = SQL_WLONGVARCHAR
          bindparam ( &sh , n+1 , ctrgtp , sqltrgtp ,
                      lenindic2 , bigdata[n] ) ;
          n++
        else
          read by sqldata into buffer
        endif
     endforeachfld
     build sql cmdbuf with content 
       INSERT INTO categories values(numval, charval, ? , ?);
     issue sqlexecdirect (hstmt, cmdbuf)
     release bigdata[0..n-1]
 endforeachrec                   

DATA CORRUPTION

Before issuing sqlexecdirect to do the INSERT, bigdata[0] buffer
contains the description field obtained from SQL Server. 
For the first record this is

04B11770  53 00 6F 00 66 00 74 00 20 00 64 00 72 00 69 00  S.o.f.t. .d.r.i.
04B11780  6E 00 6B 00 73 00 2C 00 20 00 63 00 6F 00 66 00  n.k.s.,. .c.o.f.
04B11790  66 00 65 00 65 00 73 00 2C 00 20 00 74 00 65 00  f.e.e.s.,. .t.e.
04B117A0  61 00 73 00 2C 00 20 00 62 00 65 00 65 00 72 00  a.s.,. .b.e.e.r.
04B117B0  73 00 2C 00 20 00 61 00 6E 00 64 00 20 00 61 00  s.,. .a.n.d. .a.
04B117C0  6C 00 65 00 00 00 31 32 33 CD FD FD FD FD 00 00  l.e...123Íýýýý..

The length of the this data is 86 bytes, but for debugging I added 
a few extra bytes into which I wrote chars 1,2 and 3 
(see last line above)

Immediately after the issue-sqlexecdirect the bigdata[0] buffer
contains:

04B11770  53 00 6F 00 66 00 74 00 20 00 64 00 72 00 69 00  S.o.f.t. .d.r.i.
04B11780  6E 00 6B 00 73 00 2C 00 20 00 63 00 6F 00 66 00  n.k.s.,. .c.o.f.
04B11790  66 00 65 00 65 00 73 00 2C 00 20 00 74 00 65 00  f.e.e.s.,. .t.e.
04B117A0  61 00 73 00 2C 00 20 00 62 00 65 00 65 00 72 00  a.s.,. .b.e.e.r.
04B117B0  73 00 2C 00 20 00 61 00 6E 00 64 00 20 00 61 00  s.,. .a.n.d. .a.
04B117C0  6C 00 65 00 00 00 3F 00 3F 00 3F 00 3F 00 00 00  l.e...?.?.?.?...
04B117D0  00 00 00 00 00 00 00 00 3F 00 00 00 B1 00 00 00  ........?...±...
04B117E0  3F 00 3F 00 3F 00 3F 00 00 00 00 00 00 00 00 00  ?.?.?.?.........
04B117F0  3F 00 00 00 01 00 00 00 2F 00 00 00 3F 00 3F 00  ?......./...?.?.
04B11800  3F 00 3F 00 3F 00 3F 00 3F 00 3F 00 3F 00 3F 00  ?.?.?.?.?.?.?.?.
04B11810  3F 00 3F 00 3F 00 3F 00 3F 00 3F 00 3F 00 3F 00  ?.?.?.?.?.?.?.?.

The 123 that I added at the end of the buffer have been
overwritten by question marks. That should not happen. 
Also there are hundreds of question marks in data space after
allocated bigdata[0] (see above). If that space is allocated and 
released by the ODBC all might be well, if not, this is an error.

The VC debugger reports data corruption to block 04B11770, which 
it detecs when bigdata[0] is about to be released.

INSERT SYNTAX ERROR

The sqlexecdirect call fails with error text:

1: State=23000,Native error=1064
,msg=..'[MySQL][ODBC 3.51 Driver][mysqld-5.0.17-
You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the 
right syntax to use near 'drinks, coffees, teas,beers, and ale'
line 1'.....

It looks as if the INSERT is being parsed after the parameters
have been substituted, but that would not work for big
binary data.

HOW TO REPRODUCE IT

The problem is 100% reproduceable on W2000 Server. I think it would 
occur on Windows XP too, but I cannot test this because the SQL 
Server would not run on XP.

However, it should be quite easy to reproduce. There is no need to
get data from the sqlserver and there is no need to have a record 
loop. Just insert one record:

  INSERT INTO table values (1, 'Beverages', ? , ? )

binding values 

  widechar * descr = L"Softdrinks, Coffees, Teas, Beers and Ale" ;
  char dummybindata [ 10 ] = {0,1,2,3,4,5,6,7,8,9}  

to the parameters.          

Best regards
Ken Resander
resander@speed.info.com.ph

How to repeat:
See text above

Suggested fix:
See text above
[2 Jan 2006 11:13] Vasily Kishkin
Sorry...I was not able to reproduce the bug on my C test case. Could you please create and attach simple test case ?
[8 Jan 2006 4:15] Ken Resander
I did not have time to write a stand-alone program, so
I inserted the procedure test (see below) immediately after having
connected to local MySQL server in the original program 
running on w2k server. I also tried in the same way
on Windows XP.

For both OS, it is crashing inside ODBC with unhandled exception 
(KERNEL32.DLL) at 0xc0000005 Access violation.

Here are the bits needed to reproduce the problem
by a stand-alone program:.

Manually issue:

create table testbugtb ( n int , tp nvarchar(15) , ds nvarchar(2000));

typedef struct
   {
   long  henv ;   // SQLHENV don't want to expose these to appl progs
   long  hdbc ;   // SQLHDBC
   long  hstmt ;  // SQLHSTMT   
   short rc ;     // SQLRETURN 
   // more has been snipped
   } SRVHND ;

static SRVHND sh ;

static void test ( )
   {
   wchar_t * descr = L"Softdrinks, Coffees, Teas, Beers and Ale" ;
   int len = wcslen ( descr ) ; // in double-byte chars

   bindparam ( &sh , 1 , SQL_C_WCHAR , SQL_WLONGVARCHAR ,
               len*2 , (char *)descr ) ;
   
   char * cmd = "INSERT INTO testbugtb values(1, N'Beverages' , ?)" ;
   if ( !sqlselect ( &sh , cmd ) )
      {
      dspmessage ( "sql-select command failed" ) ;
      }
   }

static void connectandtest ( )
   {
   if ( connectSrv ( dsn , userid , passwd , &sh ) )
      {
      test ( ) ;
      disconnectSrv ( &sh ) ;
      }
   }

with connectSrv, bindparan and sqlselect (all shortened) as follows:

bool connectSrv ( char * datasourcename , char * userid , char * password ,
                  SRVHND * shret )
   {
   // Allocate environment handle

   SQLHENV henv;
   SQLRETURN rc = SQLAllocHandle ( SQL_HANDLE_ENV , SQL_NULL_HANDLE , 
                                    &henv ) ;
   if ( !success ( rc ) ) 
      {
      (*shret).rc = rc ;
      return false ;
      }

   rc = SQLSetEnvAttr ( henv, SQL_ATTR_ODBC_VERSION , 
                        (void*)SQL_OV_ODBC3 , 0 ) ; 
   if ( !success ( rc ) ) 
      {
      sqlerrinfo ( SQL_HANDLE_ENV , NULL , (int)henv , "Set ODBC version error" ) ;
      (*shret).rc = rc ;
      SQLFreeEnv ( henv ) ;
      return false ;
      } ;

   // Allocate connection handle

   SQLHDBC hdbc;
   rc = SQLAllocHandle ( SQL_HANDLE_DBC , henv , &hdbc ) ; 
   if ( !success ( rc ) )
      {
      sqlerrinfo ( SQL_HANDLE_ENV , NULL , (int)hdbc , "Alloc connection handle error" ) ;
      (*shret).rc = rc ;
      SQLFreeEnv ( henv ) ;
      return false ;
      } ;

   // Connect to data source
            
   rc = SQLConnect ( hdbc , (SQLCHAR*)datasourcename , SQL_NTS,
                      (SQLCHAR*) userid , SQL_NTS,
                      (SQLCHAR*) password , SQL_NTS);
   if ( !success ( rc ) )
      {
      sqlerrinfo ( SQL_HANDLE_DBC , NULL , (int)hdbc , "Connect error" ) ;
      (*shret).rc = rc ;
      SQLFreeConnect ( hdbc ) ;
      SQLFreeEnv ( henv ) ;
      return false ;
      } ;

    SQLUSMALLINT ok ; // +++ for debugging - take out later
    rc = SQLGetFunctions(hdbc, SQL_API_SQLSETSTMTATTR , &ok);
    if ( !success ( rc ) )
       {
       sqlerrinfo ( SQL_HANDLE_DBC , NULL , (int)hdbc , "GetFuncs" ) ;
       } ;
    rc = SQLGetFunctions(hdbc, SQL_API_SQLFETCHSCROLL , &ok);
    if ( !success ( rc ) )
       {
       sqlerrinfo ( SQL_HANDLE_DBC , NULL , (int)hdbc , "GetFuncs" ) ;
       } ;

    SQLUINTEGER val ;
    SQLSMALLINT ln ;
    rc = SQLGetInfo ( hdbc , SQL_GETDATA_EXTENSIONS , &val , 8 , &ln ) ;
    if ( !success ( rc ) )
       {
       sqlerrinfo ( SQL_HANDLE_DBC , NULL , (int)hdbc , "GetInfo" ) ;
       } ;
    if ( val & SQL_GD_BLOCK )
       {
       val = val ;
       } ;

   // Allocate statement handle

   SQLHSTMT hstmt;
   rc = SQLAllocHandle ( SQL_HANDLE_STMT , hdbc , &hstmt); 
   if ( !success ( rc ) )
      {
      sqlerrinfo ( SQL_HANDLE_DBC , NULL , (int)hdbc , "Alloc Stmt error" ) ;
      (*shret).rc = rc ;
      SQLDisconnect ( hdbc ) ;
      SQLFreeConnect ( hdbc ) ;
      SQLFreeEnv ( henv ) ;
      return false ;
      } ;
   memset ( shret , 0 , sizeof ( SRVHND ) ) ;
   (*shret).henv = (long)henv ;
   (*shret).hdbc = (long)hdbc ;
   (*shret).hstmt = (long)hstmt ;
   return true ;
   }

static long lenindic ;
bool bindparam ( SRVHND * sh , int parix , int ctrgtp , int sqltrgtp , 
                 int lensrc , char * src )
   {
   SQLHSTMT hstmt = (SQLHSTMT)(*sh).hstmt ;
   lenindic = lensrc ;
   int rc = SQLBindParameter ( hstmt , parix , SQL_PARAM_INPUT , ctrgtp ,
                               sqltrgtp , lensrc , 0 , src , 0 , &lenindic ) ;
   if ( !success ( rc ) )
      {
      stmterror ( sh ) ;
      return false ;
      } ;
   return true ;
   }

bool sqlselect ( SRVHND * sh , char * cmdstr )
   {
   SQLHSTMT hstmt = (SQLHSTMT)(*sh).hstmt ;
   SQLRETURN rc ;
   rc = SQLExecDirect ( hstmt , (unsigned char *)cmdstr , SQL_NTS ) ;
   if (!success ( rc ) )
      {
      stmterror( sh , "ExecDirect sqlselect" );
      return false ;  
      }
   return true ;
   }

I was a bit uncertain about the length parameter of bindparam,
so I tried passing 'len' as well as 'len*2', but both  gave
the same result.

Best regards
Ken Resander
[9 Jan 2006 10:58] Vasily Kishkin
Sorry...I was unable to reproduce exactly your bug. But I found another error:

You have an error in your SQL syntax; check the manual that corresponds to y
our MySQL server version for the right syntax to use near ')' at line 1

My test case is attached.
[9 Jan 2006 10:58] Vasily Kishkin
Test case

Attachment: test.c (text/plain), 4.56 KiB.

[15 Jul 2007 13:11] Tonci Grgin
Ken, can you please confirm that your problem still exists in latest 3.51 version (3.51.17)? We can not reproduce it anymore.
[27 Jul 2007 11:17] Tonci Grgin
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Test is passing with new MyODBC 3.51.17GA

070727 13:00:06	      4 Connect     root@localhost on test
		      4 Query       SET SQL_AUTO_IS_NULL=0
		      4 Query       USE TEST
		      4 Query       DROP TABLE IF EXISTS testbugtb
		      4 Query       create table testbugtb ( n int , tp nvarchar(15) , ds nvarchar(2000))
070727 13:00:08	      4 Query       INSERT INTO testbugtb values(1, N'Beverages' , 'Softdrinks, Coffees, Teas, Beers and Ale')
070727 13:00:15	      4 Quit       
070727 13:01:25	      5 Connect     root@localhost on test
		      5 Query       select @@version_comment limit 1
070727 13:01:33	      5 Query       select * from testbugtb
070727 13:01:46	      5 Quit       

C:\mysql507\bin>mysql -uroot -p test
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.44-max-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from testbugtb;
+------+-----------+------------------------------------------+
| n    | tp        | ds                                       |
+------+-----------+------------------------------------------+
|    1 | Beverages | Softdrinks, Coffees, Teas, Beers and Ale |
+------+-----------+------------------------------------------+
1 row in set (0.00 sec)

There is, however, one small error in test case:
SQLINTEGER len = wcslen ( descr ); // in double-byte chars 
should be 
SQLINTEGER len = wcslen ( descr ) * 2; // in double-byte chars (besides the fact that this is not the best way as you may have to handle \0 ...)