MySQL Bugs: #62367: mysql_stmt_execute crashing with a prepared query having where clause.

Bug #62367	mysql_stmt_execute crashing with a prepared query having where clause.
Submitted:	7 Sep 2011 6:56	Modified:	28 Aug 2013 4:40
Reporter:	amit mall	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S1 (Critical)
Version:	MySql server 5.0,MySql connector c 6.0.2	OS:	Windows (windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	prepared statement, where clause

Description:
I am executing a prepared statement. the prepared query has one "where" clause whose value I am binding. When I am executing the prepared query it is crashing with segmentation fault of accessing 0x00000001.

when where clause column is number it is working fine. But with varchar it is crashing. the snippet of my code is as follows..

   prepquery = "SELECT * from test WHERE name = ?";
    
    if (mysql_stmt_prepare(stmt, prepquery, strlen(prepquery))) {
    
        fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    
        return ;
    }

    if (mysql_stmt_param_count(stmt) != 1) {
        fprintf(stderr, " invalid parameter count returned by MySQL\n");

        return ;
    }

        char * data = "Name";
        ULong len;
        len = strlen (data);
        ULong collen;

    //initialise bind structure
    /* STRING PARAM */
    bind[0].buffer_type= MYSQL_TYPE_VARCHAR;
    bind[0].buffer= data;
    bind[0].buffer_length = len;
    bind[0].length= &collen;

    /* Bind the buffers */
    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, " mysql_stmt_bind_param() failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

        return ;
    }

    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

        return ;
    }

How to repeat:
   prepquery = "SELECT * from test WHERE name = ?";
    
    if (mysql_stmt_prepare(stmt, prepquery, strlen(prepquery))) {
    
        fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    
        return ;
    }

    if (mysql_stmt_param_count(stmt) != 1) {
        fprintf(stderr, " invalid parameter count returned by MySQL\n");

        return ;
    }

        char * data = "Name";
        ULong len;
        len = strlen (data);
        ULong collen;

    //initialise bind structure
    /* STRING PARAM */
    bind[0].buffer_type= MYSQL_TYPE_VARCHAR;
    bind[0].buffer= data;
    bind[0].buffer_length = len;
    bind[0].length= &collen;

    /* Bind the buffers */
    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, " mysql_stmt_bind_param() failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

        return ;
    }

    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

        return ;
    }

Could you please provide a complete test case code. Thanks in  advance.

This is my function which performs a select query. The query is prepared statement which takes name parameter (varchar string) from bind structure.

ULong Select (MYSQL * pCon)
{
    MYSQL_STMT *    stmt;
    MYSQL_BIND      bind[1];
    char *          prepquery;

    //initialise statement
    stmt = mysql_stmt_init(pCon);
    if (!stmt) {
        //case: error occured in initialisation
        fprintf(stderr, " mysql_stmt_init(), out of memory\n");

        return 0;
    }

    prepquery = "SELECT * from test WHERE name = ?";
    
    if (mysql_stmt_prepare(stmt, prepquery, strlen(prepquery))) {
    
        fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    
        return 0;
    }

    if (mysql_stmt_param_count(stmt) != 1) {
        fprintf(stderr, " invalid parameter count returned by MySQL\n");

        return 0;
    }

        char * data = (char *) TEXT("Name");
        data = "Name";
        ULong len = _tcslen ((Char *)data) * 2 + 2;
        len = strlen (data) + 1;
        ULong collen;
        collen = len;
    //initialise bind structure
    /* STRING PARAM */
    bind[0].buffer_type= MYSQL_TYPE_BLOB;
    bind[0].buffer= data;
    bind[0].buffer_length = len;
    bind[0].length= &collen;

    /* Bind the buffers */
    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, " mysql_stmt_bind_param() failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

        return 0;
    }

    /* Execute the INSERT statement - 1*/
    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");
        fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

        return 0;
    }

    return 0;

}

Please, try to work with string data using the way described in the example in the manual, http://dev.mysql.com/doc/refman/5.5/en/mysql-stmt-execute.html. That is, pre-allocate memory for data buffer, then bind, then copy string value and set proper string length. Check if that solves the crash problem.

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 program fails with a segmentation fault on Mac OS/X Mountain Lion version 10.8.2 when compiled against the embedded mysqld library.
The same program succeeds on the same platform when compiled against the mysql threaded client libraries.

The segfault occurs in the mysql_stmt_execute function.

I used the prebuilt installer for MYSQL pulled from the official website entitled: mysql-5.5.29-osx10.6-x86_64.dmg
I installed the sakila database (specifically the country table) and populated it from the default scripts provided on the MYSQL website

I am using gcc version 4.2.1 (as provided by XCode 4.6 command line tools installer).
I compile to a build directory (here just called 'build/'), you'll need to create it to use my g++ lines (below) verbatim.

The working version of the program (bug) is compiled in the following way and run against the installed MYSQL database instance:
The Makefile actually uses the 'mysql_config --include' and 'mysql_config --libs_r' scripts for many of the switches below:

g++ -Wall -g -O0 -c bug.cpp -I/usr/local/mysql/include  -Os -g -fno-common -fno-strict-aliasing -arch x86_64 -o build/bug.o
g++ -Wall build/bug.o -I/usr/local/mysql/include  -Os -g -fno-common -fno-strict-aliasing -arch x86_64  -o build/bug -L/usr/local/mysql/lib -lmysqlclient_r   -lpthread -Lbuild/

The version of the program (bug) that produces a segfault is compiled in the following way and run against an embedded MYSQL database instance (created in my
home directory using the /usr/local/mysql/scripts/mysql_install_db script)
As before, the Makefile actually uses the 'mysql_config --include' and 'mysql_config --libmysqld-libs' scripts to obtain many of the swithes below:

g++ -Wall -g -O0 -c bug.cpp -I/usr/local/mysql/include  -Os -g -fno-common -fno-strict-aliasing -arch x86_64 -o build/bug.o
g++ -Wall build/bug.o -I/usr/local/mysql/include  -Os -g -fno-common -fno-strict-aliasing -arch x86_64  -o build/bug -L/usr/local/mysql/lib -lmysqld    -lpthread -Lbuild/

////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////

the following is the contents of bug.cpp:

////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////

#include <iostream>
#include <vector>
#include <fstream>
#include <sys/stat.h>

#include <typeinfo>

#include "my_global.h"
#include "mysql.h"

using namespace std;

static void print_stmt_error (MYSQL_STMT *stmt, const char *message) {
	fprintf (stderr, "%s\n", message);
	if (stmt != NULL) {
		fprintf(stderr, "Error %u (%s): %s\n",
			mysql_stmt_errno(stmt),
			mysql_stmt_sqlstate(stmt),
			mysql_stmt_error(stmt)); 
	} 
}

int main(int argc, char** argv) {
	bool embedded = false;
	if (argc > 1 && (strcmp(argv[1], "embedded") == 0)) {
		embedded = true;
	}

	if (embedded) {
		cout << "Hello you are connected in embedded mode" << endl;

		static const char *server_args[] = { "this_program", "--basedir=/usr/local/mysql", "--datadir=/Users/ravidesai/mysql/data", 
					     	"--plugin-dir=/Users/ravidesai/mysql/plugins", "--log-error=/Users/ravidesai/mysql/tmp/test.err",
					     	"--pid-file=/Users/ravidesai/mysql/tmp/test.pid",
					     	"--key_buffer_size=32M", "--log-bin=/Users/ravidesai/mysql/log/logbin"
					     	"--log-bin-trust-function-creators=1"
					     	"--log-bin-trust-routine-creators=1"
					    	};
		static const char *server_groups[] = { "embedded", "server", "this_program_SERVER", (char *) NULL };
	
		if (mysql_library_init(sizeof(server_args) / sizeof(char *), (char**) server_args, (char **)server_groups) != 0) {
			cout << "Error in Database::Initialize" << endl;
			return 1;
		}
	} else {
		cout << "Hello you are connected in standard client (non-embedded) mode" << endl;
		if (mysql_library_init(0, NULL, NULL) != 0) {
			cout << "Error in Database::Initialize" << endl;
		}
	}

	if (mysql_thread_init() != 0) {
		cout << "Error in Database::ThreadInitialize" << endl;
	}

	MYSQL *connect;
	connect = mysql_init(NULL);
	if (! connect) {
		cout << "MySQL initialization failure" << endl;
		return 1;
	}

	if (embedded) {
		mysql_options(connect, MYSQL_READ_DEFAULT_GROUP, "embedded");
		mysql_options(connect, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
	}

	connect = mysql_real_connect(connect, "localhost", "root", "", "sakila", 0, NULL, 0);
	if (connect)
	{
		cout << "Connection succeeded" << endl;
	}	

	const char *stmt_str = "SELECT * FROM COUNTRY WHERE COUNTRY = ?";
	MYSQL_STMT *stmt;

	stmt = mysql_stmt_init(connect);

	if (mysql_stmt_prepare(stmt, stmt_str, strlen(stmt_str)) != 0) {
		print_stmt_error(stmt, "Count not prepare SELECT statement");
		return 1;
	}

	int numberParams = mysql_stmt_param_count(stmt);
	cout << "Number parameters: " << numberParams << endl;
	MYSQL_BIND param[numberParams];
	MYSQL_RES *metaData;
	if ((metaData = mysql_stmt_result_metadata(stmt)) == NULL) {
		print_stmt_error(stmt, "Error fetching stmt metadata");
	}

	int numberFields = 0;
	MYSQL_FIELD *field;
	while ((field = mysql_fetch_field(metaData)) != NULL) {
		numberFields++;
		cout << "[" << field->name << ", " << field->type <<  "] ";
	}
	cout << endl;

	const int STRING_SIZE = 50;

	int countryId;
	char countryName[STRING_SIZE];
	unsigned long countryNameLen;
	MYSQL_TIME lastUpdate;
	my_bool is_null[numberFields];

	MYSQL_BIND bind[numberFields];
	memset ((void *) &bind, 0, sizeof(bind));

	bind[0].buffer_type = MYSQL_TYPE_LONG;
	bind[0].buffer = (void *) &countryId;
	bind[0].is_unsigned = 0;
	bind[0].is_null = &is_null[0];

	bind[1].buffer_type = MYSQL_TYPE_STRING;
	bind[1].buffer = (void *) countryName;
	bind[1].is_null = &is_null[1];
	bind[1].buffer_length = sizeof(countryName);
	bind[1].length = &countryNameLen;

	bind[2].buffer_type = MYSQL_TYPE_DATETIME;
	bind[2].buffer = (void *) &lastUpdate;
	bind[2].is_null = &is_null[2];
	
	char thisCountry[STRING_SIZE];
	unsigned long thisCountryLen;

	param[0].buffer_type = MYSQL_TYPE_STRING;
	param[0].buffer = (char *)thisCountry;
	param[0].buffer_length = STRING_SIZE;
	param[0].is_null = 0;
	param[0].length = &thisCountryLen;

	cout << "binding parameters" << endl;
	if (mysql_stmt_bind_param(stmt, param) != 0) {
		print_stmt_error(stmt, "Could not bind SELECT params");
		return 1;
	}

	cout << "binding results" << endl;
	if (mysql_stmt_bind_result(stmt, bind) != 0) {
		print_stmt_error(stmt, "Count not bind SELECT results");
		return 1;
	}

	strncpy(thisCountry, "Canada", STRING_SIZE);
	thisCountryLen = strlen(thisCountry);

	cout << "executing statement" << endl;
	if (mysql_stmt_execute(stmt) != 0) {
		print_stmt_error(stmt, "Could not execute SELECT statement");
		return 1;
	}

	cout << "storing results" << endl;
	if (mysql_stmt_store_result(stmt) != 0) {
		print_stmt_error(stmt, "Could not execute STORE RESULT");
		return 1;
	}

	cout << "fetching statement" << endl;
	while (mysql_stmt_fetch(stmt) == 0) {
		printf("[%d] ", countryId);
		printf("[%.*s] ", (int) countryNameLen, countryName);
		printf("[%04d-%02d-%02d %02d:%02d:%02d]", 
			lastUpdate.year, lastUpdate.month, lastUpdate.day,
                     	lastUpdate.hour, lastUpdate.minute, lastUpdate.second);
		printf("\n");
	}

	cout << "closing statement" << endl;
	mysql_stmt_close(stmt);
	cout << "closing database" << endl;
	mysql_close(connect);

	return 0;
}

I have a similar problem, I'm using plain C in Ubuntu 12.04 (libmysqlclient18 version 5.5.31-0ubuntu0.12.04.2)

The simplest example to what I could narrow it is (essentially made of copy paste of example in http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-execute.html):

#define STRING_SIZE 50
#define QUERY "SELECT id FROM tablename WHERE name = ?"

char str_data[STRING_SIZE];
unsigned long str_length;

MYSQL_STMT *stmt = mysql_stmt_init(conn);

mysql_stmt_prepare(stmt, QUERY, strlen(QUERY));

strcpy(str_data, "test");
str_length = strlen(str_data);

MYSQL_BIND bind[1];

memset(bind, 0, sizeof(bind));

/* STRING PARAM */
bind[0].buffer_type= MYSQL_TYPE_STRING;
bind[0].buffer= (char *) str_data;
bind[0].buffer_length= str_length;
bind[0].is_null= 0;
bind[0].length= &str_length;

mysql_stmt_bind_param(stmt, bind);

mysql_stmt_execute(stmt); // SEGMENTATION FAULT

In http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-data-structures.html I read:

* MYSQL_BIND buffer_length: the buffer_length value specifies the length of *buffer when used with mysql_stmt_bind_param() to specify input values
* MYSQL_BIND length: For input parameter data binding, set *length to indicate the actual length of the parameter value stored in *buffer. This is used by mysql_stmt_execute().

As I understand buffer_length is used for mysql_stmt_bind_param and length for mysql_stmt_execute. This lead me to find the following workaround:

#define STRING_SIZE 50
#define QUERY "SELECT id FROM tablename WHERE name = ?"

char str_data[STRING_SIZE];
unsigned long str_length;

MYSQL_STMT *stmt = mysql_stmt_init(conn);

mysql_stmt_prepare(stmt, QUERY, strlen(QUERY));

strcpy(str_data, "test");
str_length = strlen(str_data);

MYSQL_BIND bind[1];

memset(bind, 0, sizeof(bind));

/* STRING PARAM */
bind[0].buffer_type= MYSQL_TYPE_STRING;
bind[0].buffer= (char *) str_data;
bind[0].buffer_length= str_length;
bind[0].is_null= 0;
bind[0].length= 0;// XXXXXXXXX as length is not used for mysql_stmt_bind_param I set it to NULL

mysql_stmt_bind_param(stmt, bind);

bind[0].length= &str_length; // XXXXXXXXXXX now set the length

mysql_stmt_execute(stmt); // NO SEGMENTATION FAULT and following code, mysql_stmt_bind_result, mysql_stmt_fetch, ... works as expected

Please try version 6.1. Thanks.

I could not repeat this issue using libmysqlclient version 5.5.31 on Linux. I used code identical to last example. Everything works as expected - no crash in mysql_stmt_execute(). I run test against 5.5.31 server.

I did not check embedded library though.

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

@Michel Ganguin Thank you so much from 2022! This issue has been a plague for me and your solution has solved my issue! Thanks again!