#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    1
    Rep Power
    0

    MySQL Insert query in gcc/Cygwin poses problem in a repeated loop


    My simple code looks like this:
    #include <windows.h>
    #include <stdio.h>
    #include <stdlib.h>
    #include "mysql.h"



    int main( )
    {
    int i=0;
    MYSQL *conn;

    int ret;
    char str[200] ;
    char mote_mac[20]="1122334455667788";
    char mote_id[10]="W02";
    char CO2[10]="123.123";
    char NO2[11]="111.111";
    char CO[10]="222.222";
    int temp=28;
    int batt=78;

    conn = mysql_init(NULL);

    if (!mysql_real_connect(conn, "localhost", "root", "admin","WASPDB", 0, NULL, 0))
    {
    fprintf(stderr,"Couldn't connect to DB!\n%s\n\n",mysql_error(conn));
    perror("");
    exit(1);
    } else {

    fprintf(stderr, "Connected to database: return: %s\n", mysql_error(conn));
    }

    while(1) {

    sprintf( str, "insert into mote_data values(NULL,'%s','%s','%s','%s','%s','%d','%d');", mote_id, mote_mac, CO2,NO2,CO,temp,batt);

    fprintf(stdout,"query is:str len %d: string is %s str pointer %d\n\n",strlen(str),str,&str);

    if(mysql_real_query(conn, str,strlen(str)) !=0 )
    {
    fprintf(stderr,"Query failed (%s)\n",mysql_error(conn));
    exit(1);
    } else
    fprintf(stdout,"Insertion in DB succeeded...\n\n");
    }

    mysql_close(conn);
    exit(0);
    return 0;
    }

    =======================
    Output looks like this:
    $ ./usr/asprin
    Connected to database: return:
    query is:str len 100: string is insert into mote_data values(NULL,'W02','1122334455667788','123.123','111.111','222.222','28','78'); str pointer 2272128

    Insertion in DB succeeded...

    query is:str len 101: string is insert into mote_data values(NULL,'W02','1122334455667788','123.123','111.111','222.222','28','▒"'); str pointer 2272128

    Insertion in DB succeeded...

    query is:str len 99: string is insert into mote_data values(NULL,'W02','1122334455667788','123.123','111.111','222.222','','@▒"'); str pointer 2272128

    Insertion in DB succeeded...

    query is:str len 102: string is insert into mote_data values(NULL,'W02','1122334455667788','123.123','111.111','222.222','0▒"','▒ @'); str pointer 2272128

    Insertion in DB succeeded...

    query is:str len 98: string is insert into mote_data values(NULL,'W02','1122334455667788','123.123','111.111',' ▒"','P▒"','h▒"'); str pointer 2272128

    Insertion in DB succeeded...

    query is:str len 98: string is insert into mote_data values(NULL,'W02','1122334a▒▒"','▒"'); str pointer 2272128'Eg

    Insertion in DB succeeded...

    query is:str len 85: string is insert into mote_data values(NULL,'W02','1122334455667788','','@▒"','▒▒"','','▒"'); str pointer 2272128

    Insertion in DB succeeded...

    query is:str len 92: string is insert into mote_data values(NULL,'W02','1122334455667788','0▒"','▒ @','U',' wa`!@','
    ▒"'); str pointer 2272128

    Insertion in DB succeeded...

    query is:str len 92: string is insert into mote_data values(NULL,' ▒"','1122334455667788','P▒"','h▒"','`!@','0▒"','▒▒Xg\'); str pointer 2272128

    Query failed (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''▒▒Xg\')' at line 1)

    user@notebook /cygdrive/c/cygwin
    $
    ========================================
    First record gets inserted successfully and then from second record onwards junks are entering into fields. I am unable to find out a reason. without the MySQL insert query, the program works fine ... say sprintf works fine. I changed sprintf to asprintf, vsprintf etc., only 2 or 3 records at the maximum are successful

    anybody could help ??

    Thanks
    gomes
  2. #2
  3. I'm Baaaaaaack!
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Jul 2003
    Location
    Maryland
    Posts
    5,538
    Rep Power
    244
    Your output has all the signs of a buffer overrun (actually, it looks like an underrun to me). However, it does not appear to be in your code, but appears to be in the MySQL lib somehow. What version of MySQL are you using? If it is old, the simplest solution is to just upgrade. If it is the recent version you might want to create a bug ticket, this is just the sort of thing they like, small code that clearly demonstrates a problem.

    BTW, if you post here again, please use "code" tags (see FAQ), you will get better results.

    My blog, The Fount of Useless Information http://sol-biotech.com/wordpress/
    Free code: http://sol-biotech.com/code/.
    Secure Programming: http://sol-biotech.com/code/SecProgFAQ.html.
    Performance Programming: http://sol-biotech.com/code/PerformanceProgramming.html.
    LinkedIn Profile: http://www.linkedin.com/in/keithoxenrider

    It is not that old programmers are any smarter or code better, it is just that they have made the same stupid mistake so many times that it is second nature to fix it.
    --Me, I just made it up

    The reasonable man adapts himself to the world; the unreasonable one persists in trying to adapt the world to himself. Therefore, all progress depends on the unreasonable man.
    --George Bernard Shaw
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    187
    Rep Power
    83
    I ran your code against my mySql database, version 5.5.28 without any problems.

    You may want to initialize that local str variable. Just can't predict what may be in that variable which might cause your app to choke if it's not initialized as follows:

    Code:
    char str[200] = {0} ;
  6. #4
  7. I'm Baaaaaaack!
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Jul 2003
    Location
    Maryland
    Posts
    5,538
    Rep Power
    244
    sprintf() will NULL terminate the string.

    My blog, The Fount of Useless Information http://sol-biotech.com/wordpress/
    Free code: http://sol-biotech.com/code/.
    Secure Programming: http://sol-biotech.com/code/SecProgFAQ.html.
    Performance Programming: http://sol-biotech.com/code/PerformanceProgramming.html.
    LinkedIn Profile: http://www.linkedin.com/in/keithoxenrider

    It is not that old programmers are any smarter or code better, it is just that they have made the same stupid mistake so many times that it is second nature to fix it.
    --Me, I just made it up

    The reasonable man adapts himself to the world; the unreasonable one persists in trying to adapt the world to himself. Therefore, all progress depends on the unreasonable man.
    --George Bernard Shaw
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    187
    Rep Power
    83
    I was thinking more along the lines of Unitialized local variables since I've been there and done that.

    For example, I've run into grief when there was an unprintable character in the middle of my string. So, I've learned the hard way to zero out my local variables before using them.
  10. #6
  11. I'm Baaaaaaack!
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Jul 2003
    Location
    Maryland
    Posts
    5,538
    Rep Power
    244
    A good compiler with warnings turned up will alert you to the use of uninitialized variables. However, your recommendation is the best way to proceed.

    My blog, The Fount of Useless Information http://sol-biotech.com/wordpress/
    Free code: http://sol-biotech.com/code/.
    Secure Programming: http://sol-biotech.com/code/SecProgFAQ.html.
    Performance Programming: http://sol-biotech.com/code/PerformanceProgramming.html.
    LinkedIn Profile: http://www.linkedin.com/in/keithoxenrider

    It is not that old programmers are any smarter or code better, it is just that they have made the same stupid mistake so many times that it is second nature to fix it.
    --Me, I just made it up

    The reasonable man adapts himself to the world; the unreasonable one persists in trying to adapt the world to himself. Therefore, all progress depends on the unreasonable man.
    --George Bernard Shaw
  12. #7
  13. Contributed User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jun 2005
    Posts
    4,417
    Rep Power
    1871
    The bit I don't get is how %d in
    sprintf( str, "insert into mote_data values(NULL,'%s','%s','%s','%s','%s','%d','%d');", mote_id, mote_mac, CO2,NO2,CO,temp,batt);

    Managed to produce corrupted characters.
    query is:str len 101: string is insert into mote_data values(NULL,'W02','1122334455667788','123.123','111.111','222.222','28','▒"'); str pointer 2272128

    Now if batt were trashed, you'd still see a string of 0-9 digits either way.

    My suspicion is that this isn't the real code that crashed, but something cut down for handy posting.
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper
  14. #8
  15. I'm Baaaaaaack!
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Jul 2003
    Location
    Maryland
    Posts
    5,538
    Rep Power
    244
    That is probably a better explanation than mine as MySQL is heavily used and a bug this obvious would almost certainly have been caught by internal testing.

    My blog, The Fount of Useless Information http://sol-biotech.com/wordpress/
    Free code: http://sol-biotech.com/code/.
    Secure Programming: http://sol-biotech.com/code/SecProgFAQ.html.
    Performance Programming: http://sol-biotech.com/code/PerformanceProgramming.html.
    LinkedIn Profile: http://www.linkedin.com/in/keithoxenrider

    It is not that old programmers are any smarter or code better, it is just that they have made the same stupid mistake so many times that it is second nature to fix it.
    --Me, I just made it up

    The reasonable man adapts himself to the world; the unreasonable one persists in trying to adapt the world to himself. Therefore, all progress depends on the unreasonable man.
    --George Bernard Shaw

IMN logo majestic logo threadwatch logo seochat tools logo