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: | |
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
[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 ...)