//utf8 charset bug test

//====Setup Instructions=====

//Create a file called 'textfile' and paste some english and japanese text

//====create a database and table:====

//create database bugtest;
//use bugtest;
//CREATE TABLE testtable (id bigint(20) AUTO_INCREMENT PRIMARY KEY, textdata longtext) ENGINE=InnoDB;

//=====verify default charset is utf8mb4 and collate is utf8mb4_0900_ai_ci:===

//show create table testtable;

//====Create a user for the database and set permissions:===

//create user 'guest'@'localhost' identified by 'qwer';
//grant insert on testtable to 'guest'@'localhost';
//grant select on testtable to 'guest'@'localhost';

//==== Now compile and run this program ====

//gcc bugtest.c -o bugtest -I/usr/include/mysql -lmysqlclient -std=c99

#include <mysql.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>

char *payload, *inserttext;
FILE *textfile;

void finish_with_error(MYSQL *con)
{
  fprintf(stderr, "%s\n", mysql_error(con));
  mysql_close(con);
  exit(1);        
}

int main(int argc, char **argv)
{
	//allocates or initialises a MYSQL object
	MYSQL *con = mysql_init(NULL);
	if (con == NULL) 
	{
		finish_with_error(con);
	}

	//establish a connection to the database.  We provide connection handler, host name, user name and password parameters to the function. The other four parameters are the database name, port number, unix socket and finally the client flag
	if (mysql_real_connect(con, "localhost", "guest", "qwer", NULL, 0, NULL, 0) == NULL) 
	{
		finish_with_error(con);
	} 
	
	if (mysql_query(con, "use bugtest;")) 
	{
	    finish_with_error(con);
	}

	//this should allow insert of Japanese text but it doesn't! using "SET CHARSET cp932" will work, using "SET CHARSET utf8mb4" will truncate japanese text, using "SET CHARSET utf8" will insert gibberish
	if (mysql_query(con, "SET CHARSET utf8;")) 
	{
	    finish_with_error(con);
	}

	//Allocate 10000 bytes for the text file
	payload = (char*)calloc(10000,sizeof(char));
	//Allocate 11000 bytes for when appending payload to insert statement
	inserttext = (char*)calloc(11000,sizeof(char));
	
	//load text file (put a mixture of english and japanese in there)
	int c=0;
	int position=0;
	textfile = fopen("textfile","rb");
	while((c = fgetc(textfile)) != EOF && position < 9998){
		payload[position] = c;
		position ++;
	}
	fclose(textfile);

	//Insert the text into the database
	printf("\nInserting textfile contents into database...");
	strcpy(inserttext,"INSERT INTO testtable (textdata) VALUES ('");
	strcat(inserttext,payload);
	strcat(inserttext,"');");
	if (mysql_query(con, inserttext)) 
	{
	    finish_with_error(con);
	}

	//free allocated memory
	free(payload); 
	free(inserttext); 
	
	//close the connection
	mysql_close(con);

	printf("\n\nSuccess! Now go manually check the table data and see what happened:\n\n   \"select * from testtable;\"\n\n");
}