/* * cmd_escapestring: certain strings sent to a database should be properly * escaped -- for instance, quotes need to be escaped to insure that * a query string is properly formatted. cmd_escapestring does whatever * is necessary to escape the special characters in a string. * * Inputs: * cmd->argv[0]: connection name * cmd->argv[1]: string to escape * * Returns: * this command CANNOT fail. The return string is null-terminated and * stored in the data field of the modret_t structure. * * Notes: * Different languages may escape different characters in different ways. * A backend should handle this correctly, where possible. If there is * no client library function to do the string conversion, it is strongly * recommended that the backend module writer do whatever is necessry (read * the database documentation and figure it out) to do the conversion * themselves in this function. * * A backend MUST supply a working escapestring implementation. Simply * copying the data from argv[0] into the data field of the modret allows * for possible SQL injection attacks when this backend is used. */ MODRET cmd_escapestring(cmd_rec * cmd) { conn_entry_t *entry = NULL; db_conn_t *conn = NULL; modret_t *cmr = NULL; char *unescaped = NULL; char *escaped = NULL; cmd_rec *close_cmd; size_t unescaped_len = 0; #ifdef HAVE_POSTGRES_PQESCAPESTRINGCONN int pgerr = 0; #endif sql_log(DEBUG_FUNC, "%s", "entering \tpostgres cmd_escapestring"); _sql_check_cmd(cmd, "cmd_escapestring"); if (cmd->argc != 2) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_escapestring"); return PR_ERROR_MSG(cmd, MOD_SQL_POSTGRES_VERSION, "badly formed request"); } /* get the named connection */ entry = _sql_get_connection(cmd->argv[0]); if (!entry) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_escapestring"); return PR_ERROR_MSG(cmd, MOD_SQL_POSTGRES_VERSION, "unknown named connection"); } conn = (db_conn_t *) entry->data; /* Make sure the connection is open. */ cmr = cmd_open(cmd); if (MODRET_ERROR(cmr)) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_escapestring"); return cmr; } unescaped = cmd->argv[1]; unescaped_len = strlen(unescaped); escaped = (char *) pcalloc(cmd->tmp_pool, sizeof(char) * (unescaped_len * 2) + 1); #ifdef HAVE_POSTGRES_PQESCAPESTRINGCONN PQescapeStringConn(conn->postgres, escaped, unescaped, unescaped_len, &pgerr); if (pgerr != 0) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_escapestring"); return _build_error(cmd, conn); } #else PQescapeString(escaped, unescaped, unescaped_len); #endif close_cmd = _sql_make_cmd(cmd->tmp_pool, 1, entry->name); cmd_close(close_cmd); SQL_FREE_CMD(close_cmd); sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_escapestring"); return mod_create_data(cmd, (void *) escaped); }
/* * cmd_exit: closes all open connections. * * Inputs: * None * * Returns: * A simple non-error modret_t. */ static modret_t *cmd_exit(cmd_rec *cmd) { register unsigned int i = 0; sql_log(DEBUG_FUNC, "%s", "entering \tpostgres cmd_exit"); for (i = 0; i < conn_cache->nelts; i++) { conn_entry_t *entry = ((conn_entry_t **) conn_cache->elts)[i]; if (entry->connections > 0) { cmd_rec *close_cmd = _sql_make_cmd(conn_pool, 2, entry->name, "1"); cmd_close(close_cmd); destroy_pool(close_cmd->pool); } } sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_exit"); return PR_HANDLED(cmd); }
/* * sql_timer_cb: when a timer goes off, this is the function that gets called. * This function makes assumptions about the db_conn_t members. */ static int sql_timer_cb(CALLBACK_FRAME) { conn_entry_t *entry = NULL; int i = 0; cmd_rec *cmd = NULL; for (i = 0; i < conn_cache->nelts; i++) { entry = ((conn_entry_t **) conn_cache->elts)[i]; if (entry->timer == p2) { sql_log(DEBUG_INFO, "timer expired for connection '%s'", entry->name); cmd = _sql_make_cmd( conn_pool, 2, entry->name, "1" ); cmd_close( cmd ); SQL_FREE_CMD(cmd); entry->timer = 0; } } return 0; }
/* * cmd_insert: executes an INSERT query, properly constructing the query * based on the inputs. * * cmd_insert takes either exactly two inputs, or exactly four. If only * two inputs are given, the second is a monolithic query string. See * the examples below. * * Inputs: * cmd->argv[0]: connection name * cmd->argv[1]: table * cmd->argv[2]: field string * cmd->argv[3]: value string * * Returns: * either a properly filled error modret_t if the insert failed, or a * simple non-error modret_t. * * Example: * These are example queries that would be executed for Postgres; other * backends will have different SQL syntax. * * argv[] = "default","log","userid, date, count", "'aah', now(), 2" * query = "INSERT INTO log (userid, date, count) VALUES ('aah', now(), 2)" * * argv[] = "default"," INTO foo VALUES ('do','re','mi','fa')" * query = "INSERT INTO foo VALUES ('do','re','mi','fa')" * * Notes: * none */ MODRET cmd_insert(cmd_rec *cmd) { conn_entry_t *entry = NULL; db_conn_t *conn = NULL; modret_t *cmr = NULL; modret_t *dmr = NULL; char *query = NULL; cmd_rec *close_cmd; sql_log(DEBUG_FUNC, "%s", "entering \tpostgres cmd_insert"); _sql_check_cmd(cmd, "cmd_insert"); if ((cmd->argc != 2) && (cmd->argc != 4)) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_insert"); return PR_ERROR_MSG(cmd, MOD_SQL_POSTGRES_VERSION, "badly formed request"); } /* get the named connection */ entry = _sql_get_connection(cmd->argv[0]); if (!entry) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_insert"); return PR_ERROR_MSG(cmd, MOD_SQL_POSTGRES_VERSION, "unknown named connection"); } conn = (db_conn_t *) entry->data; cmr = cmd_open(cmd); if (MODRET_ERROR(cmr)) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_insert"); return cmr; } /* construct the query string */ if (cmd->argc == 2) { query = pstrcat(cmd->tmp_pool, "INSERT ", cmd->argv[1], NULL); } else { query = pstrcat( cmd->tmp_pool, "INSERT INTO ", cmd->argv[1], " (", cmd->argv[2], ") VALUES (", cmd->argv[3], ")", NULL ); } /* log the query string */ sql_log(DEBUG_INFO, "query \"%s\"", query); /* perform the query. if it doesn't work, log the error, close the * connection then return the error from the query processing. */ if (!(conn->result = PQexec(conn->postgres, query)) || (PQresultStatus(conn->result) != PGRES_COMMAND_OK)) { dmr = _build_error( cmd, conn ); if (conn->result) PQclear(conn->result); close_cmd = _sql_make_cmd( cmd->tmp_pool, 1, entry->name ); cmd_close(close_cmd); SQL_FREE_CMD(close_cmd); sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_insert"); return dmr; } PQclear(conn->result); /* close the connection and return HANDLED. */ close_cmd = _sql_make_cmd( cmd->tmp_pool, 1, entry->name ); cmd_close(close_cmd); SQL_FREE_CMD(close_cmd); sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_insert"); return PR_HANDLED(cmd); }
/* * cmd_select: executes a SELECT query. properly constructing the query * based on the inputs. See mod_sql.h for the definition of the _sql_data * structure which is used to return the result data. * * cmd_select takes either exactly two inputs, or more than two. If only * two inputs are given, the second is a monolithic query string. See * the examples below. * * Inputs: * cmd->argv[0]: connection name * cmd->argv[1]: table * cmd->argv[2]: select string * Optional: * cmd->argv[3]: where clause * cmd->argv[4]: requested number of return rows (LIMIT) * * etc. : other options, such as "GROUP BY", "ORDER BY", * and "DISTINCT" will start at cmd->arg[5]. All * backends MUST support 'DISTINCT', the other * arguments are optional (but encouraged). * * Returns: * either a properly filled error modret_t if the select failed, or a * modret_t with the result data filled in. * * Example: * These are example queries that would be executed for Postgres; other * backends will have different SQL syntax. * * argv[] = "default","user","userid, count", "userid='aah'","2" * query = "SELECT userid, count FROM user WHERE userid='aah' LIMIT 2" * * argv[] = "default","usr1, usr2","usr1.foo, usr2.bar" * query = "SELECT usr1.foo, usr2.bar FROM usr1, usr2" * * argv[] = "default","usr1","foo",,,"DISTINCT" * query = "SELECT DISTINCT foo FROM usr1" * * argv[] = "default","bar FROM usr1 WHERE tmp=1 ORDER BY bar" * query = "SELECT bar FROM usr1 WHERE tmp=1 ORDER BY bar" * * Notes: * certain selects could return huge amounts of data. do whatever is * possible to minimize the amount of data copying here. */ MODRET cmd_select(cmd_rec *cmd) { conn_entry_t *entry = NULL; db_conn_t *conn = NULL; modret_t *cmr = NULL; modret_t *dmr = NULL; char *query = NULL; int cnt = 0; cmd_rec *close_cmd; sql_log(DEBUG_FUNC, "%s", "entering \tpostgres cmd_select"); _sql_check_cmd(cmd, "cmd_select"); if (cmd->argc < 2) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_select"); return PR_ERROR_MSG(cmd, MOD_SQL_POSTGRES_VERSION, "badly formed request"); } /* get the named connection */ entry = _sql_get_connection(cmd->argv[0]); if (!entry) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_select"); return PR_ERROR_MSG(cmd, MOD_SQL_POSTGRES_VERSION, "unknown named connection"); } conn = (db_conn_t *) entry->data; cmr = cmd_open(cmd); if (MODRET_ERROR(cmr)) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_select"); return cmr; } /* construct the query string */ if (cmd->argc == 2) { query = pstrcat(cmd->tmp_pool, "SELECT ", cmd->argv[1], NULL); } else { query = pstrcat( cmd->tmp_pool, cmd->argv[2], " FROM ", cmd->argv[1], NULL ); if ((cmd->argc > 3) && (cmd->argv[3])) query = pstrcat( cmd->tmp_pool, query, " WHERE ", cmd->argv[3], NULL ); if ((cmd->argc > 4) && (cmd->argv[4])) query = pstrcat( cmd->tmp_pool, query, " LIMIT ", cmd->argv[4], NULL ); if (cmd->argc > 5) { /* handle the optional arguments -- they're rare, so in this case * we'll play with the already constructed query string, but in * general we should probably take optional arguments into account * and put the query string together later once we know what they are. */ for (cnt=5; cnt < cmd->argc; cnt++) { if ((cmd->argv[cnt]) && !strcasecmp("DISTINCT",cmd->argv[cnt])) { query = pstrcat( cmd->tmp_pool, "DISTINCT ", query, NULL); } } } query = pstrcat( cmd->tmp_pool, "SELECT ", query, NULL); } /* log the query string */ sql_log(DEBUG_INFO, "query \"%s\"", query); /* perform the query. if it doesn't work, log the error, close the * connection then return the error from the query processing. */ if (!(conn->result = PQexec(conn->postgres, query)) || (PQresultStatus(conn->result) != PGRES_TUPLES_OK)) { dmr = _build_error( cmd, conn ); if (conn->result) PQclear(conn->result); close_cmd = _sql_make_cmd( cmd->tmp_pool, 1, entry->name ); cmd_close(close_cmd); SQL_FREE_CMD(close_cmd); sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_select"); return dmr; } /* get the data. if it doesn't work, log the error, close the * connection then return the error from the data processing. */ dmr = _build_data( cmd, conn ); PQclear(conn->result); if (MODRET_ERROR(dmr)) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_select"); close_cmd = _sql_make_cmd( cmd->tmp_pool, 1, entry->name ); cmd_close(close_cmd); SQL_FREE_CMD(close_cmd); return dmr; } /* close the connection, return the data. */ close_cmd = _sql_make_cmd( cmd->tmp_pool, 1, entry->name ); cmd_close(close_cmd); SQL_FREE_CMD(close_cmd); sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_select"); return dmr; }
/* * cmd_query: executes a freeform query string, with no syntax checking. * * cmd_query takes exactly two inputs, the connection and the query string. * * Inputs: * cmd->argv[0]: connection name * cmd->argv[1]: query string * * Returns: * depending on the query type, returns a modret_t with data, a non-error * modret_t, or a properly filled error modret_t if the query failed. * * Example: * None. The query should be passed directly to the backend database. * * Notes: * None. */ MODRET cmd_query(cmd_rec *cmd) { conn_entry_t *entry = NULL; db_conn_t *conn = NULL; modret_t *cmr = NULL; modret_t *dmr = NULL; char *query = NULL; cmd_rec *close_cmd; sql_log(DEBUG_FUNC, "%s", "entering \tpostgres cmd_query"); _sql_check_cmd(cmd, "cmd_query"); if (cmd->argc != 2) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_query"); return ERROR_MSG(cmd, MOD_SQL_POSTGRES_VERSION, "badly formed request"); } /* get the named connection */ entry = _sql_get_connection(cmd->argv[0]); if (!entry) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_query"); return ERROR_MSG(cmd, MOD_SQL_POSTGRES_VERSION, "unknown named connection"); } conn = (db_conn_t *) entry->data; cmr = cmd_open(cmd); if (MODRET_ERROR(cmr)) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_query"); return cmr; } query = pstrcat(cmd->tmp_pool, cmd->argv[1], NULL); /* log the query string */ sql_log( DEBUG_INFO, "query \"%s\"", query); /* perform the query. if it doesn't work, log the error, close the * connection then return the error from the query processing. */ if (!(conn->result = PQexec(conn->postgres, query)) || ((PQresultStatus(conn->result) != PGRES_TUPLES_OK) && (PQresultStatus(conn->result) != PGRES_COMMAND_OK))) { dmr = _build_error( cmd, conn ); if (conn->result) PQclear(conn->result); close_cmd = _sql_make_cmd( cmd->tmp_pool, 1, entry->name ); cmd_close(close_cmd); SQL_FREE_CMD(close_cmd); sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_select"); return dmr; } /* get data if necessary. if it doesn't work, log the error, close the * connection then return the error from the data processing. */ if ( PQresultStatus( conn->result ) == PGRES_TUPLES_OK ) { dmr = _build_data( cmd, conn ); PQclear(conn->result); if (MODRET_ERROR(dmr)) { sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_query"); } } else { dmr = HANDLED(cmd); } /* close the connection, return the data. */ close_cmd = _sql_make_cmd( cmd->tmp_pool, 1, entry->name ); cmd_close(close_cmd); SQL_FREE_CMD(close_cmd); sql_log(DEBUG_FUNC, "%s", "exiting \tpostgres cmd_query"); return dmr; }