EEVblog Electronics Community Forum
Products => Computers => Programming => Topic started 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.
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 ["].
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.
-
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
-
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.
-
Thank you for your answers. I will try and get back.
-
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 ;)
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;
}
-
That's good to know. And glad you took the prepared statement approach!
-
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