Bug #5853 Using Update with 'WHERE CURRENT OF' with binary data crashes (fix included)
Submitted: 1 Oct 2004 19:16 Modified: 26 Mar 2007 6:37
Reporter: Ernesto Corvi Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.12 OS:Unix
Assigned to: Jim Winstead CPU Architecture:Any
Tags: Contribution

[1 Oct 2004 19:16] Ernesto Corvi
When using a query such as:

UPDATE tablename SET 'parameter'=? WHERE CURSOR OF cursorname

and 'parameter' is a BLOB/Binary, myODBC crashes.

The bug:
my_SQLExecute() calls do_my_pos_cursor(), which truncates the original
statement string removing 'WHERE CURSOR OF cursorname', and creates a dynamic string. Then it calls my_pos_update() in cursor.c, which builds a WHERE clause on the previously allocated dynamic string pointing to the row affected. A new temporary statement is created and prepared, and parameters from the original statement are copied to the temporary ones (as a side note, the function copy_input_param() trashes the 'pos_in_query' member of the original statement's parameter during the copy, as it points it to the temporary statement's 'query' member). my_SQLExecute() gets called with the temporary statement, but since 'parameter' is a BLOB/Binary, it returns SQL_NEED_DATA. The temporary statement is deallocated, and the original statement is now corrupt (do_my_pos_cursor() truncated the original string, and copy_input_param() trashed the 'pos_in_query' member of the parameters). When SQLParamData() receives the last parameter and tries to execute the broken statement, myODBC crashes.

How to repeat:
When using a query such as:

UPDATE tablename SET 'parameter'=? WHERE CURSOR OF cursorname

and 'parameter' is a BLOB/Binary, myODBC crashes.

Suggested fix:
The fix:
my_pos_update() needs to be aware that my_SQLExecute() can return SQL_NEED_DATA. In that case, the original statement's query must be normalized and the parameters 'pos_in_query' members must be fixed. As far as the query, we can use the already built dynamic string as the new query on the statement, and my_SQLPrepare() will fix the parameters. Since my_SQLPrepare() is what you use to set the query string also, then a single call to it fixes this problem.

The code:
In file cursor.c, inside function my_pos_update(), search for:
if (sqlRet == SQL_SUCCESS || sqlRet == SQL_SUCCESS_WITH_INFO)
    stmtParam->affected_rows= mysql_affected_rows(&stmtTemp->dbc->mysql);
    sqlRet= update_status(stmtParam,SQL_ROW_UPDATED);

add after that:
  else if ( sqlRet == SQL_NEED_DATA )
  	/* replace the truncated query string in the original statement with the dynamic one, and fix parameters */
  	if (my_SQLPrepare(stmtParam,(SQLCHAR FAR*) dynQuery->str,dynQuery->length) != SQL_SUCCESS)
[13 Dec 2004 15:19] Harun Eren

i have tested this bug report with myodbc3 (v.3.51.10) and i get always the following error message:

[MySQL][ODBC 3.51 Driver][mysqld-4.1.7]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 'OF "SQL_CUR0"' at line 1

My test environment:

new statement handle
create resultset ("SQL_CUR0")
positioned in resultset with SQLExtendedFetch()

new statement handle
prepare update "update test set  b1=? where CURSOR OF "SQL_CUR0""
binding variable with SQLBindParameter()
execute statement
error message.

Best Regards
[13 Dec 2004 17:42] Ernesto Corvi
Oops, I have a typo in there. The right command would be: 'WHERE CURRENT OF'.
Notice CURRENT vs CURSOR. The bug is still present. Let me know if you need more details.

[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[29 Jan 2007 18:24] Ernesto Corvi
This is still broken. The suggested fix still works properly.

Again, the way to trigger it is:

UPDATE tablename SET 'parameter'=? WHERE CURRENT OF cursorname

where the parameter is a long binary. Executing that will return SQL_NEEDS_DATA.
After the last call to SQLParamData() to provide the parameter(s), the program crashes.

See original bug report for explanation and fix.

[29 Jan 2007 18:25] Ernesto Corvi
Updated the Synopsis field to reflect the actual problem (CURRENT OF vs CURSOR OF)

[13 Mar 2007 0:04] Jim Winstead
The proposed fix isn't enough -- if we then move the cursor to another row and execute again, we need to reset the prepared statement back to its original form. I'm working on a complete fix.
[14 Mar 2007 3:27] Jim Winstead
A work-in-progress patch

Attachment: bug5853-wip.diff (application/octet-stream, text), 9.33 KiB.

[14 Mar 2007 3:33] Jim Winstead
The patch that I just attached is a work-in-progress. The basic idea is that when we are dealing with 'WHERE CURRENT OF', we save a copy of the original query before munging it, whether by chopping off the WHERE CURRENT OF or preparing a rewritten query with my_SQLPrepare. Then after we execute a query, we restore the original form of the query to the statement.

This needs more testing, but I suspect that re-execution of any statements that involved WHERE CURRENT OF has always been broken. This patch should fix that, too.
[14 Mar 2007 18:11] Ernesto Corvi
Fix looks good, and yes, this has been broken forever.
[14 Mar 2007 23:11] Jim Winstead
Complete patch, including tests

Attachment: bug5853.diff (application/octet-stream, text), 17.87 KiB.

[20 Mar 2007 20:21] Bogdan Degtyariov
patch with fixed compiling issue

Attachment: bug5853.diff (application/octet-stream, text), 17.87 KiB.

[21 Mar 2007 16:59] Jim Winstead
The fix for this has been pushed the repository, and will be in the next release (3.51.15).
[26 Mar 2007 6:37] MC Brown
A note has been added to the 3.51.15 changelog.