About single row query results with parameterized querying

Question

Soo, I'm having a few places in my program where I just fetch a single row (sometimes even a single column in a single row), for example

SELECT id,title,posted FROM posts WHERE id=4;

Since this will only return a single row (unless I suck at database stuff and somehow manage to have duplicated IDs) I feel it's overkill to go the whole sqlite3_prepare_v2(...), sqlite3_bind_int(...), sqlite3_step(...), sqlite3_finalize(...) way just to fetch the single row.

I know this can be done with sqlite3_exec(...) and using a callback function to process the results that also return 1;'s to abort further executions, but this doesn't work in my case because my query looks like this:

SELECT id,title,posted FROM posts WHERE id=?;

And you can't have parameterized queries with sqlite3_exec(...). Inserting the ID via some kind of printf(...) isn't safe here either.

So, this is what I'm doing right now

char *sql;
sqlite3_stmt *stmt;

sql = sqlite3_mprintf(
       "SELECT \
          title, \
          ((julianday(posted) - 2440587.5) * 86400), \
          text \
        FROM \
           %s \
        WHERE id=?;",
     POSTS_TABLE); /* printf safe here, POSTS_TABLE is a #define */

if (sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL) != SQLITE_OK)
   show_sqlite_error(db);
else
{
   sqlite3_bind_text(stmt, 1, id, strlen(id), SQLITE_TRANSIENT);
   sqlite_code = sqlite3_step(stmt); /* This right here seems just ugly */

   if (sqlite_code != SQLITE_ROW) /* especially this */
      printf("<div id=\"error\">ERROR: No such ID in database</div>\n");
   else
   {
      int i;
      char time_string[25];

      const unsigned char *title = sqlite3_column_text(stmt, 0);
      time_t time = sqlite3_column_int(stmt, 1);
      const unsigned char *text  = sqlite3_column_text(stmt, 2);

      strftime(time_string, 25, DATE_FORMAT, gmtime(&time));

      printf("<h2 id=\"post_headline\">%s</h2>\n\n", title);
      printf("<h3 id=\"post_time\">%s</h3>\n", time_string);
      printf("<p id=\"post_body\">\n");

      for (i = 0; i < strlen((char *)text); i++)
         if (text[i] == '\n')
            printf("<br />");
         else
            printf("%c", text[i]);
   }
}

My issue with this code is, that I call sqlite3_step() once, error out if everything but SQLITE_ROW was returned, display whatever needs to be displays if SQLITE_ROW was returned, and call finalize without actually stepping to SQLITE_DONE. While this (as I was told) wouldn't do any harm with a SELECT statement, it feels ugly and overkill.

Another thing is queries which simply return a single value.

SELECT MAX(id) FROM posts;

In my case, this simply returns "5". No more, no less. Especially here it's really overkill to go the whole prepare, bind, step, finalize way, but I HAVE to do it that way, because my query again looks like

SELECT MIN(id) FROM posts WHERE id > ?;

to get the next ID after the current one for example. It just returns "5" when the current ID is "4".

To sum this up: is there a quick way to grab single row results from a SELECT statement without explicitly looping until SQLITE_DONE is received from sqlite3_step() AND how do I grab single row, single column value (skalar basically) without going the whole way again, including a call to sqlite_column_...?

Answer 1

I'm an advocate of preventing SQL injection, but I think it should be safe to do this:

sql = sqlite3_mprintf("SELECT ... WHERE id=%d;", atoi(id));

This interprets the id string as an integer via atoi(), so there's no chance of SQL injection.

As for fetching a single row or a single scalar, I'd write a wrapper function that executes the SQL and returns the contents simply. You may have to use a global to transfer the contents from the callback function to the wrapper function.