Re: Programmers toolkit - C api sprintf-like mysql_query()

On 7/2/2017 12:26 PM, Lew Pitcher wrote:
> I've recently taken up writing some C code to access mysql databases. One
> tedious part of that process is the preparation of query statements (not
> "prepared statement" queries, however) using program-supplied values.
>
> Typically, to me, that means using sprintf() on a query string like
> "INSERT name, number VALUES ('%s',%d) INTO table"
> to plug in the program-supplied values, then executing mysql_query() on the
> resulting string to actually perform the query.
>
> Yes, I know that I could use a "prepared statement", but for the code I'm
> writing, that would be overkill. However, the sprintf()/mysql_query()
> sequence occurs often enough (and there doesn't seem to be an equivalent
> mysql_*() api) that I encapsulated it into a single function.
>
> So, for your enjoyment, edification, and use, I give you
> formatted_mysql_query().
>
> ---- cut --------
> /*
> ** this code is released into the public domain
> ** Lew Pitcher (lew.pitcher_at_digitalfreehold.ca) 2017-07-02
> */
>
> #include <mysql/my_global.h>
> #include <mysql/mysql.h>
>
> #include <stdio.h>
> #include <stdlib.h>
> #include <stdarg.h>
>
> int formatted_mysql_query(MYSQL *dbm_handle, char *fquery, ...)
> {
> int retcode = 0;
>
> va_list ap, /* our va_list */
> pp; /* vsnprintf va_list */
> size_t querysize;
> char dummy[1],
> *query = NULL;
>
> va_start(ap,fquery);
>
> va_copy(pp,ap);
> querysize = 2 + vsnprintf(dummy,sizeof(dummy),fquery,pp);
>
> if (query = malloc(querysize))
> {
> va_copy(pp,ap);
> vsnprintf(query,querysize,fquery,pp);
>
> retcode = mysql_query(dbm_handle,query);
> free(query);
> }
> else retcode = -1; /* NOT a mysql error code */
>
> va_end(ap);
>
> return retcode;
> }
>
> ---- cut --------
>
> formatted_mysql_query() inserts user-specified values into an SQL query,
> then executes the resulting query via mysql_query()
>
> Accepts: MYSQL * points to mysql dbm handle
> char * points to sprintf-formatted query
> ... arguments as required to satisfy the
> the sprintf-formatted query string
>
> Updates: MYSQL * dbm_handle is updated with query
> results (on success)
> database tables as required
>
> Returns: (int) -1 indicating malloc() failure
> (int) 0 indicating mysql_query() success
> (int) other indicating mysql_query() error
>
> Example usage:
> int rc;
>
> char *name = "the name"
> int number = 6;
>
> MYSQL *dbm_handle;
>
> rc = formatted_mysql_query(dbm_handle,
> "INSERT nam,num VALUES ('%s',%d) INTO table",
> name,number);
> switch (rc)
> {
> case -1:
> puts("query failed - malloc() failure");
> break;
> case 0:
> puts("query succeeded")
> break;
> default:
> printf("query failed - mysql failure %d",rc);
> break;
> }
>
>
> I hope this makes someone's life easier.
>

[Quoted] Ensure you call mysql_real_escape_string() for all non-numeric values that come from external sources. Failure to do so can lead to a SQL injection attack.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================