EEVblog Electronics Community Forum

Products => Computers => Programming => Topic started by: georgian on April 29, 2020, 08:16:55 pm

Title: Raspberry pi, C/C++, mySql, Instert in table string that contains "
Post by: georgian on April 29, 2020, 08:16:55 pm

Hello guys,
I have a problem and i have no idea how to solve it. I'm trying to save an received SMS in a database using an raspberry pi, C++ and mySql. It works for now, but if the SMS contains ["] this character, i get an error.

Using this line of codeI can insert the ["] character but i get an error wen i have the single one ['] like in It's working.

Code: [Select]

sprintf(tmp, "INSERT INTO INBOX (sender, sms, senderDateTime) VALUES ('%s','%s','%s')", sender, SMS, timeStamp);
if (mysql_query(con, tmp)) {
	finishWithError(con);
}

Now if I change the code to this one, I can store ['] this character but not the double one ["].

Code: [Select]

sprintf(tmp, "INSERT INTO INBOX (sender, sms, senderDateTime) VALUES (\"%s\",\"%s\",\"%s\")", sender, SMS, timeStamp);
if (mysql_query(con, tmp)) {
	finishWithError(con);
}

There must be some way to get rid of the error. So please let me know. I hope i give enough details.
Thank you,
Georgian Borca.

Title: Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
Post by: greenpossum on April 29, 2020, 10:42:42 pm

You should use prepared SQL statements, otherwise you are prone to SQL injection danger. That will also solve your quoting problem. See https://stackoverflow.com/questions/24698040/how-to-add-a-string-containg-a-double-quote-to-a-sql-database

Title: Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
Post by: SiliconWizard on April 29, 2020, 11:22:09 pm

Simple answer if you don't care much about security: escape the single quotes:
https://www.databasestar.com/sql-escape-single-quote/ (https://www.databasestar.com/sql-escape-single-quote/)

Fancier answer: what greenpossum suggests above, in order to avoid SQL injection issues.

Title: Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
Post by: georgian on April 30, 2020, 03:35:37 pm

Thank you for your answers. I will try and get back.

Title: Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
Post by: georgian on April 30, 2020, 07:34:19 pm

I finaly got it working after a lot o googleing. Thank you gus for pointing me in this direction.
No sure if this is the right way but it work  ;)

Code: [Select]

bool storeSMS(char *sms, char *sender, char *timeStamp) {
	memset(sqlStr, '\0', sizeof(sqlStr));
	strcpy(sqlStr, "INSERT INTO INBOX(sender, sms, senderdateTime) VALUES (?, ?, ?)");

	if (mysql_stmt_prepare(statement, sqlStr, strlen(sqlStr))) {
        fprintf(stderr, "ERROR:mysql_stmt_prepare() failed. Error:%s\nsql:%s\n", mysql_stmt_error(statement), sqlStr);
        exit(1);
       }

	MYSQL_BIND input_bind[3];
        memset(input_bind, 0, sizeof(input_bind));
	
	input_bind[0].buffer_type = MYSQL_TYPE_STRING;
	input_bind[0].buffer = sender;
	input_bind[0].buffer_length = strlen(sender);
	input_bind[0].length = 0;
	input_bind[0].is_null = 0;
	
	input_bind[1].buffer_type = MYSQL_TYPE_STRING;
	input_bind[1].buffer = sms;
	input_bind[1].buffer_length = strlen(sms);
	input_bind[1].length = 0;
	input_bind[1].is_null = 0;
	
	input_bind[2].buffer_type = MYSQL_TYPE_STRING;
	input_bind[2].buffer = timeStamp;
	input_bind[2].buffer_length = strlen(timeStamp);
	input_bind[2].length = 0;
	input_bind[2].is_null = 0;
	
	if (mysql_stmt_bind_param(statement, input_bind)) {
        fprintf(stderr, "ERROR:mysql_stmt_bind_param failed\n");
        exit(1);
    }

    if (mysql_stmt_execute(statement)) {
        fprintf(stderr, "mysql_stmt_execute(), failed. Error:%s\n", mysql_stmt_error(statement));
        exit(1);
    }

    return true;
	
}

Title: Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
Post by: SiliconWizard on April 30, 2020, 10:57:45 pm

That's good to know. And glad you took the prepared statement approach!

Title: Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
Post by: RenThraysk on May 17, 2020, 11:22:20 am

Bit late... but if you are using version 8.0 or later MySQL server...  the new X Protocol and X DevAPI is simpler.
Also gives the option of not using prepared statements, just parameterised ones.

https://dev.mysql.com/doc/dev/connector-cpp/8.0/xapi_ref.html