The get_stock( ) function provides all stock information from MySQL for the stock with the specified symbol. It formulates a query based on the ticker symbol and sticks the results into a Stock structure.

Stock *get_stock(char *symbol) {
    char *query = "SELECT symbol, openPrice, currPrice, high52, low52, 
                  FROM Stock WHERE symbol = '%s'";
    char *sql;
    int state;
  
    error = (char *)NULL;
    sql = (char *)malloc((strlen(query) + strlen(symbol) + 1) * sizeof(char));
    sprintf(sql, query, symbol);
    state = mysql_query(connection, sql);
    free(sql);
    if( state != 0 ) {
        error = mysql_error(connection);
        return (Stock *)NULL;
    }
    else {
        MYSQL_RES *result;
        Stock *stock;
        MYSQL_ROW row;
  
        result = mysql_store_result(connection);
        if( result == (MYSQL_RES *)NULL ) {
            error = mysql_error(connection);
            return (Stock *)NULL;
        }
        stock = (Stock *)malloc(sizeof(Stock));
        row = mysql_fetch_row(result);
        if( !row ) {
            error = "Invalid symbol.";
            return (Stock *)NULL;
        }
        stock->symbol = row[0];
        stock->open_price = atof(row[1]);
        stock->current_price = atof(row[2]);
        stock->high52 = atof(row[3]);
        stock->low52 = atof(row[4]);
        return stock;
    }
}

The first line of the function is where we prepare the query. It includes the entire query except for a %s placeholder for the ticker symbol. We will insert this into the string later using sprintf( ) .

The function next clears out the error message used by the get_error( ) helper function. Clearing out the error message is critical since MySQL actually manages the memory allocated to MySQL error messages. If this function failed to clear out the error message, and the client calling this API tried to call get_error( ) when no error occurred, it would be possible for the error message to point to garbage memory.

To make sure the final SQL has enough memory allocated to it, the function allocates enough space for the query with the placeholder, the ticker symbol, and a final null terminator. Using sprintf( ), the query is assembled into its final SQL.

In applications in which the stock symbol may come from data entered by a user, either your application or—perhaps more appropriately—the API should check the ticker symbol for validity. In other words, you should scan it for SQL, especially single quotes. If you fail to check for these things, users may inadvertently cause errors in the database calls or—even worse—exploit the error to corrupt the database.

Immediately after running the query, the API frees the memory allocated for the query and proceeds to error handling. As we illustrated earlier, you check the error on a query by checking its return value. If the return value is nonzero, an error occurred. This API sets the value of the current error message to whatever mysql_error( ) has set and returns NULL to the calling application.

On success, the function retrieves the results of the query. For this query, it is looking for one and only one result. The call to mysql_store_result( ) gets the result set from MySQL. The mysql_store_result( ) function returns NULL under two conditions:

Because we know we sent a query to the database, this situation can mean only that an error has occurred. We therefore set the error message and return null.

We are now ready to allocate a Stock struct and assign it values. This example actually does row processing in a different manner from what we did earlier. It specifically calls mysql_fetch_row( ) instead of directly accessing the data through the MYSQL_RES struct. Though both methods are proper, mysql_fetch_row( ) is technically more proper. It enables you to step through a result set row by row and get meta-data for the row. Though we care about neither issue in this case, we still use that function.

The mysql_fetch_row( ) function will return a MYSQL_ROW struct if there are more rows in the result set. For this query, there should be only one row. If the call to mysql_fetch_row( ) returns NULL, the symbol simply is not in the database. We therefore check that it is in the database and begin assigning values to the Stock struct based on the data in the result set.