Finally, I managed to get my hands on that code :-)
Gordon Messmer wrote:
Alessandro Vesely wrote:
Indeed, to use prepared statements would be itself a good reason to review
that code. It was added in MySQL 4.1.2, the auth code still has a conditional
part for older MySQL versions. Should I assume we should continue supporting
them, and use that statement only for recent versions?
I'm not sure what "older" versions means, specifically.
There are blocks such as
#if MYSQL_VERSION_ID >= 32200
for mysql_connect vs mysql_real_connect and ssl usage, if configured.
Prepared
statements have been supported by MySQL since the production release of
4.1 in late 2004. Support for the 4.1 release ended 15 months ago. I
don't think it's appropriate to support anything older than that, and if
supporting 4.1 means a less secure or less maintainable driver, I don't
see why you'd go out of your way to support even that.
I don't think it's feasible to _replace_ current direct queries with
prepared ones, because of user supplied select clauses. They will
either work with direct queries or with prepared ones, not both.
Synthesized queries can still fail, e.g. if the name field is '?'.
In the patch, prepared statements are attempted for the main SELECT
query, not password change nor enumeration. However, I also made a
number of changes:
* patch 2007-10-02 07:55:07 by Anton Dobkin moved at session
initialization;
* in auth_mysql_setpass() removed test against control chars in the
new password, and replaced ad hoc escape function;
* Change get_localpart();
* use mysql escape function also in a number of other places; the
MySQL team took years to get it straight...
With that, I could get a line of "Apr 16 20:49:58 north authdaemond:
Authenticated: sysusername=<null>, sysuserid=117, sysgroupid=117,
homedir=/mail/tana.it/hasthem, address="has \"the\" quotes"@tana.it,
fullname=<null>, maildir=<null>, quota=<null>,
options=disableimap=0,disablepop3=0,disablewebmail=0,sharedgroup="
For non-prepared queries, the patched program produces a snippet like
WHERE addr = '\"has \\\"the\\\" quotes\"@tana.it'
while the old get_localpart() only copies the inner quotes, unescaped.
Without user supplied select clause, and no funny fields, I saw a
performance gain of about 50%, as the average time per query dropped
from 333~345 to 143~154 usecs on an idle new server I'm setting up.
However, queries are not cached for prepared statements, and MySQL
docs suggest to experiment both methods. To select different users for
each call I used a slightly modified version of authtest that I just
copied to http://www.tana.it/sw/courier-utils/testmailuser.c
--- authmysqllib.original.c 2007-10-07 04:50:45.000000000 +0200
+++ authmysqllib.c 2008-04-17 18:42:31.000000000 +0200
@@ -110,6 +110,61 @@
static MYSQL *mysql=0;
+static void set_session_options(void)
+/*
+* session variables can be set once for the whole session
+*/
+{
+/* Anton Dobkin <[EMAIL PROTECTED]>, VIAN, Ltd. */
+#if MYSQL_VERSION_ID >= 41000
+ const char *character_set=read_env("MYSQL_CHARACTER_SET");
+
+ if(character_set){
+#if MYSQL_VERSION_ID >= 41013
+ /*
+ * This function works like the SET NAMES statement, but also sets
+ * the value of mysql->charset, and thus affects the character set
+ * used by mysql_real_escape_string()
+ */
+ if (!mysql_set_character_set(mysql, character_set))
+ {
+ err("Cannot set MySQL character set \"%s\", working with
\"%s\"\n",
+ character_set,
+ mysql_character_set_name(mysql));
+ }
+ else
+ {
+ DPRINTF("Install of a character set for MySQL: %s",
character_set);
+ }
+#else
+ char *character_set_buf;
+
+ character_set_buf=malloc(strlen(character_set)+11);
+
+ if (!character_set_buf)
+ {
+ perror("malloc");
+ return (0);
+ }
+
+ strcpy(character_set_buf, "SET NAMES ");
+ strcat(character_set_buf, character_set);
+
+ DPRINTF("Install of a character set for MySQL. SQL query: SET
NAMES %s", character_set);
+
+ if(mysql_query (mysql, character_set_buf))
+ {
+ err("Install of a character set for MySQL is failed: %s
MYSQL_CHARACTER_SET: may be invalid character set", mysql_error(mysql));
+ }
+
+ free(character_set_buf);
+#endif /* 41013 */
+ }
+#endif /* 41000 */
+}
+
+
+
static int do_connect()
{
const char *server;
@@ -138,10 +193,8 @@
time(&t_check);
- if (t_check < last_time)
- last_time=t_check; /* System clock changed */
-
- if (t_check < last_time + 60)
+ /* System clock changed? */
+ if (t_check >= last_time && t_check < last_time + 60)
return (0);
last_time=t_check;
@@ -149,8 +202,7 @@
if (mysql_ping(mysql) == 0) return (0);
DPRINTF("authmysqllib: mysql_ping failed, connection lost");
- mysql_close(mysql);
- mysql=0;
+ auth_mysql_cleanup();
}
server=read_env("MYSQL_SERVER");
@@ -236,13 +288,33 @@
mysql=0;
return (-1);
}
+
+ DPRINTF("authmysqllib: connected. Versions: "
+ "header %lu, "
+ "client %lu, "
+ "server %lu",
+ (long)MYSQL_VERSION_ID,
+ mysql_get_client_version(),
+ mysql_get_server_version(mysql));
+
+ set_session_options();
return (0);
}
+#if !defined MY_TARGET_STMT_VERSION
+#define MY_TARGET_STMT_VERSION 50032 /* probably can compile with 41020 */
+#endif
+#if MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+static void my_stmt_cleanup(void);
+#endif // MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+
void auth_mysql_cleanup()
{
if (mysql)
{
+#if MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+ my_stmt_cleanup();
+#endif // MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
mysql_close(mysql);
mysql=0;
}
@@ -274,13 +346,18 @@
static void append_username(char *p, const char *username,
const char *defdomain)
+/*
+* append user name and possibly defdomain to dest pointed by p.
+* p must have enough room for storing [EMAIL PROTECTED], i.e.
+* 2*( strlen(username) + strlen(defdomain) + 1 ) + 1
+*/
{
- for (strcpy(p, username); *p; p++)
- if (*p == '\'' || *p == '"' || *p == '\\' ||
- (int)(unsigned char)*p < ' ')
- *p=' '; /* No funny business */
+ p += mysql_real_escape_string(mysql, p, username, strlen(username));
if (strchr(username, '@') == 0 && defdomain && *defdomain)
- strcat(strcpy(p, "@"), defdomain);
+ {
+ *p++ ='@';
+ mysql_real_escape_string(mysql, p, defdomain,
strlen(defdomain));
+ }
}
/* [EMAIL PROTECTED] */
@@ -491,38 +568,27 @@
/* [EMAIL PROTECTED] */
static const char *get_localpart (const char *username)
{
-size_t lbuf = 0;
-const char *l_end, *p;
+size_t lbuf;
+const char *p;
char *q;
-static char localpart_buf[130];
+static char localpart_buf[2*130];
if (!username || *username == '\0') return NULL;
p = strchr(username,'@');
if (p)
{
- if ((p-username) > 128)
+ if ((lbuf = p-username) > 128)
return NULL;
- l_end = p;
}
else
{
if ((lbuf = strlen(username)) > 128)
return NULL;
- l_end = username + lbuf;
}
-
- p=username;
- q=localpart_buf;
- while (*p && p != l_end)
- if (*p == '\"' || *p == '\\' ||
- *p == '\'' || (int)(unsigned char)*p < ' ')
- p++;
- else
- *q++ = *p++;
-
- *q = '\0';
+ lbuf = mysql_real_escape_string(mysql, localpart_buf, username, lbuf);
+ localpart_buf[lbuf] = 0;
return localpart_buf;
}
@@ -567,35 +633,29 @@
static char pass_buf[2][540]; /* Use two buffers, see parse_chpass_clause */
static int next_pass=0;
const char *p;
-char *q, *endq;
+char *q;
+unsigned passlen, esclen;
- if (!password || *password == '\0' || (strlen(password)) > 256)
+ if (!password || *password == '\0' || (passlen=strlen(password)) > 256U)
return NULL;
next_pass= 1-next_pass;
p = password;
q = pass_buf[next_pass];
- endq = q + sizeof pass_buf[next_pass];
-
- while (*p && q < endq)
- {
- if (*p == '\"' || *p == '\\' || *p == '\'')
- *q++ = '\\';
- *q++ = *p++;
- }
- if (q >= endq)
+ esclen = mysql_real_escape_string(mysql, q, p, passlen);
+ if (esclen >= sizeof pass_buf[next_pass])
return NULL;
- *q = '\0';
+ q[esclen] = '\0';
return pass_buf[next_pass];
}
/* [EMAIL PROTECTED] */
-static char *parse_select_clause (const char *clause, const char *username,
- const char *defdomain,
- const char *service)
+static char *
+parse_select_clause_direct (const char *clause, const char *username,
+ const char *domain, const char *service)
{
static struct var_data vd[]={
{"local_part", NULL, sizeof("local_part"), 0},
@@ -603,17 +663,29 @@
{"service", NULL, sizeof("service"), 0},
{NULL, NULL, 0, 0}};
+ vd[0].value = username;
+ vd[1].value = domain;
+ vd[2].value = service;
+
+ return (parse_string (clause, vd));
+}
+
+/* [EMAIL PROTECTED] */
+static char *parse_select_clause (const char *clause, const char *username,
+ const char *defdomain,
+ const char *service)
+{
+ char const *p0, *p1;
if (clause == NULL || *clause == '\0' ||
!username || *username == '\0')
return NULL;
- vd[0].value = get_localpart (username);
- vd[1].value = get_domain (username, defdomain);
- if (!vd[0].value || !vd[1].value)
+ p0 = get_localpart (username);
+ p1 = get_domain (username, defdomain);
+ if (!p0 || !p1)
return NULL;
- vd[2].value = service;
- return (parse_string (clause, vd));
+ return (parse_select_clause_direct (clause, p0, p1, service));
}
/* [EMAIL PROTECTED] */
@@ -644,10 +716,638 @@
return (parse_string (clause, vd));
}
+#if MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+
+typedef enum { stmt_default = 0, stmt_never, stmt_always} stmt_status;
+
+#define MAX_STMT_EXPECTED_RESULT 10
+#define MAX_STMT_PREPARED_PARAMS 20
+static struct my_stmt_vars
+{
+ MYSQL_STMT *handle;
+ MYSQL_BIND *param, *result;
+ unsigned param_size, result_size;
+ stmt_status status;
+ char order[MAX_STMT_PREPARED_PARAMS];
+} my_stmt;
+
+static void my_stmt_cleanup(void)
+{
+ if (my_stmt.handle)
+ mysql_stmt_close(my_stmt.handle);
+ free(my_stmt.param);
+ free(my_stmt.result);
+ memset(&my_stmt, 0, sizeof my_stmt);
+}
+
+#define MAX_STMT_STRING_LENGTH 512
+
+/*
+* buffer for passing parameters to prepared statemets
+*/
+static struct my_query_param_buf_vars
+{
+ char string_buf[MAX_STMT_STRING_LENGTH];
+ unsigned long string_len[MAX_STMT_PREPARED_PARAMS];
+ my_bool is_null[MAX_STMT_PREPARED_PARAMS];
+} my_query_param_buf;
+
+/*
+* buffer for fetching prepared statemets results
+*/
+static struct my_query_buf_vars
+{
+ unsigned long long numeric[2];
+ unsigned long string_len[MAX_STMT_EXPECTED_RESULT];
+ my_bool is_null[MAX_STMT_EXPECTED_RESULT];
+ // n.u. my_bool error[MAX_STMT_EXPECTED_RESULT];
+} my_query_buf;
+
+static char const *explicit_mysql_stmt_error(void)
+{
+ char const *p = mysql_stmt_error(my_stmt.handle);
+ if (p == NULL || *p == 0)
+ p = "(no error)";
+ return p;
+}
+
+static int my_stmt_numeric_field(MYSQL_FIELD *field, unsigned count)
+{
+ int is_num = IS_NUM(field->type);
+ MYSQL_BIND *result = &my_stmt.result[count];
+
+ DPRINTF("MySQL: field %u: %s %s", count,
+ *field->name? field->name: "(empty)",
+ is_num? "OK": "warning: is not numeric");
+
+ result->buffer_type = MYSQL_TYPE_LONGLONG;
+ result->buffer = &my_query_buf.numeric[count != 3]; // 3 = uid, or gid
+ result->is_unsigned = 1;
+ result->is_null = &my_query_buf.is_null[count];
+ // n.u. result->error = &my_query_buf.error[count];
+ return 0;
+}
+
+static int my_stmt_string_field(MYSQL_FIELD *field, unsigned count)
+{
+ int const is_string = field->type == MYSQL_TYPE_STRING ||
+ field->type == MYSQL_TYPE_VAR_STRING;
+ MYSQL_BIND *result = &my_stmt.result[count];
+
+ DPRINTF("MySQL: field %u: %s, length %lu: %s", count,
+ *field->name? field->name: "(empty)",
+ field->length,
+ is_string? "OK":
+ field->type == MYSQL_TYPE_NULL? "is NULL":
+ "warning: is not [var]char");
+
+ result->buffer_type = MYSQL_TYPE_STRING;
+ result->buffer = NULL;
+ result->buffer_length = 0;
+ result->length = &my_query_buf.string_len[count];
+ result->is_null = &my_query_buf.is_null[count];
+ // n.u. result->error = &my_query_buf.error[count];
+ return 0;
+}
+
+static int my_stmt_prepare(char const *querybuf, char const *param_order)
+/*
+* try and prepare a statement that can then be executed.
+*
+* NOTE: the prepare once, execute many times strategy does not
+* work for every possible query. Limitations include string values
+* that specify table or field names, as well as binary operators
+* such as an equality test like "? = ?" that don't permit to identify
+* the types of the operands.
+*
+* The point is to identify incompatible queries so that traditional
+* queries can be done with them. Users shall configure accordingly.
+*
+* return values:
+* -1 hard error, general failure;
+* 0 ok, prepared statement ready to be executed;
+* 1 possibly unsupported query.
+*/
+{
+ int rc = -1, retry;
+ unsigned qblen, count;
+ char const *fail = NULL;
+ MYSQL_RES *res = NULL;
+
+ if (querybuf == NULL)
+ return -1;
+
+ qblen = strlen(querybuf);
+ DPRINTF("MySQL: prepare query: %s", querybuf);
+
+ /* initialize statement, try twice */
+ for (retry = 0; ; ++retry)
+ {
+ if ((my_stmt.handle = mysql_stmt_init(mysql)) == NULL)
+ {
+ err("MySQL out of memory: cannot allocate statement");
+ return -1;
+ }
+
+ rc = mysql_stmt_prepare(my_stmt.handle, querybuf, qblen);
+ if (rc == 0)
+ break;
+
+ if (retry == 0)
+ {
+ auth_mysql_cleanup();
+ if (do_connect())
+ return -1;
+ }
+ else
+ {
+ fail = "prepare";
+ rc = 1;
+ goto error_exit;
+ }
+ }
+
+ /* prepare results */
+
+ res = mysql_stmt_result_metadata(my_stmt.handle);
+ if (res == NULL)
+ {
+ fail = "get metadata";
+ rc = -1;
+ goto error_exit;
+ }
+
+ my_stmt.result_size = mysql_num_fields(res);
+ if (my_stmt.result_size < 6)
+ {
+ err("MySQL: found %u fields, required at least 6",
+ my_stmt.result_size);
+ fail = "find fields";
+ rc = -1;
+ goto error_exit;
+ }
+ else if (my_stmt.result_size > MAX_STMT_EXPECTED_RESULT)
+ {
+ err("MySQL: found %u fields, can support max %u",
+ my_stmt.result_size, MAX_STMT_EXPECTED_RESULT);
+ fail = "handle fields";
+ rc = 1; // the traditional query supports this
+ goto error_exit;
+ }
+
+ my_stmt.result = (MYSQL_BIND*)
+ calloc(my_stmt.result_size, sizeof(MYSQL_BIND));
+ if (my_stmt.result == NULL)
+ {
+ fail = "alloc";
+ rc = -1;
+ goto error_exit;
+ }
+
+ for (count = 0; count < my_stmt.result_size; ++count)
+ {
+ MYSQL_FIELD *field = mysql_fetch_field_direct(res, count);
+ if (field == NULL)
+ {
+ fail = "examine field";
+ rc = -1;
+ goto error_exit;
+ }
+ switch (count)
+ {
+ case 0: /* username */
+ case 1: /* cryptpw */
+ case 2: /* clearpw */
+ rc = my_stmt_string_field(field, count);
+ break;
+ case 3: /* uid */
+ case 4: /* gid */
+ rc = my_stmt_numeric_field(field, count);
+ break;
+ case 5: /* home */
+ case 6: /* maildir */
+ case 7: /* quota */
+ case 8: /* fullname */
+ case 9: /* options */
+ rc = my_stmt_string_field(field, count);
+ break;
+ default:
+ rc = -1;
+ break;
+ }
+ if (rc != 0)
+ {
+ fail = "examine field";
+ rc = -1;
+ goto error_exit;
+ }
+ }
+ mysql_free_result(res);
+ res = NULL;
+
+ /* prepare parameters */
+
+ my_stmt.param_size = mysql_stmt_param_count(my_stmt.handle);
+ if (my_stmt.param_size >= MAX_STMT_PREPARED_PARAMS)
+ {
+ fail = "too many parameters";
+ rc = 1;
+ goto error_exit;
+ }
+
+ my_stmt.param = (MYSQL_BIND*)
+ calloc(my_stmt.param_size, sizeof(MYSQL_BIND));
+ if (my_stmt.param == NULL)
+ {
+ fail = "alloc param";
+ rc = -1;
+ goto error_exit;
+ }
+
+ if (param_order)
+ {
+ char const *p = param_order;
+ unsigned count = 0;
+
+ while ((p = strchr(p, '?')) != NULL &&
+ count < MAX_STMT_PREPARED_PARAMS)
+ {
+ int u = *(unsigned char*)++p - '0';
+ if (u < 0 || u >= 3)
+ break;
+ my_stmt.order[count++] = u;
+ }
+
+ if (p || count != my_stmt.param_size)
+ {
+ if (p == NULL && count > my_stmt.param_size)
+ fail = "read parameters inside quotes";
+ else
+ fail = "foobar";
+ rc = 1;
+ goto error_exit;
+ }
+ }
+ else
+ {
+ my_stmt.order[0] = 'u';
+ }
+
+ return 0;
+
+error_exit:
+ err("MySQL prepare: %s: cannot %s: %s",
+ rc < 0? "fatal": rc > 0? "warning": "foobar",
+ fail? fail: "work",
+ explicit_mysql_stmt_error());
+ if (res)
+ mysql_free_result(res);
+ my_stmt_cleanup();
+ my_stmt.status = stmt_never;
+ return rc;
+}
+
+static void my_stmt_string_param(int count, char const *buffer, unsigned
buflen)
+{
+ MYSQL_BIND *param = &my_stmt.param[count];
+ param->buffer_type = MYSQL_TYPE_STRING;
+ param->buffer = (void*)buffer;
+ param->buffer_length = my_query_param_buf.string_len[count] = buflen;
+ param->length = &my_query_param_buf.string_len[count];
+ my_query_param_buf.is_null[count] = buffer == NULL;
+ param->is_null = &my_query_param_buf.is_null[count];
+}
+
+static int my_stmt_string_result(unsigned count, char **buffer)
+{
+ MYSQL_BIND *result = &my_stmt.result[count];
+ unsigned length;
+ int rc;
+
+ if (result == NULL || result->is_null == NULL ||
+ result->length == NULL ||
+ *result->length >= MAX_STMT_STRING_LENGTH)
+ return -1;
+
+ if (*result->is_null || (length = *result->length) == 0)
+ {
+ DPRINTF("MySQL: column %u: %s", count,
+ *result->is_null? "is NULL": "empty string");
+ return 0;
+ }
+
+ /* modify result, fetch the value, reset result */
+ length += 1;
+ if ((result->buffer = malloc(length)) == NULL)
+ return -1;
+
+ result->buffer_length = length;
+ rc = mysql_stmt_fetch_column(my_stmt.handle, result, count, 0);
+ *buffer = (char*)result->buffer;
+ result->buffer = NULL;
+ result->buffer_length = 0;
+ if (rc)
+ {
+ free(*buffer);
+ *buffer = NULL;
+ err("MySQL: cannot fetch column %u: %s", count,
+ explicit_mysql_stmt_error());
+ return -1;
+ }
+
+ DPRINTF("MySQL: column %u: %s", count,
+ count >= 2 && count <= 3 && courier_authdebug_login_level < 2?
+ "*****": *buffer);
+ return 0;
+}
+
+static int my_stmt_numeric_result(unsigned count)
+{
+ MYSQL_BIND *result = &my_stmt.result[count];
+
+ if (result == NULL || result->is_null == NULL ||
+ (count != 3 && count != 4))
+ {
+ err("MySQL: cannot handle result column %u", count);
+ return -1;
+ }
+
+ if (*result->is_null)
+ {
+ err("MySQL: column %u '%s': is NULL", count,
+ count == 3? "uid": "gid");
+ return -2;
+ }
+
+ DPRINTF("MySQL: column %u: %lu", count,
+ my_query_buf.numeric[count - 3]);
+
+ if (count == 3)
+ ui.uid = (uid_t) my_query_buf.numeric[0];
+ else
+ ui.gid = (gid_t) my_query_buf.numeric[1];
+ return 0;
+}
+
+static int
+my_stmt_execute(char const *username, char const *defdomain, char const
*service)
+/*
+* This is called either right after preparing the query, or on successive
+* queries. Errors are likely due to disconnected server, in that case
+* it is necessary to prepare the query again before retrying.
+*
+* return values:
+* -1 hard error, general failure; or
+* query ok, but some required column is missing (internally -2);
+* 0 ok, user data is in ui, if user found;
+* 1 stmt error, unsupported parameter or result -never again; or
+* server or connection error, should prepare again -default (internally 2).
+*/
+{
+ int rc;
+ unsigned length[3];
+ char const *fail = NULL;
+
+ length[0] = strlen(username);
+
+ /* bind parameters */
+ if (my_stmt.order[0] >= 3)
+ {
+ if (strchr(username, '@') == NULL)
+ {
+ unsigned len;
+ length[1] = strlen(defdomain);
+ len = length[0] + length[1] + 1;
+ if (len >= sizeof my_query_param_buf.string_buf)
+ {
+ rc = -1;
+ fail = "user+domain exceeds max string length";
+ goto error_exit;
+ }
+ strcpy(&my_query_param_buf.string_buf[0], username);
+ my_query_param_buf.string_buf[length[0]] = '@';
+ strcpy(&my_query_param_buf.string_buf[length[0] + 1],
+ defdomain);
+ my_stmt_string_param(0,
+ my_query_param_buf.string_buf, len);
+ }
+ else
+ my_stmt_string_param(0, username, length[0]);
+ }
+ else
+ {
+ unsigned count;
+ char const *parm[3];
+
+ if ((parm[1] = strchr(username, '@')) == NULL)
+ {
+ parm[0] = username;
+ parm[1] = defdomain;
+ length[1] = strlen(defdomain);
+ }
+ else
+ {
+ length[1] = length[0];
+ length[0] = parm[1] - username;
+ parm[1] += 1;
+ length[1] -= length[0] + 1;
+ if (length[0] >= sizeof my_query_param_buf.string_buf)
+ {
+ rc = -1;
+ fail = "user+domain exceeds max string length";
+ goto error_exit;
+ }
+ strncpy(my_query_param_buf.string_buf, username,
+ length[0]);
+ my_query_param_buf.string_buf[length[0]] = 0;
+ parm[0] = my_query_param_buf.string_buf;
+ }
+
+ parm[2] = service? service: "";
+ length[2] = strlen(parm[2]);
+
+ for (count = 0; count < my_stmt.param_size; ++count)
+ {
+ unsigned const ndx = my_stmt.order[count];
+ my_stmt_string_param(count, parm[ndx], length[ndx]);
+ }
+ }
+
+ if (mysql_stmt_bind_param(my_stmt.handle, my_stmt.param))
+ {
+ rc = mysql_stmt_errno(my_stmt.handle);
+ if (rc == CR_UNSUPPORTED_PARAM_TYPE)
+ rc = 1;
+ else
+ rc = 2;
+ fail = "bind param";
+ goto error_exit;
+ }
+
+ /* execute query */
+ if (mysql_stmt_execute(my_stmt.handle))
+ {
+ rc = mysql_stmt_errno(my_stmt.handle);
+ if (rc == CR_SERVER_GONE_ERROR || rc == CR_SERVER_LOST)
+ rc = 2;
+ else
+ rc = -1;
+ fail = "execute";
+ goto error_exit;
+ }
+
+ /* bind results */
+ if (mysql_stmt_bind_result(my_stmt.handle, my_stmt.result))
+ {
+ rc = mysql_stmt_errno(my_stmt.handle);
+ if (rc == CR_UNSUPPORTED_PARAM_TYPE)
+ /*
+ * All unsupported conversions are signalled here,
+ * except for date/time buffer-type on non date/time
+ * column-type (commented out below).
+ */
+ rc = 1;
+ else
+ rc = -1;
+ fail = "bind result";
+ goto error_exit;
+ }
+
+ /* fetch results */
+ rc = mysql_stmt_fetch(my_stmt.handle);
+ if (rc != 0 && rc != MYSQL_DATA_TRUNCATED && rc != MYSQL_NO_DATA)
+ {
+ rc = mysql_stmt_errno(my_stmt.handle);
+ if (rc == CR_SERVER_GONE_ERROR || rc == CR_SERVER_LOST)
+ rc = 2;
+ // else if (rc == CR_UNSUPPORTED_PARAM_TYPE)
+ // rc = 1; // bad date/time - see comment above
+ else
+ rc = -1;
+ fail = "fetch result";
+ goto error_exit;
+ }
+
+ if (rc == MYSQL_NO_DATA)
+ {
+ DPRINTF("MySQL: no rows found");
+ }
+ else
+ {
+ /* 0: username */
+ rc = my_stmt_string_result(0, &ui.username);
+
+ /* 1: cryptpw */
+ if (rc == 0)
+ rc = my_stmt_string_result(1, &ui.cryptpw);
+
+ /* 2: clearpw */
+ if (rc == 0)
+ rc = my_stmt_string_result(2, &ui.clearpw);
+
+ /* 3: uid */
+ if (rc == 0)
+ rc = my_stmt_numeric_result(3);
+
+ /* 4: gid */
+ if (rc == 0)
+ rc = my_stmt_numeric_result(4);
+
+ /* 5: home */
+ if (rc == 0)
+ {
+ rc = my_stmt_string_result(5, &ui.home);
+ if (ui.home == NULL)
+ {
+ err("required value for "
+ "'home' (column 5) is missing");
+ rc = -2;
+ }
+ }
+
+ /* 6: maildir */
+ if (rc == 0 && my_stmt.result_size > 6)
+ rc = my_stmt_string_result(6, &ui.maildir);
+
+ /* 7: quota */
+ if (rc == 0 && my_stmt.result_size > 7)
+ rc = my_stmt_string_result(7, &ui.quota);
+
+ /* 8: fullname */
+ if (rc == 0 && my_stmt.result_size > 8)
+ rc = my_stmt_string_result(8, &ui.fullname);
+
+ /* 9: options */
+ if (rc == 0 && my_stmt.result_size > 9)
+ rc = my_stmt_string_result(9, &ui.options);
+
+ if (rc != 0)
+ {
+ fail = "convert field";
+ goto error_exit;
+ }
+ }
+
+ mysql_stmt_free_result(my_stmt.handle);
+ my_stmt.status = stmt_always;
+ return 0;
+
+
+error_exit:
+{
+ static const char * const internal_rc[] =
+ {
+ "missing data", // -2, n.u.
+ "hard error", // -1
+ "ok", // 0, n.u.
+ "unsupported query", // 1
+ "recoverable error" // 2
+ };
+
+ if (rc == -2)
+ {
+ // err already logged
+ rc = -1;
+ mysql_stmt_free_result(my_stmt.handle);
+ }
+ else
+ {
+ err("MySQL: failed %s: %u (%s) %s, diagnosed as %s",
+ fail? fail: "unknown",
+ mysql_stmt_errno(my_stmt.handle),
+ mysql_stmt_sqlstate(my_stmt.handle),
+ explicit_mysql_stmt_error(),
+ rc+2 >= 0 &&
+ (size_t)(rc+2) < sizeof internal_rc /
+ sizeof internal_rc[0]?
+ internal_rc[rc+2]: "foobar");
+ if (rc > 1)
+ /* attempt recovery only if called on successive query */
+ {
+ rc = 1;
+ if (my_stmt.status == stmt_always)
+ {
+ auth_mysql_cleanup();
+ if (do_connect())
+ rc = -1;
+ }
+ }
+ else
+ /* hard error, possibly due to unsupported query */
+ {
+ my_stmt_cleanup();
+ if (rc == 1)
+ my_stmt.status = stmt_never;
+ }
+ }
+ return rc;
+}}
+#endif // MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+
struct authmysqluserinfo *auth_mysql_getuserinfo(const char *username,
const char *service)
{
-const char *defdomain =NULL;
+const char *defdomain;
char *querybuf, *p;
MYSQL_ROW row;
MYSQL_RES *result;
@@ -657,16 +1357,29 @@
const char *select_clause; /* [EMAIL PROTECTED] */
static const char query[]=
- "SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s = \"";
+ "SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s FROM %s WHERE %s = ";
if (do_connect()) return (0);
initui();
- select_clause=read_env("MYSQL_SELECT_CLAUSE");
defdomain=read_env("DEFAULT_DOMAIN");
if (!defdomain) defdomain="";
+#if MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+ if (my_stmt.status == stmt_always)
+ {
+ switch (my_stmt_execute(username, defdomain, service))
+ {
+ default:
+ case -1: return 0;
+ case 0: return &ui;
+ case 1: break;
+ }
+ }
+#endif // MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+
+ select_clause=read_env("MYSQL_SELECT_CLAUSE");
if (!select_clause) /* [EMAIL PROTECTED] */
{
const char *user_table,
@@ -742,8 +1455,8 @@
+ strlen(name_field)
+ strlen(options_field)
+ strlen(user_table)
- + strlen(username)
- + strlen(defdomain)
+ + 2* (strlen(username) /* escaped fields */
+ + strlen(defdomain) + 1)
+ strlen(where_clause));
if (!querybuf)
@@ -758,8 +1471,35 @@
p=querybuf+strlen(querybuf);
+#if MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+ if (my_stmt.status == stmt_never ||
+ strchr(querybuf, '?') ||
+ strchr(where_clause, '?'))
+ my_stmt.status = stmt_never;
+ else
+ {
+ int rc;
+ p[0] = '?';
+ p[1] = 0;
+
+ if (strcmp(where_clause, "")) {
+ strcat(p, " AND (");
+ strcat(p, where_clause);
+ strcat(p, ")");
+ }
+
+ if ((rc = my_stmt_prepare(querybuf, 0)) == 0 &&
+ (rc = my_stmt_execute(username,
+ defdomain, service)) == 0)
+ return &ui;
+ if (rc < 0) return 0;
+ p[0] = 0;
+ }
+#endif // MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+
+ *p++ = '\'';
append_username(p, username, defdomain);
- strcat(p, "\"");
+ strcat(p, "\'");
if (strcmp(where_clause, "")) {
strcat(p, " AND (");
@@ -769,6 +1509,32 @@
}
else
{
+#if MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+ if (my_stmt.status == stmt_never ||
+ *select_clause == 0 || strchr(select_clause, '?'))
+ my_stmt.status = stmt_never;
+ else if (username && *username)
+ {
+ char *query = parse_select_clause_direct(select_clause,
+ "?", "?", "?");
+ char *order = parse_select_clause_direct(select_clause,
+ "?0", "?1", "?2");
+ int rc;
+ if (query && order)
+ {
+ if ((rc = my_stmt_prepare(query, order)) == 0)
+ rc = my_stmt_execute(username,
+ defdomain, service);
+ }
+ else
+ rc = -1;
+ free(query);
+ free(order);
+ if (rc == 0) return &ui;
+ if (rc < 0) return 0;
+ }
+#endif // MYSQL_VERSION_ID >= MY_TARGET_STMT_VERSION
+
/* [EMAIL PROTECTED] */
querybuf=parse_select_clause (select_clause, username,
defdomain, service);
@@ -779,43 +1545,6 @@
}
}
-/* Anton Dobkin <[EMAIL PROTECTED]>, VIAN, Ltd. */
-#if MYSQL_VERSION_ID >= 41000
- const char *character_set=read_env("MYSQL_CHARACTER_SET");
-
- if(character_set){
-
- char *character_set_buf;
-
- character_set_buf=malloc(strlen(character_set)+11);
-
- if (!character_set_buf)
- {
- perror("malloc");
- return (0);
- }
-
- strcpy(character_set_buf, "SET NAMES ");
- strcat(character_set_buf, character_set);
-
- DPRINTF("Install of a character set for MySQL. SQL query: SET
NAMES %s", character_set);
-
- if(mysql_query (mysql, character_set_buf))
- {
- err("Install of a character set for MySQL is failed: %s
MYSQL_CHARACTER_SET: may be invalid character set", mysql_error(mysql));
- auth_mysql_cleanup();
-
- if (do_connect())
- {
- free(character_set_buf);
- return (0);
- }
- }
-
- free(character_set_buf);
- }
-#endif
-
DPRINTF("SQL query: %s", querybuf);
if (mysql_query (mysql, querybuf))
{
@@ -887,7 +1616,7 @@
ui.home=strdup(row[5]);
else
{
- DPRINTF("required value for 'home' (column 6)
is missing");
+ DPRINTF("required value for 'home' (column 5)
is missing");
mysql_free_result(result);
return(0);
}
@@ -920,8 +1649,6 @@
const char *oldpass)
{
char *newpass_crypt;
- const char *p;
- int l;
char *sql_buf;
const char *comma;
int rc=0;
@@ -933,6 +1660,8 @@
*user_table =NULL,
*login_field =NULL,
*chpass_clause =NULL; /* [EMAIL PROTECTED] */
+
+ unsigned const passlen = strlen(pass? pass: "");
if (!mysql)
return (-1);
@@ -941,18 +1670,6 @@
if (!(newpass_crypt=authcryptpasswd(pass, oldpass)))
return (-1);
- for (l=0, p=pass; *p; p++)
- {
- if ((int)(unsigned char)*p < ' ')
- {
- free(newpass_crypt);
- return (-1);
- }
- if (*p == '"' || *p == '\\')
- ++l;
- ++l;
- }
-
/* [EMAIL PROTECTED] */
chpass_clause=read_env("MYSQL_CHPASS_CLAUSE");
defdomain=read_env("DEFAULT_DOMAIN");
@@ -966,10 +1683,13 @@
where_clause=read_env("MYSQL_WHERE_CLAUSE");
sql_buf=malloc(strlen(crypt_field ? crypt_field:"")
+ strlen(clear_field ? clear_field:"")
- + strlen(defdomain ? defdomain:"")
- + strlen(login_field) + l +
strlen(newpass_crypt)
+ + strlen(login_field) + strlen(newpass_crypt)
+ strlen(user_table)
+ strlen(where_clause ? where_clause:"")
+ + /* 3 escaped fields */ 3 + 2 * (
+ strlen(defdomain ? defdomain:"") +
+ strlen(user) +
+ passlen)
+ 200);
}
else
@@ -999,16 +1719,11 @@
char *q;
strcat(strcat(strcat(sql_buf, " "), clear_field),
- "=\"");
+ "=\'");
q=sql_buf+strlen(sql_buf);
- while (*pass)
- {
- if (*pass == '"' || *pass == '\\')
- *q++= '\\';
- *q++ = *pass++;
- }
- strcpy(q, "\"");
+ q += mysql_real_escape_string(mysql, q, pass, passlen);
+ strcpy(q, "\'");
comma=", ";
}
@@ -1025,11 +1740,11 @@
strcat(strcat(strcat(sql_buf, " WHERE "),
login_field),
- "=\"");
+ "=\'");
append_username(sql_buf+strlen(sql_buf), user, defdomain);
- strcat(sql_buf, "\"");
+ strcat(sql_buf, "\'");
if (where_clause && *where_clause)
{
@@ -1051,7 +1766,7 @@
}
if (mysql_query (mysql, sql_buf))
{
- DPRINTF("setpass SQL failed");
+ err("mysql: setpass SQL failed: %s", mysql_error(mysql));
rc= -1;
auth_mysql_cleanup();
}
--- authmysqlrc.original 2007-10-07 04:50:45.000000000 +0200
+++ authmysqlrc 2008-04-17 19:26:55.000000000 +0200
@@ -195,9 +195,10 @@
# (EXPERIMENTAL)
# This is optional, MYSQL_SELECT_CLAUSE can be set when you have a database,
# which is structuraly different from proposed. The fixed string will
-# be used to do a SELECT operation on database, which should return fields
-# in order specified bellow:
+# be used to do a SELECT operation on database, which should return ten fields
+# in the order specified below:
#
+# 0 1 2 3 4 5 6 7 8 9
# username, cryptpw, clearpw, uid, gid, home, maildir, quota, fullname, options
#
# The username field should include the domain (see example below).
@@ -205,7 +206,7 @@
# Enabling this option causes ignorance of any other field-related
# options, excluding default domain.
#
-# There are two variables, which you can use. Substitution will be made
+# There are three variables, which you can use. Substitution will be made
# for them, so you can put entered username (local part) and domain name
# in the right place of your query. These variables are:
# $(local_part), $(domain), $(service)
@@ -220,8 +221,17 @@
# DEFAULTDELIVERY. Courier mail server will also use esmtp when doing
# authenticated ESMTP.
#
-# This example is a little bit modified adaptation of vmail-sql
-# database scheme:
+# The following example is a little bit modified adaptation of vmail-sql
+# database scheme.
+#
+# NOTE: you have to remove the single quotes around the variables to use
+# prepared queries. This example will NOT WORK with prepared statements
+# because the variables are quoted. Quoting is fine for direct queries where
+# each variable is replaced by its escaped value. For preparing a query, the
+# variables are replaced by markers, and values are passed as parameters on
+# each execution. For that to work, markers must be outside quotes. Please
+# set DEBUG_LOGIN=1 and watch the log as you run authtest in order to get some
+# insight on what happens.
#
# MYSQL_SELECT_CLAUSE SELECT CONCAT(popbox.local_part, '@',
popbox.domain_name), \
# CONCAT('{MD5}', popbox.password_hash), \
@@ -238,7 +248,8 @@
# FROM popbox, domain \
# WHERE popbox.local_part = '$(local_part)' \
# AND popbox.domain_name = '$(domain)' \
-# AND popbox.domain_name = domain.domain_name
+# AND popbox.domain_name = domain.domain_name \
+# LIMIT 1
##NAME: MYSQL_ENUMERATE_CLAUSE:1
@@ -248,6 +259,8 @@
# in order to compile a list of accounts for shared folders. The query
# should return the following fields: name, uid, gid, homedir, maildir, options
#
+# No attempt is done to prepare a statement for this query.
+#
# Example:
# MYSQL_ENUMERATE_CLAUSE SELECT CONCAT(popbox.local_part, '@',
popbox.domain_name), \
# domain.uid, \
@@ -280,9 +293,11 @@
# $(newpass) contains plain password
# $(newpass_crypt) contains its crypted form
#
+# No attempt is done to prepare a statement for this query.
+#
# MYSQL_CHPASS_CLAUSE UPDATE popbox \
# SET clearpw='$(newpass)', \
# password_hash='$(newpass_crypt)' \
# WHERE local_part='$(local_part)' \
-# AND domain_name='$(domain)'
+# AND domain_name='$(domain)' LIMIT 1
#
-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
courier-users mailing list
[email protected]
Unsubscribe: https://lists.sourceforge.net/lists/listinfo/courier-users
