Description:
A statement like
retcode = SQLSetPos(hStmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
cause a heap violation (resulting in a crash) or fails with an error message, when the SQL statement contains a "?", e.g.:
retcode = SQLExecDirect(hStmt, (_TUCHAR*)_T("SELECT * FROM `testtab` WHERE `name` = ?"), SQL_NTS);
The crash does not occur if '?' is replaced by a string literal.
The error message is:
HY000: [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.11]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 '`name`=,`myval`= WHERE `id`= LIMIT 1' at line 1
How to repeat:
Prepare the database:
CREATE TABLE testtab
(
id INTEGER NOT NULL PRIMARY KEY,
name CHAR(80) NOT NULL,
myval INTEGER NOT NULL
)
INSERT INTO testtab VALUES (1, 'This is a test', 100)
Compile and execute the following code:
#include "stdafx.h"
// Put your ODBC connect string here:
#define CONNECT_STRING _T("DSN=FMUMYSQL_FMU1;SERVER=fmumysql;UID=fmu1;PWD=fmu1;DATABASE=fmuschema;PORT=3306")
#define CHECK_ERR(retcode, hStmt, msg) \
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) \
{ \
Error(hStmt, msg, retcode); \
}
CString GetOdbcErrorText(HSTMT hStmt)
{
CString strMsg;
_TUCHAR szSQLState[256];
SDWORD fNativeError;
_TUCHAR szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
SWORD cbErrorMsg;
RETCODE retcode;
for (SQLSMALLINT nRec=1;; nRec++)
{
retcode = SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, nRec,
szSQLState, &fNativeError, szErrorMsg,
SQL_MAX_MESSAGE_LENGTH-1, &cbErrorMsg);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
CString strTmp;
strTmp.Format(_T("%s: %s "), szSQLState, szErrorMsg);
strMsg += strTmp;
}
else
{
return strMsg;
}
}
return CString();
}
static void Error(HSTMT hStmt, LPCTSTR szMsg, RETCODE retcode)
{
CString strOdbcErr;
if (hStmt != NULL)
{
strOdbcErr = GetOdbcErrorText(hStmt);
}
_ftprintf(stderr, _T("%s %s (retcode %d)\n"), szMsg, (LPCTSTR) strOdbcErr, (int) retcode);
exit(EXIT_FAILURE);
}
int _tmain(int argc, _TCHAR* argv[])
{
// Allocate handles:
HENV hEnv;
SQLRETURN retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
CHECK_ERR(retcode, NULL, _T("Cannot allocate an ODBC environment"));
retcode = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
CHECK_ERR(retcode, NULL, _T("Cannot set ODBC version"));
HDBC hDbc;
retcode = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
CHECK_ERR(retcode, NULL, _T("Cannot allocate a connection handle"));
// Connect to database:
_TUCHAR szBuf[1024];
short wLen;
retcode = SQLDriverConnect(hDbc, NULL, (_TUCHAR*) CONNECT_STRING,
(SQLSMALLINT) _tcslen(CONNECT_STRING), szBuf, sizeof(szBuf), &wLen,
SQL_DRIVER_NOPROMPT);
CHECK_ERR(retcode, NULL, _T("Cannot connect to ODBC data source"));
// Allocate statement handle:
HSTMT hStmt;
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
CHECK_ERR(retcode, NULL, _T("Cannot allocate a statement handle"));
// Bind input parameter for column "name":
SQLLEN uLen = 28;
retcode = SQLBindParameter(hStmt,
1, SQL_PARAM_INPUT, SQL_C_WCHAR,
SQL_WCHAR, 14, 0,
L"This is a test", 28, &uLen);
CHECK_ERR(retcode, hStmt, _T("Cannot bind column 1 for update"));
// Perform a bulk-insert of all rows:
retcode = SQLExecDirect(hStmt, (_TUCHAR*)_T("SELECT * FROM `testtab` WHERE `name` = ?"), SQL_NTS);
CHECK_ERR(retcode, hStmt, _T("Failed to execute SELECT statement"));
// Bind three columns id, name, and myval:
long nId = 0;
SQLLEN lenId = 4;
retcode = SQLBindCol(hStmt, 1, SQL_C_SLONG, &nId, lenId, &lenId);
CHECK_ERR(retcode, hStmt, _T("Cannot bind column 'id'"));
wchar_t szName[81];
SQLLEN lenName = 0;
retcode = SQLBindCol(hStmt, 2, SQL_C_WCHAR, szName, 81*sizeof(wchar_t), &lenName);
CHECK_ERR(retcode, hStmt, _T("Cannot bind column 'name'"));
long nMyVal = 0;
SQLLEN lenMyVal = 4;
retcode = SQLBindCol(hStmt, 3, SQL_C_SLONG, &nMyVal, lenMyVal, &lenMyVal);
CHECK_ERR(retcode, hStmt, _T("Cannot bind column 'myVal'"));
// Fetch first row:
retcode = SQLFetch(hStmt);
CHECK_ERR(retcode, hStmt, _T("SQLFetch failed"));
// Change 'myVal' column to 200 when SQLSetPos is called:
nMyVal = 200;
// Update values in columns 'id', 'name', and 'myVal':
retcode = SQLSetPos(hStmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
CHECK_ERR(retcode, hStmt, _T("SQLSetPos failed"));
assert(_CrtCheckMemory());
// Free all handles:
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
_tprintf(_T("Done\n"));
return 0;
}
stdafx.h:
#pragma once
#include "targetver.h"
#define _ATL_CSTRING_EXPLICIT_CONSTRUCTORS // some CString constructors will be explicit
#include <atlbase.h>
#include <atlstr.h>
#include <sql.h>
#include <sqlext.h>
#include <assert.h>
Description: A statement like retcode = SQLSetPos(hStmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE); cause a heap violation (resulting in a crash) or fails with an error message, when the SQL statement contains a "?", e.g.: retcode = SQLExecDirect(hStmt, (_TUCHAR*)_T("SELECT * FROM `testtab` WHERE `name` = ?"), SQL_NTS); The crash does not occur if '?' is replaced by a string literal. The error message is: HY000: [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.11]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 '`name`=,`myval`= WHERE `id`= LIMIT 1' at line 1 How to repeat: Prepare the database: CREATE TABLE testtab ( id INTEGER NOT NULL PRIMARY KEY, name CHAR(80) NOT NULL, myval INTEGER NOT NULL ) INSERT INTO testtab VALUES (1, 'This is a test', 100) Compile and execute the following code: #include "stdafx.h" // Put your ODBC connect string here: #define CONNECT_STRING _T("DSN=FMUMYSQL_FMU1;SERVER=fmumysql;UID=fmu1;PWD=fmu1;DATABASE=fmuschema;PORT=3306") #define CHECK_ERR(retcode, hStmt, msg) \ if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) \ { \ Error(hStmt, msg, retcode); \ } CString GetOdbcErrorText(HSTMT hStmt) { CString strMsg; _TUCHAR szSQLState[256]; SDWORD fNativeError; _TUCHAR szErrorMsg[SQL_MAX_MESSAGE_LENGTH]; SWORD cbErrorMsg; RETCODE retcode; for (SQLSMALLINT nRec=1;; nRec++) { retcode = SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, nRec, szSQLState, &fNativeError, szErrorMsg, SQL_MAX_MESSAGE_LENGTH-1, &cbErrorMsg); if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { CString strTmp; strTmp.Format(_T("%s: %s "), szSQLState, szErrorMsg); strMsg += strTmp; } else { return strMsg; } } return CString(); } static void Error(HSTMT hStmt, LPCTSTR szMsg, RETCODE retcode) { CString strOdbcErr; if (hStmt != NULL) { strOdbcErr = GetOdbcErrorText(hStmt); } _ftprintf(stderr, _T("%s %s (retcode %d)\n"), szMsg, (LPCTSTR) strOdbcErr, (int) retcode); exit(EXIT_FAILURE); } int _tmain(int argc, _TCHAR* argv[]) { // Allocate handles: HENV hEnv; SQLRETURN retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv); CHECK_ERR(retcode, NULL, _T("Cannot allocate an ODBC environment")); retcode = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0); CHECK_ERR(retcode, NULL, _T("Cannot set ODBC version")); HDBC hDbc; retcode = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc); CHECK_ERR(retcode, NULL, _T("Cannot allocate a connection handle")); // Connect to database: _TUCHAR szBuf[1024]; short wLen; retcode = SQLDriverConnect(hDbc, NULL, (_TUCHAR*) CONNECT_STRING, (SQLSMALLINT) _tcslen(CONNECT_STRING), szBuf, sizeof(szBuf), &wLen, SQL_DRIVER_NOPROMPT); CHECK_ERR(retcode, NULL, _T("Cannot connect to ODBC data source")); // Allocate statement handle: HSTMT hStmt; retcode = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt); CHECK_ERR(retcode, NULL, _T("Cannot allocate a statement handle")); // Bind input parameter for column "name": SQLLEN uLen = 28; retcode = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WCHAR, 14, 0, L"This is a test", 28, &uLen); CHECK_ERR(retcode, hStmt, _T("Cannot bind column 1 for update")); // Perform a bulk-insert of all rows: retcode = SQLExecDirect(hStmt, (_TUCHAR*)_T("SELECT * FROM `testtab` WHERE `name` = ?"), SQL_NTS); CHECK_ERR(retcode, hStmt, _T("Failed to execute SELECT statement")); // Bind three columns id, name, and myval: long nId = 0; SQLLEN lenId = 4; retcode = SQLBindCol(hStmt, 1, SQL_C_SLONG, &nId, lenId, &lenId); CHECK_ERR(retcode, hStmt, _T("Cannot bind column 'id'")); wchar_t szName[81]; SQLLEN lenName = 0; retcode = SQLBindCol(hStmt, 2, SQL_C_WCHAR, szName, 81*sizeof(wchar_t), &lenName); CHECK_ERR(retcode, hStmt, _T("Cannot bind column 'name'")); long nMyVal = 0; SQLLEN lenMyVal = 4; retcode = SQLBindCol(hStmt, 3, SQL_C_SLONG, &nMyVal, lenMyVal, &lenMyVal); CHECK_ERR(retcode, hStmt, _T("Cannot bind column 'myVal'")); // Fetch first row: retcode = SQLFetch(hStmt); CHECK_ERR(retcode, hStmt, _T("SQLFetch failed")); // Change 'myVal' column to 200 when SQLSetPos is called: nMyVal = 200; // Update values in columns 'id', 'name', and 'myVal': retcode = SQLSetPos(hStmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE); CHECK_ERR(retcode, hStmt, _T("SQLSetPos failed")); assert(_CrtCheckMemory()); // Free all handles: SQLFreeHandle(SQL_HANDLE_STMT, hStmt); SQLFreeHandle(SQL_HANDLE_DBC, hDbc); SQLFreeHandle(SQL_HANDLE_ENV, hEnv); _tprintf(_T("Done\n")); return 0; } stdafx.h: #pragma once #include "targetver.h" #define _ATL_CSTRING_EXPLICIT_CONSTRUCTORS // some CString constructors will be explicit #include <atlbase.h> #include <atlstr.h> #include <sql.h> #include <sqlext.h> #include <assert.h>