MySQL Bugs: #5853: Using Update with 'WHERE CURRENT OF' with binary data crashes (fix included)

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:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.12	OS:	Unix
Assigned to:	Jim Winstead	CPU Architecture:	Any
Tags:	Contribution

Description:
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)
  		return(SQL_ERROR);
  }

Hi,

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:

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

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

Best Regards

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.

Ernesto.

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".

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.

Ernesto.

Updated the Synopsis field to reflect the actual problem (CURRENT OF vs CURSOR OF)

Ernesto

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.

A work-in-progress patch

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

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.

Fix looks good, and yes, this has been broken forever.

Complete patch, including tests

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

patch with fixed compiling issue

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

The fix for this has been pushed the repository, and will be in the next release (3.51.15).

A note has been added to the 3.51.15 changelog.