Author Topic: Raspberry pi, C/C++, mySql, Instert in table string that contains "  (Read 197 times)

0 Members and 1 Guest are viewing this topic.

Offline georgian

  • Contributor
  • Posts: 14
  • Country: at
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.
 

Online greenpossum

  • Frequent Contributor
  • **
  • Posts: 258
  • Country: au
Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
« Reply #1 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
 
The following users thanked this post: georgian

Online SiliconWizard

  • Super Contributor
  • ***
  • Posts: 5324
  • Country: fr
Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
« Reply #2 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/

Fancier answer: what greenpossum suggests above, in order to avoid SQL injection issues.
 
The following users thanked this post: georgian

Offline georgian

  • Contributor
  • Posts: 14
  • Country: at
Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
« Reply #3 on: April 30, 2020, 03:35:37 pm »
Thank you for your answers. I will try and get back.
 

Offline georgian

  • Contributor
  • Posts: 14
  • Country: at
Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
« Reply #4 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;

}
 

Online SiliconWizard

  • Super Contributor
  • ***
  • Posts: 5324
  • Country: fr
Re: Raspberry pi, C/C++, mySql, Instert in table string that contains "
« Reply #5 on: April 30, 2020, 10:57:45 pm »
That's good to know. And glad you took the prepared statement approach!
 

Offline RenThraysk

  • Contributor
  • Posts: 15
  • Country: gb
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
 


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf