| Bug #43751 | failure to write NULL byte to blob column via ODBC 3.1 | ||
|---|---|---|---|
| Submitted: | 19 Mar 2009 15:58 | Modified: | 26 Mar 2009 11:47 |
| Reporter: | Ilya Kaplan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.1 | OS: | Windows (XP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ODBC MySQL blob NULL | ||
[21 Mar 2009 16:52]
Tonci Grgin
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Explanation: Hi Ilya and thanks for your report. I've just built c/ODBC from bzr sources and cant repeat your problem.
Test case:
CHAR buf[15];
SQLINTEGER len;
strcpy(buf, "hello world");
len= strlen(buf);
buf[5]= 0;
SQLRETURN rc;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
std::cout << "Connecting to " << "3-51-27-on-opensol" << std::endl;
rc = SQLConnect(hdbc, (SQLCHAR *)"3-51-27-on-opensol", SQL_NTS,
(SQLCHAR *)"***", SQL_NTS,
(SQLCHAR *)"***", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc=SQLExecDirect(hstmt, (SQLCHAR *)"drop table if exists bug43751", SQL_NTS);
rc=SQLExecDirect(hstmt, (SQLCHAR *)"create table bug43751 (id integer, obj blob)", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = (hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARCHAR, 0, 0, buf, 0, &len));
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = SQLExecDirect(hstmt, (SQLCHAR *)"insert into bug43751(id, obj) values(1, ?)", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
std::cout << "Success:" << "test done" << std::endl;
return 0;
Output:
Connecting to 3-51-27-on-opensol
Success:test done
[22 Mar 2009 6:03]
Ilya Kaplan
Changed your test code a bit, so it will compile on pure C. Run it, had the same error as in past. What could possibly be wrong?
int main(){
CHAR buf[15];
SQLINTEGER len;
SQLRETURN rc;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
strcpy(buf, "hello world");
len= strlen(buf);
buf[5]= 0;
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
printf("Connecting to test_mysql");
rc = SQLConnect(hdbc, (SQLCHAR*)"test_mysql", SQL_NTS , NULL, 0, NULL, 0);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc=SQLExecDirect(hstmt, (SQLCHAR *)"drop table if exists bug43751", SQL_NTS);
rc=SQLExecDirect(hstmt, (SQLCHAR *)"create table bug43751 (id integer, obj blob)",
SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = (hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARCHAR, 0,
0, buf, 0, &len));
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
rc = SQLExecDirect(hstmt, (SQLCHAR *)"insert into bug43751(id, obj) values(1, ?)",
SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(-1);
printf("Success: test done\n");
system("cmd /c pause");
return 0;
}
[22 Mar 2009 6:21]
Ilya Kaplan
I thought now, maybe there is some option that I should set in MySQL options file, or in MyODBC options, that changes the way MySQL handles NULL characters? Please tell me, if there is anything that is connected to the issue.
[23 Mar 2009 15:00]
Tonci Grgin
Ilya, did you tried building c/ODBC sources from https://launchpad.net/myodbc? Or, at least, take snapshot and test with it. We've tested both 3.51 and 5.1 branch with your test case and found no problem.
[26 Mar 2009 11:47]
Ilya Kaplan
Thank you Tonci Grgin, your testing of the issue made me recheck every option I could possibly change and I found the source of the problem. As I thought the problem was created by options that were used. I used "NO_BACKSLASH_ESCAPES" in my.cnf configuration file. When I disabled it, problem with NULL character disappeared. I guess, the driver escapes NULL character with backslash when it sends data to the server, and when I disabled the backslash escapes, the process went wrong... However, the problem still remains, my program saves a lot of file pathes to database, and need to put backslashes as are, and I can't escape it, since other data sources like Access or SQLite don't work with backslash escapes. Please tell if there is any efficient workaround for the issue. Thank you again, Ilya.
[26 Mar 2009 15:16]
Tonci Grgin
Ilya, good catch, I hope some will profit from it! Now, I'd try with "/", I think they'll work. Other than that, I have no idea how to bypass this but will consult with guys.
Description: Failure on writing data that contains NULL-bytes to binary typed column (blob, long varbinary, etc.) via Microsoft ODBC 3.1 with MyODBC driver. Usual algorithm is defining binary parameter with SQLBindParameter(...) and then executing the query that contains "?" in place of binary value with SQLExecDirect(...). It works fine with SQLite and MS.Access data sources, but when I run it with MySQL I get a syntax error on index where NULL-byte is inserted. Possible workaround: create encode/decode functions before writing and after reading but: 1. It would be not effective, especially when large amount of data is to be written. 2. It would take more place in DB. 3. It will make program not compatible with existing SQLite and MS.Access databases. How to repeat: create empty database allocate enviroment, make connection define the following variables: sql: SQLHDBC, the connection stmt: SQLHSTMT resultOK(rc): checks if <rc> is successfull or successfull_with_info buf: char array then run the following code: SQLAllocHandle(SQL_HANDLE_STMT, sql, &stmt); if (stmt){ rc=SQLExecDirect(stmt, "create table data (id integer, obj blob)", SQL_NTS); if (resultOK(rc)){ strcpy(buf,"hello world"); len=strlen(buf); buf[5]=0; //to test the NULL-byte writing rc=SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARCHAR, 0, 0, (SQLPOINTER) buf, 0, (SQLINTEGER*)&len); if (resultOK(rc)) rc=SQLExecDirect(stmt, "insert into data(id, obj) values(1, ?)", SQL_NTS); /* At this point rc is -1 (ERROR) and message is: 0:[MySQL][ODBC 3.51 Driver][mysqld-5.1.30-community]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 ''hello' at line 1 */ } SQLFreeHandle(SQL_HANDLE_STMT, stmt); } Suggested fix: Putting bytes of data AS IS, just as it is documented in ODBC API in writing binary data topic.