Login | Register
My pages Projects Community openCollabNet

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Catacomb] dbms.c proposed changes



In reviewing the code for Catacomb, I realized that there is a (common) security problem with the dbms.c file. namely that using sprintf with strings to generate SQL queries can be broken by a well-designed value (For example, if you manage to pass a url that looked something like "'; drop database repos;" the query would result in a valid drop database statement being executed, quite deadly! The best solution is to use the string escaping mechanism provided by MySQL's C API, but it's a bit clunky to insert into the existing code.

This issue, coupled with my desire to connect Catacomb to other RDBMS's (particularly PostgreSQL) had me thinking last night about coming up with a more generic, JDBC-style interface. I've attached a first pass at a .h file for such a thing. I could probably have the code written for this by end-of-day today and all of the queries in dbms.c should be changed to use this mechanism. I'm sure there will be more functions (dbms_set_X) and wrappers for results retrieval.

Sound good?

Alternatives include libdbi.sourceforge.net (although it doesn't provide the automatic escaping of parameters and you can't pre-fill parameters, we might be able to influence the project to include such features) or possibly ODBC interfaces...
#ifndef DBMS_MYSQL_H
#define DBMS_MYSQL_H
#include <mysql.h>

/**
 * This piece of code provides a JDBC-like API for query execution. In
 * particular, it provides for parameter substitution (with parameter value
 * escaping.) This *may* actually improve performance if you have multiple
 * queries sharing some common parameters. An abstract example:
 *
 * dbms db;
 * dbms_query *q;
 * MYSQL_RES *res;
 *
 * if (!dbms_create(&db, "root", "root", "repos", null, 0)) exit(1);
 * q = dbms_prepare(&db,
 *   "select address from employee where name = ? and officenum = ?");
 * dbms_set_string(q, 1, "Chris Knight");
 * dbms_set_int(q, 2, 295);
 * res = dbms_execute(q);
 * if (!res) exit(2);
 * ... (use the result as you would normally)
 * dbms_set_string(q, 2, 101);
 * res = dbms_execute(q);
 * if (!res) exit(2);
 */

typedef struct {
	MYSQL mysql;
	apr_pool_t pool; /* for query composition */
} dbms;

typedef struct {
	dbms *db;
	int query_id;
} dbms_query;
/**
 * Initializes the database connection and memory pool. Returns true on
 * success.
 *
 * @see #dbms_destroy
 * @param host - The hostname to connect to. If null, then the UNIX-domain
 *   socket is used. (And port is useless.)
 * @param port - Specifies the port to connect to, or 0 if you would like
 *   to use the default port.
 */
int dbms_create(dbms *db, char *user, char *password, char *database,
	char *host, int port);

/**
 * Destroys the database connection for this database. Returns true on success.
 */
int dbms_destroy(dbms *db);

/**
 * Creates a new dbms_query object. Returns null when there is a failure.
 *
 * @param db - The database to perform the query on.
 * @param query - The query you would like to execute. Any parameters you will
 *   pass to this query should be denoted by "?" placeholders in the query
 *   string.  I.e. "select address from employee where name = ?"  Note you
 *   should *not* place quotes around placeholders you plan on replacing with
 *   strings.
 * @see #dbms_set_int
 */
dbms_query *dbms_prepare(dbms *db, char *query);

/**
 * Replaces a placeholder "?" with an integer value.
 *
 * @param query - The query that you would like to place the value in.
 * @param num - The number of the "?" to replace (the first is #1, etc.)
 * @param value - The value to place at that position.
 * @see #dbms_set_float
 * @see #dbms_set_string
 */
int dbms_set_int(dbms_query *query, int num, long value);

/**
 * Replaces a placeholder "?" with an floating-point value.
 *
 * @param query - The query that you would like to place the value in.
 * @param num - The number of the "?" to replace (the first is #1, etc.)
 * @param value - The value to place at that position.
 * @see #dbms_set_int
 * @see #dbms_set_string
 */
int dbms_set_float(dbms_query *query, int num, double value);

/**
 * Replaces a placeholder "?" with a string value. Note, this creates an
 * escaped version of this string in the dbms's memory pool.
 *
 * @param query - The query that you would like to place the value in.
 * @param num - The number of the "?" to replace (the first is #1, etc.)
 * @param value - The value to place at that position.
 * @see #dbms_set_int
 * @see #dbms_set_float
 */
int dbms_set_string(dbms_query *query, int num, char *value);

/**
 * Executes the query. Returns null if failure.
 */
MYSQL_RES *dbms_execute(dbms_query *query);

#endif DBMS_MYSQL_H