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