Unexpected output of embedded SQL program

Question

I'm trying to implement an absolute basic toy example using ECPG to run an embedded SQL query in C. This is my code:

int main() {

    EXEC SQL WHENEVER NOT FOUND DO BREAK;
    EXEC SQL BEGIN DECLARE SECTION;
    char v_country_iso2[2], v_name[32];
    EXEC SQL END DECLARE SECTION;
    
    // Connect to database
    EXEC SQL BEGIN DECLARE SECTION;
    const char *target = "exampledb@localhost";
    const char *user = "demo";
    const char *password = "topsecret";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL CONNECT TO :target USER :user USING :password;
    
    // Declare cursor
    EXEC SQL DECLARE c CURSOR FOR
    SELECT country_iso2, name FROM cities LIMIT 3;
    
    // Open cursor
    EXEC SQL OPEN c;
    
    // Loop through cursor and display results
    for (;;) {
        EXEC SQL FETCH NEXT FROM c INTO :v_country_iso2, :v_name;
        printf(">>> Country (ISO-2): %s, City: %s\n", v_country_iso2, v_name);
    }
    
    // Clean-up (close cursor, commit, disconnect)
    EXEC SQL CLOSE c;
    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT;
    
    return 0;   
}

The code compiles and runs just fine. However, the output is the following

>>> Country (ISO-2): JPTokyo, City: Tokyo
>>> Country (ISO-2): IDJakarta, City: Jakarta
>>> Country (ISO-2): INDelhi, City: Delhi

when the expected output is:

>>> Country (ISO-2): JP, City: Tokyo
>>> Country (ISO-2): ID, City: Jakarta
>>> Country (ISO-2): IN, City: Delhi

It seems that v_country_iso2 is a concatenation of the country code and the city name, and I have no idea where and why this happens. I'm sure the data is correct in the database table. Also, if I only fetch the country code with SELECT country_iso2 FROM cities LIMIT 3; then I get indeed only the country code.

I'm using a PostgreSQL database with a simple table cities with just for columns including country_iso2 and name.

I'm sure that I'm making a really stupid mistake somewhere, but just can see it. I haven't touched C/C++ in ages, but the code looks simple enough.

Answer 1

The problem is caused by printf with %s format. When you pass an unterminated string, printf keeps printing outside the bounds of the array. In your case it happens to print the following string in memory but it could just as easily crash. To fix this increase the size and after reading 2 chars from the SQL add the terminator before printing the string.

v_country_iso2[2] = '\0';

Note that you should most likely do this for city as well.