1 /*
2 * ProFTPD: mod_sql_mysql -- Support for connecting to MySQL databases.
3 * Copyright (c) 2001 Andrew Houghton
4 * Copyright (c) 2004-2021 TJ Saunders
5 *
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 2 of the License, or
9 * (at your option) any later version.
10 *
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with this program; if not, write to the Free Software
18 * Foundation, Inc., 51 Franklin Street, Suite 500, Boston, MA 02110-1335, USA.
19 *
20 * As a special exemption, Andrew Houghton and other respective copyright
21 * holders give permission to link this program with OpenSSL, and distribute
22 * the resulting executable, without including the source code for OpenSSL in
23 * the source distribution.
24 *
25 * -----DO NOT EDIT-----
26 * $Libraries: -lm -lmysqlclient -lz$
27 */
28
29 /* INTRO:
30 *
31 * mod_sql_mysql is the reference backend module for mod_sql. As such,
32 * it's very, very over-commented.
33 *
34 * COPYRIGHT NOTICE:
35 *
36 * The section of the copyright notice above that refers to OpenSSL *must*
37 * be present in every backend module. Without that exemption the backend
38 * module cannot legally be compiled into ProFTPD, even if the backend
39 * module makes no use of OpenSSL.
40 *
41 * FUNCTIONS IN THIS CODE:
42 *
43 * Backend modules are only called into via the functions listed in
44 * sql_cmdtable (see the end of this file). All other functions are
45 * internal.
46 *
47 * For stylistic reasons, it's requested that backend authors maintain the
48 * following conventions:
49 * 1) when returning data in a modret_t, use the standard ProFTPD macros
50 * whenever possible (ERR_MSG, HANDLED, etc.)
51 * 2) although 'static modret_t *' and 'MODRET' are equivalent, please
52 * use MODRET only for those functions listed in sql_cmdtable.
53 *
54 * NAMED CONNECTIONS:
55 *
56 * Backend modules need to handle named connections. A named connection
57 * is the complete specification of how to access a database coupled with
58 * a unique (to the session) descriptive name. Every call mod_sql makes
59 * into a backend is directed at a particular named connection.
60 * mod_sql_mysql includes a set of simplistic cache functions to keep an
61 * internal map of names to connections -- other backends should feel free
62 * to handle this however they want.
63 *
64 * OPEN/CLOSE SEMANTICS & CONNECTION COUNTING:
65 *
66 * Administrators using mod_sql decide on one of three connection policies:
67 * 1) open a connection to the database and hold it open for the life of
68 * the client process
69 * 2) open a connection to the database and hold it open for the life of
70 * each call
71 * 3) open a connection to the database and hold it open until a specified
72 * period of time has elapsed with no activity
73 *
74 * mod_sql enforces this choice by requiring that backends:
75 * 1) wrap each call in an open/close bracket (so if a connection isn't
76 * currently open, it will be opened for the call and closed afterwards)
77 * 2) properly do connection counting to ensure that a connection is not
78 * re-opened unnecessarily, and not closed too early.
79 *
80 * In simple terms: if an administrator chooses the "one connection for the
81 * life of the process" policy, mod_sql will send an initial cmd_open call
82 * for that connection at the start of the client session, and a final
83 * cmd_close call when the session ends. If an administrator chooses the
84 * "per-call" connection policy, the initial cmd_open and final cmd_close
85 * calls will not be made. If an administrator chooses the "timeout"
86 * connection policy, connections may be closed at any time and may need
87 * to be reopened for any call.
88 *
89 * CONNECTION TIMERS
90 *
91 * Backends are required to handle connection timers; when a connection is
92 * defined via cmd_defineconnection, a time value (in seconds) will be sent
93 * with the definition. Given the complexity of the semantics, it's
94 * recommended that backend authors simply copy the timer handling code from
95 * this module. Timer handling code exists in nearly every function in this
96 * module; read the code for more information.
97 *
98 * ERROR HANDLING AND LOGGING:
99 *
100 * Proper error handling is required of backend modules -- the modret_t
101 * structure passed back to mod_sql should have the error fields correctly
102 * filled. mod_sql handles backend errors by logging them then closing the
103 * connection and the session. Therefore, it's not necessary for backends
104 * to log errors which will be passed back to mod_sql, but they should log
105 * any errors or useful information which will not be returned in the
106 * modret_t. If an error is transient -- if there's any way for the backend
107 * module to handle an error intelligently -- it should do so. mod_sql
108 * will always handle backend errors by ending the client session.
109 *
110 * Good debug logging is encouraged -- major functions (the functions that
111 * mod_sql calls directly) should be wrapped in 'entering' and 'exiting'
112 * DEBUG_FUNC level output, the text of SQL queries should be visible with
113 * DEBUG_INFO level output, and any errors should be visible with DEBUG_WARN
114 * level output.
115 *
116 * Check the code if this makes no sense.
117 *
118 * COMMENTS / QUESTIONS:
119 *
120 * Backend module writers are encouraged to read through all comments in this
121 * file. If anything is unclear, please contact the author.
122 */
123
124 /* Internal define used for debug and logging. All backends are encouraged
125 * to use the same format.
126 */
127 #define MOD_SQL_MYSQL_VERSION "mod_sql_mysql/4.0.9"
128
129 #define _MYSQL_PORT "3306"
130
131 #include "conf.h"
132 #include "../contrib/mod_sql.h"
133
134 #include <mysql.h>
135 #include <stdbool.h>
136
137 /* The my_make_scrambled_password{,_323} functions are not part of the public
138 * MySQL API and are not declared in any of the MySQL header files. But the
139 * use of these functions are required for implementing the "Backend"
140 * SQLAuthType for MySQL. Thus these functions are declared here (Bug#3908).
141 */
142 #if defined(HAVE_MYSQL_MY_MAKE_SCRAMBLED_PASSWORD)
143 void my_make_scrambled_password(char *to, const char *from, size_t fromlen);
144 #endif
145
146 #if defined(HAVE_MYSQL_MY_MAKE_SCRAMBLED_PASSWORD_323)
147 void my_make_scrambled_password_323(char *to, const char *from, size_t fromlen);
148 #endif
149
150 /* Timer-handling code adds the need for a couple of forward declarations. */
151 MODRET cmd_close(cmd_rec *cmd);
152 module sql_mysql_module;
153
154 /*
155 * db_conn_struct: an internal struct to hold connection information. This
156 * connection information is backend-specific; the members here reflect
157 * the information MySQL needs for connections.
158 *
159 * Other backends are expected to make whatever changes are necessary.
160 */
161 struct db_conn_struct {
162
163 /* MySQL-specific members */
164 const char *host;
165 const char *user;
166 const char *pass;
167 const char *db;
168 const char *port;
169 const char *unix_sock;
170
171 /* For configuring the SSL/TLS session to the MySQL server. */
172 const char *ssl_cert_file;
173 const char *ssl_key_file;
174 const char *ssl_ca_file;
175 const char *ssl_ca_dir;
176 const char *ssl_ciphers;
177
178 MYSQL *mysql;
179 };
180
181 typedef struct db_conn_struct db_conn_t;
182
183 /*
184 * This struct is a wrapper for whatever backend data is needed to access
185 * the database, and supports named connections, connection counting, and
186 * timer handling. In most cases it should be enough for backend authors
187 * to change db_conn_t and leave this struct alone.
188 */
189
190 struct conn_entry_struct {
191 const char *name;
192 void *data;
193
194 /* Timer handling */
195 int timer;
196 int ttl;
197
198 /* Connection handling */
199 unsigned int connections;
200 };
201
202 typedef struct conn_entry_struct conn_entry_t;
203
204 #define DEF_CONN_POOL_SIZE 10
205
206 static pool *conn_pool = NULL;
207 static array_header *conn_cache = NULL;
208
209 static const char *trace_channel = "sql.mysql";
210
211 /* sql_get_connection: walks the connection cache looking for the named
212 * connection. Returns NULL if unsuccessful, a pointer to the conn_entry_t
213 * if successful.
214 */
sql_get_connection(const char * conn_name)215 static conn_entry_t *sql_get_connection(const char *conn_name) {
216 register unsigned int i;
217
218 if (conn_name == NULL) {
219 errno = EINVAL;
220 return NULL;
221 }
222
223 /* walk the array looking for our entry */
224 for (i = 0; i < conn_cache->nelts; i++) {
225 conn_entry_t *entry;
226
227 entry = ((conn_entry_t **) conn_cache->elts)[i];
228 if (strcmp(conn_name, entry->name) == 0) {
229 return entry;
230 }
231 }
232
233 errno = ENOENT;
234 return NULL;
235 }
236
237 /* sql_add_connection: internal helper function to maintain a cache of
238 * connections. Since we expect the number of named connections to be small,
239 * simply use an array header to hold them. We don't allow duplicate
240 * connection names.
241 *
242 * Returns: NULL if the insertion was unsuccessful, a pointer to the
243 * conn_entry_t that was created if successful.
244 */
sql_add_connection(pool * p,const char * name,db_conn_t * conn)245 static void *sql_add_connection(pool *p, const char *name, db_conn_t *conn) {
246 conn_entry_t *entry = NULL;
247
248 if (name == NULL ||
249 conn == NULL ||
250 p == NULL) {
251 errno = EINVAL;
252 return NULL;
253 }
254
255 if (sql_get_connection(name) != NULL) {
256 errno = EEXIST;
257 return NULL;
258 }
259
260 entry = (conn_entry_t *) pcalloc(p, sizeof(conn_entry_t));
261 entry->name = pstrdup(p, name);
262 entry->data = conn;
263
264 *((conn_entry_t **) push_array(conn_cache)) = entry;
265 return entry;
266 }
267
268 /* sql_check_cmd: tests to make sure the cmd_rec is valid and is properly
269 * filled in. If not, it's grounds for the daemon to shutdown.
270 */
sql_check_cmd(cmd_rec * cmd,char * msg)271 static void sql_check_cmd(cmd_rec *cmd, char *msg) {
272 if (cmd == NULL ||
273 cmd->tmp_pool == NULL) {
274 pr_log_pri(PR_LOG_ERR, MOD_SQL_MYSQL_VERSION
275 ": '%s' was passed an invalid cmd_rec (internal bug); shutting down",
276 msg);
277 sql_log(DEBUG_WARN, "'%s' was passed an invalid cmd_rec (internal bug); "
278 "shutting down", msg);
279 pr_session_end(0);
280 }
281
282 return;
283 }
284
285 /* sql_timer_cb: when a timer goes off, this is the function that gets called.
286 * This function makes assumptions about the db_conn_t members.
287 */
sql_timer_cb(CALLBACK_FRAME)288 static int sql_timer_cb(CALLBACK_FRAME) {
289 register unsigned int i;
290
291 for (i = 0; i < conn_cache->nelts; i++) {
292 conn_entry_t *entry = NULL;
293
294 entry = ((conn_entry_t **) conn_cache->elts)[i];
295 if ((unsigned long) entry->timer == p2) {
296 cmd_rec *cmd = NULL;
297
298 sql_log(DEBUG_INFO, "timer expired for connection '%s'", entry->name);
299 cmd = sql_make_cmd(conn_pool, 2, entry->name, "1");
300 cmd_close(cmd);
301 SQL_FREE_CMD(cmd);
302 entry->timer = 0;
303 }
304 }
305
306 return 0;
307 }
308
309 /* build_error: constructs a modret_t filled with error information;
310 * mod_sql_mysql calls this function and returns the resulting mod_ret_t
311 * whenever a call to the database results in an error. Other backends
312 * may want to use a different method to return error information.
313 */
build_error(cmd_rec * cmd,db_conn_t * conn)314 static modret_t *build_error(cmd_rec *cmd, db_conn_t *conn) {
315 char num[20] = {'\0'};
316
317 if (conn == NULL) {
318 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
319 }
320
321 pr_snprintf(num, 20, "%u", mysql_errno(conn->mysql));
322 return PR_ERROR_MSG(cmd, pstrdup(cmd->pool, num),
323 pstrdup(cmd->pool, (char *) mysql_error(conn->mysql)));
324 }
325
326 /* build_data: both cmd_select and cmd_procedure potentially
327 * return data to mod_sql; this function builds a modret to return
328 * that data. This is MySQL specific; other backends may choose
329 * to do things differently.
330 */
build_data(cmd_rec * cmd,db_conn_t * conn)331 static modret_t *build_data(cmd_rec *cmd, db_conn_t *conn) {
332 modret_t *mr = NULL;
333 MYSQL *mysql = NULL;
334 MYSQL_RES *result = NULL;
335 MYSQL_ROW row;
336 sql_data_t *sd = NULL;
337 char **data = NULL;
338 unsigned long cnt = 0;
339 unsigned long i = 0;
340
341 if (conn == NULL) {
342 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
343 }
344
345 mysql = conn->mysql;
346
347 /* Would much rather use mysql_use_result here but without knowing
348 * the number of rows returned we can't presize the data[] array.
349 */
350
351 result = mysql_store_result(mysql);
352 if (!result) {
353 return build_error(cmd, conn);
354 }
355
356 sd = (sql_data_t *) pcalloc(cmd->tmp_pool, sizeof(sql_data_t));
357 sd->rnum = (unsigned long) mysql_num_rows(result);
358 sd->fnum = (unsigned long) mysql_num_fields(result);
359 cnt = sd->rnum * sd->fnum;
360
361 data = (char **) pcalloc(cmd->tmp_pool, sizeof(char *) * (cnt + 1));
362
363 while ((row = mysql_fetch_row(result))) {
364 for (cnt = 0; cnt < sd->fnum; cnt++)
365 data[i++] = pstrdup(cmd->tmp_pool, row[cnt]);
366 }
367
368 /* At this point either we finished correctly or an error occurred in the
369 * fetch. Do the right thing.
370 */
371 if (mysql_errno(mysql) != 0) {
372 mr = build_error(cmd, conn);
373 mysql_free_result(result);
374 return mr;
375 }
376
377 mysql_free_result(result);
378 data[i] = NULL;
379 sd->data = data;
380
381 #ifdef CLIENT_MULTI_RESULTS
382 /* We might be dealing with multiple result sets here, as when a stored
383 * procedure was called which produced more results than we expect.
384 *
385 * We only want the first result set, so simply iterate through and free
386 * up any remaining result sets.
387 */
388 while (mysql_next_result(mysql) == 0) {
389 pr_signals_handle();
390 result = mysql_store_result(mysql);
391 mysql_free_result(result);
392 }
393 #endif
394
395 return mod_create_data(cmd, (void *) sd);
396 }
397
398 /*
399 * cmd_open: attempts to open a named connection to the database.
400 *
401 * Inputs:
402 * cmd->argv[0]: connection name
403 *
404 * Returns:
405 * either a properly filled error modret_t if a connection could not be
406 * opened, or a simple non-error modret_t.
407 *
408 * Notes:
409 * mod_sql depends on these semantics -- a backend should not open
410 * a connection unless mod_sql requests it, nor close one unless
411 * mod_sql requests it. Connection counting is *REQUIRED* for complete
412 * compatibility; a connection should not be closed unless the count
413 * reaches 0, and ideally will not need to be re-opened for counts > 1.
414 */
cmd_open(cmd_rec * cmd)415 MODRET cmd_open(cmd_rec *cmd) {
416 conn_entry_t *entry = NULL;
417 db_conn_t *conn = NULL;
418 unsigned long client_flags = CLIENT_INTERACTIVE;
419 #ifdef PR_USE_NLS
420 const char *encoding = NULL;
421 #endif
422 #ifdef HAVE_MYSQL_MYSQL_GET_SSL_CIPHER
423 const char *ssl_cipher = NULL;
424 #endif
425
426 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_open");
427
428 sql_check_cmd(cmd, "cmd_open");
429
430 if (cmd->argc < 1) {
431 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_open");
432 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
433 }
434
435 entry = sql_get_connection(cmd->argv[0]);
436 if (entry == NULL) {
437 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_open");
438 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
439 pstrcat(cmd->tmp_pool, "unknown named connection: ", cmd->argv[0], NULL));
440 }
441
442 conn = (db_conn_t *) entry->data;
443
444 /* If we're already open (connections > 0), AND our connection to MySQL
445 * is still alive, increment the connection counter, reset our timer (if
446 * we have one), and return HANDLED.
447 */
448 if (entry->connections > 0) {
449 if (mysql_ping(conn->mysql) == 0) {
450 entry->connections++;
451
452 if (entry->timer) {
453 pr_timer_reset(entry->timer, &sql_mysql_module);
454 }
455
456 sql_log(DEBUG_INFO, "connection '%s' count is now %d", entry->name,
457 entry->connections);
458 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_open");
459 return PR_HANDLED(cmd);
460
461 } else {
462 sql_log(DEBUG_INFO, "lost connection to database: %s",
463 mysql_error(conn->mysql));
464
465 entry->connections = 0;
466 if (entry->timer) {
467 pr_timer_remove(entry->timer, &sql_mysql_module);
468 entry->timer = 0;
469 }
470
471 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_open");
472 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
473 "lost connection to database");
474 }
475 }
476
477 /* Make sure we have a new conn struct */
478 conn->mysql = mysql_init(NULL);
479 if (conn->mysql == NULL) {
480 pr_log_pri(PR_LOG_ALERT, MOD_SQL_MYSQL_VERSION
481 ": failed to allocate memory for MYSQL structure; shutting down");
482 sql_log(DEBUG_WARN, "%s", "failed to allocate memory for MYSQL structure; "
483 "shutting down");
484 pr_session_end(0);
485 }
486
487 if (!(pr_sql_opts & SQL_OPT_IGNORE_CONFIG_FILE)) {
488 /* Make sure the MySQL config files are read in. This will read in
489 * options from group "client" in the MySQL .cnf files.
490 */
491 mysql_options(conn->mysql, MYSQL_READ_DEFAULT_GROUP, "client");
492 }
493
494 #if MYSQL_VERSION_ID >= 50013
495 /* The MYSQL_OPT_RECONNECT option appeared in MySQL 5.0.13, according to
496 *
497 * http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html
498 */
499 if (!(pr_sql_opts & SQL_OPT_NO_RECONNECT)) {
500 #if MYSQL_VERSION_ID >= 80000
501 bool reconnect = true;
502 #else
503 my_bool reconnect = TRUE;
504 #endif
505 mysql_options(conn->mysql, MYSQL_OPT_RECONNECT, &reconnect);
506 }
507 #endif
508
509 #ifdef CLIENT_MULTI_RESULTS
510 /* Enable mod_sql_mysql to deal with multiple result sets which may be
511 * returned from calling stored procedures.
512 */
513 client_flags |= CLIENT_MULTI_RESULTS;
514 #endif
515
516 #if defined(HAVE_MYSQL_MYSQL_SSL_SET)
517 /* Per the MySQL docs, this function always returns success. Errors are
518 * reported when we actually attempt to connect.
519 *
520 * Note: There are some other TLS-related options, in newer versions of
521 * MySQL, which might be interest (although they require the use of the
522 * mysql_options() function, not mysql_ssl_set()):
523 *
524 * MYSQL_OPT_SSL_ENFORCE (boolean, defaults to 'false')
525 * MYSQL_OPT_SSL_VERIFY_SERVER_CERT (boolean, defaults to 'false')
526 * MYSQL_OPT_TLS_VERSION (char *, for configuring the protocol versions)
527 */
528 (void) mysql_ssl_set(conn->mysql, conn->ssl_key_file, conn->ssl_cert_file,
529 conn->ssl_ca_file, conn->ssl_ca_dir, conn->ssl_ciphers);
530 #endif
531
532 if (!mysql_real_connect(conn->mysql, conn->host, conn->user, conn->pass,
533 conn->db, (int) strtol(conn->port, (char **) NULL, 10),
534 conn->unix_sock, client_flags)) {
535 modret_t *mr = NULL;
536
537 /* If it didn't work, return an error. */
538 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_open");
539 mr = build_error(cmd, conn);
540
541 /* Since we failed to connect here, avoid a memory leak by freeing up the
542 * mysql conn struct.
543 */
544 mysql_close(conn->mysql);
545 conn->mysql = NULL;
546
547 return mr;
548 }
549
550 sql_log(DEBUG_FUNC, "MySQL version ID: %d", MYSQL_VERSION_ID);
551 sql_log(DEBUG_FUNC, "MySQL client version: %s", mysql_get_client_info());
552 sql_log(DEBUG_FUNC, "MySQL server version: %s",
553 mysql_get_server_info(conn->mysql));
554
555 # if MYSQL_VERSION_ID >= 50703 && defined(HAVE_MYSQL_GET_OPTION)
556 /* Log the configured authentication plugin, if any. For example, it
557 * might be set in the my.cnf file using:
558 *
559 * [client]
560 * default-auth = mysql_native_password
561 *
562 * Note: the mysql_get_option() function appeared in MySQL 5.7.3, as per:
563 *
564 * https://dev.mysql.com/doc/refman/5.7/en/mysql-get-option.html
565 *
566 * The MYSQL_DEFAULT_AUTH value is an enum, not a #define, so we cannot
567 * use a simple #ifdef here.
568 */
569 {
570 const char *auth_plugin = NULL;
571
572 if (mysql_get_option(conn->mysql, MYSQL_DEFAULT_AUTH, &auth_plugin) == 0) {
573 /* There may not have been a default auth plugin explicitly configured,
574 * and the MySQL internals themselves may not set one. So it is not
575 * surprising if the pointer remains null.
576 */
577 if (auth_plugin != NULL) {
578 sql_log(DEBUG_FUNC, "MySQL client default authentication plugin: %s",
579 auth_plugin);
580 }
581 }
582 }
583 #endif /* MySQL 5.7.3 and later */
584
585 #if defined(HAVE_MYSQL_MYSQL_GET_SSL_CIPHER)
586 ssl_cipher = mysql_get_ssl_cipher(conn->mysql);
587 /* XXX Should we fail the connection here, if we expect an SSL session to
588 * have been successfully completed/required?
589 */
590 if (ssl_cipher != NULL) {
591 sql_log(DEBUG_FUNC, "%s", "MySQL SSL connection: true");
592 sql_log(DEBUG_FUNC, "MySQL SSL cipher: %s", ssl_cipher);
593
594 } else {
595 sql_log(DEBUG_FUNC, "%s", "MySQL SSL connection: false");
596 }
597 #endif
598
599 #if defined(PR_USE_NLS)
600 encoding = pr_encode_get_encoding();
601 if (encoding != NULL) {
602
603 # if MYSQL_VERSION_ID >= 50007
604 /* Configure the connection for the current local character set.
605 *
606 * Note: the mysql_set_character_set() function appeared in MySQL 5.0.7,
607 * as per:
608 *
609 * http://dev.mysql.com/doc/refman/5.0/en/mysql-set-character-set.html
610 *
611 * Yes, even though the variable names say "charset", we (and MySQL,
612 * though their documentation says otherwise) actually mean "encoding".
613 */
614
615 if (strcasecmp(encoding, "UTF-8") == 0) {
616 # if MYSQL_VERSION_ID >= 50503
617 /* MySQL prefers the name "utf8mb4", not "UTF-8" */
618 encoding = pstrdup(cmd->tmp_pool, "utf8mb4");
619 # else
620 /* MySQL prefers the name "utf8", not "UTF-8" */
621 encoding = pstrdup(cmd->tmp_pool, "utf8");
622 # endif /* MySQL before 5.5.3 */
623 }
624
625 if (mysql_set_character_set(conn->mysql, encoding) != 0) {
626 /* Failing to set the character set should NOT be a fatal error.
627 * There are situations where, due to client/server mismatch, the
628 * requested character set may not be available. Thus for now,
629 * we simply log the failure.
630 *
631 * A future improvement might be to implement fallback behavior,
632 * trying to set "older" character sets as needed.
633 */
634 sql_log(DEBUG_FUNC, MOD_SQL_MYSQL_VERSION
635 ": failed to set character set '%s': %s (%u)", encoding,
636 mysql_error(conn->mysql), mysql_errno(conn->mysql));
637 }
638
639 sql_log(DEBUG_FUNC, "MySQL connection character set now '%s' (from '%s')",
640 mysql_character_set_name(conn->mysql), pr_encode_get_encoding());
641
642 # else
643 /* No mysql_set_character_set() API available. But
644 * mysql_character_set_name() has been around for a while; we can use it
645 * to at least see whether there might be a character set discrepancy.
646 */
647
648 const char *local_charset = pr_encode_get_encoding();
649 const char *mysql_charset = mysql_character_set_name(conn->mysql);
650
651 if (strcasecmp(mysql_charset, "utf8") == 0) {
652 mysql_charset = pstrdup(cmd->tmp_pool, "UTF-8");
653 }
654
655 if (local_charset &&
656 mysql_charset &&
657 strcasecmp(local_charset, mysql_charset) != 0) {
658 pr_log_pri(PR_LOG_ERR, MOD_SQL_MYSQL_VERSION
659 ": local character set '%s' does not match MySQL character set '%s', "
660 "SQL injection possible, shutting down", local_charset, mysql_charset);
661 sql_log(DEBUG_WARN, "local character set '%s' does not match MySQL "
662 "character set '%s', SQL injection possible, shutting down",
663 local_charset, mysql_charset);
664 pr_session_end(0);
665 }
666 # endif /* older MySQL */
667 }
668 #endif /* !PR_USE_NLS */
669
670 /* bump connections */
671 entry->connections++;
672
673 if (pr_sql_conn_policy == SQL_CONN_POLICY_PERSESSION) {
674 /* If the connection policy is PERSESSION... */
675 if (entry->connections == 1) {
676 /* ...and we are actually opening the first connection to the database;
677 * we want to make sure this connection stays open, after this first use
678 * (as per Bug#3290). To do this, we re-bump the connection count.
679 */
680 entry->connections++;
681 }
682
683 } else if (entry->ttl > 0) {
684 /* Set up our timer if necessary */
685
686 entry->timer = pr_timer_add(entry->ttl, -1, &sql_mysql_module,
687 sql_timer_cb, "mysql connection ttl");
688 sql_log(DEBUG_INFO, "connection '%s' - %d second timer started",
689 entry->name, entry->ttl);
690
691 /* timed connections get re-bumped so they don't go away when cmd_close
692 * is called.
693 */
694 entry->connections++;
695 }
696
697 /* return HANDLED */
698 sql_log(DEBUG_INFO, "connection '%s' opened", entry->name);
699 sql_log(DEBUG_INFO, "connection '%s' count is now %d", entry->name,
700 entry->connections);
701 pr_event_generate("mod_sql.db.connection-opened", &sql_mysql_module);
702
703 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_open");
704 return PR_HANDLED(cmd);
705 }
706
707 /*
708 * cmd_close: attempts to close the named connection.
709 *
710 * Inputs:
711 * cmd->argv[0]: connection name
712 * Optional:
713 * cmd->argv[1]: close immediately
714 *
715 * Returns:
716 * either a properly filled error modret_t if a connection could not be
717 * closed, or a simple non-error modret_t. For the case of mod_sql_mysql,
718 * there are no error codes returned by the close call; other backends
719 * may be able to return a useful error message.
720 *
721 * Notes:
722 * mod_sql depends on these semantics -- a backend should not open
723 * a connection unless mod_sql requests it, nor close one unless
724 * mod_sql requests it. Connection counting is *REQUIRED* for complete
725 * compatibility; a connection should not be closed unless the count
726 * reaches 0, and should not need to be re-opened for counts > 1.
727 *
728 * If argv[1] exists and is not NULL, the connection should be immediately
729 * closed and the connection count should be reset to 0.
730 */
cmd_close(cmd_rec * cmd)731 MODRET cmd_close(cmd_rec *cmd) {
732 conn_entry_t *entry = NULL;
733 db_conn_t *conn = NULL;
734
735 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_close");
736
737 sql_check_cmd(cmd, "cmd_close");
738
739 if ((cmd->argc < 1) || (cmd->argc > 2)) {
740 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_close");
741 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
742 }
743
744 entry = sql_get_connection(cmd->argv[0]);
745 if (entry == NULL) {
746 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_close");
747 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
748 pstrcat(cmd->tmp_pool, "unknown named connection: ", cmd->argv[0], NULL));
749 }
750
751 conn = (db_conn_t *) entry->data;
752
753 /* if we're closed already (connections == 0) return HANDLED */
754 if (entry->connections == 0) {
755 sql_log(DEBUG_INFO, "connection '%s' count is now %d", entry->name,
756 entry->connections);
757
758 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_close");
759 return PR_HANDLED(cmd);
760 }
761
762 /* decrement connections. If our count is 0 or we received a second arg
763 * close the connection, explicitly set the counter to 0, and remove any
764 * timers.
765 */
766 if (((--entry->connections) == 0) || ((cmd->argc == 2) && (cmd->argv[1]))) {
767 if (conn->mysql != NULL) {
768 mysql_close(conn->mysql);
769 conn->mysql = NULL;
770 }
771 entry->connections = 0;
772
773 if (entry->timer) {
774 pr_timer_remove(entry->timer, &sql_mysql_module);
775 entry->timer = 0;
776 sql_log(DEBUG_INFO, "connection '%s' - timer stopped", entry->name);
777 }
778
779 sql_log(DEBUG_INFO, "connection '%s' closed", entry->name);
780 pr_event_generate("mod_sql.db.connection-closed", &sql_mysql_module);
781 }
782
783 sql_log(DEBUG_INFO, "connection '%s' count is now %d", entry->name,
784 entry->connections);
785 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_close");
786
787 return PR_HANDLED(cmd);
788 }
789
790 /* cmd_defineconnection: takes all information about a database
791 * connection and stores it for later use.
792 *
793 * Inputs:
794 * cmd->argv[0]: connection name
795 * cmd->argv[1]: username portion of the SQLConnectInfo directive
796 * cmd->argv[2]: password portion of the SQLConnectInfo directive
797 * cmd->argv[3]: info portion of the SQLConnectInfo directive
798 *
799 * Optional:
800 * cmd->argv[4]: time-to-live in seconds
801 * cmd->argv[5]: SSL client cert file
802 * cmd->argv[6]: SSL client key file
803 * cmd->argv[7]: SSL CA file
804 * cmd->argv[8]: SSL CA directory
805 * cmd->argv[9]: SSL ciphers
806 *
807 * Returns:
808 * either a properly filled error modret_t if the connection could not
809 * defined, or a simple non-error modret_t.
810 *
811 * Notes:
812 * time-to-live is the length of time to allow a connection to remain unused;
813 * once that amount of time has passed, a connection should be closed and
814 * it's connection count should be reduced to 0. If ttl is 0, or ttl is not
815 * a number or ttl is negative, the connection will be assumed to have no
816 * associated timer.
817 */
cmd_defineconnection(cmd_rec * cmd)818 MODRET cmd_defineconnection(cmd_rec *cmd) {
819 char *have_host = NULL, *have_port = NULL, *info = NULL, *name = NULL;
820 const char *db = NULL, *host = NULL, *port = NULL;
821 const char *ssl_cert_file = NULL, *ssl_key_file = NULL, *ssl_ca_file = NULL;
822 const char *ssl_ca_dir = NULL, *ssl_ciphers = NULL;
823 conn_entry_t *entry = NULL;
824 db_conn_t *conn = NULL;
825
826 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_defineconnection");
827
828 sql_check_cmd(cmd, "cmd_defineconnection");
829
830 if (cmd->argc < 4 ||
831 cmd->argc > 10 ||
832 !cmd->argv[0]) {
833 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_defineconnection");
834 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
835 }
836
837 if (conn_pool == NULL) {
838 pr_log_pri(PR_LOG_WARNING, "WARNING: the mod_sql_mysql module has not been "
839 "properly initialized. Please make sure your --with-modules configure "
840 "option lists mod_sql *before* mod_sql_mysql, and recompile.");
841
842 sql_log(DEBUG_FUNC, "%s", "The mod_sql_mysql module has not been properly "
843 "initialized. Please make sure your --with-modules configure option "
844 "lists mod_sql *before* mod_sql_mysql, and recompile.");
845 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_defineconnection");
846
847 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "uninitialized module");
848 }
849
850 conn = (db_conn_t *) pcalloc(conn_pool, sizeof(db_conn_t));
851
852 name = pstrdup(conn_pool, cmd->argv[0]);
853 conn->user = pstrdup(conn_pool, cmd->argv[1]);
854 conn->pass = pstrdup(conn_pool, cmd->argv[2]);
855
856 info = cmd->argv[3];
857
858 db = pstrdup(cmd->tmp_pool, info);
859
860 have_host = strchr(db, '@');
861 have_port = strchr(db, ':');
862
863 /* If have_port, parse it, otherwise default it.
864 * If have_port, set it to '\0'.
865 *
866 * If have_host, parse it, otherwise default it.
867 * If have_host, set it to '\0'.
868 */
869
870 if (have_port != NULL) {
871 port = have_port + 1;
872 *have_port = '\0';
873
874 } else {
875 port = _MYSQL_PORT;
876 }
877
878 if (have_host != NULL) {
879 host = have_host + 1;
880 *have_host = '\0';
881
882 } else {
883 host = "localhost";
884 }
885
886 /* Hack to support ability to configure path to Unix domain socket
887 * for MySQL: if the host string starts with a '/', assume it's
888 * a path to the Unix domain socket to use.
889 */
890 if (*host == '/') {
891 conn->unix_sock = pstrdup(conn_pool, host);
892
893 } else {
894 conn->host = pstrdup(conn_pool, host);
895 }
896
897 conn->db = pstrdup(conn_pool, db);
898 conn->port = pstrdup(conn_pool, port);
899
900 /* SSL parameters, if configured. */
901 if (cmd->argc >= 6) {
902 ssl_cert_file = cmd->argv[5];
903 if (ssl_cert_file != NULL) {
904 conn->ssl_cert_file = pstrdup(conn_pool, ssl_cert_file);
905 }
906 }
907
908 if (cmd->argc >= 7) {
909 ssl_key_file = cmd->argv[6];
910 if (ssl_key_file != NULL) {
911 conn->ssl_key_file = pstrdup(conn_pool, ssl_key_file);
912 }
913 }
914
915 if (cmd->argc >= 8) {
916 ssl_ca_file = cmd->argv[7];
917 if (ssl_ca_file != NULL) {
918 conn->ssl_ca_file = pstrdup(conn_pool, ssl_ca_file);
919 }
920 }
921
922 if (cmd->argc >= 9) {
923 ssl_ca_dir = cmd->argv[8];
924 if (ssl_ca_dir != NULL) {
925 conn->ssl_ca_dir = pstrdup(conn_pool, ssl_ca_dir);
926 }
927 }
928
929 if (cmd->argc >= 10) {
930 ssl_ciphers = cmd->argv[9];
931 if (ssl_ciphers != NULL) {
932 conn->ssl_ciphers = pstrdup(conn_pool, ssl_ciphers);
933 }
934 }
935
936 entry = sql_add_connection(conn_pool, name, (void *) conn);
937 if (entry == NULL &&
938 errno == EEXIST) {
939 /* Log only connections named other than "default", for debugging
940 * misconfigurations with multiple different SQLNamedConnectInfo
941 * directives using the same name.
942 */
943 if (strcmp(name, "default") != 0) {
944 sql_log(DEBUG_FUNC, "named connection '%s' already exists", name);
945 }
946
947 entry = sql_get_connection(name);
948 }
949
950 if (entry == NULL) {
951 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_defineconnection");
952 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
953 "error adding named connection");
954 }
955
956 if (cmd->argc >= 5) {
957 entry->ttl = (int) strtol(cmd->argv[4], (char **) NULL, 10);
958 if (entry->ttl >= 1) {
959 pr_sql_conn_policy = SQL_CONN_POLICY_TIMER;
960
961 } else {
962 entry->ttl = 0;
963 }
964 }
965
966 entry->timer = 0;
967 entry->connections = 0;
968
969 sql_log(DEBUG_INFO, " name: '%s'", entry->name);
970 sql_log(DEBUG_INFO, " user: '%s'", conn->user);
971
972 if (conn->host != NULL) {
973 sql_log(DEBUG_INFO, " host: '%s'", conn->host);
974
975 } else if (conn->unix_sock != NULL) {
976 sql_log(DEBUG_INFO, "socket: '%s'", conn->unix_sock);
977 }
978
979 sql_log(DEBUG_INFO, " db: '%s'", conn->db);
980 sql_log(DEBUG_INFO, " port: '%s'", conn->port);
981 sql_log(DEBUG_INFO, " ttl: '%d'", entry->ttl);
982
983 if (conn->ssl_cert_file != NULL) {
984 sql_log(DEBUG_INFO, " ssl: client cert = '%s'", conn->ssl_cert_file);
985 }
986
987 if (conn->ssl_key_file != NULL) {
988 sql_log(DEBUG_INFO, " ssl: client key = '%s'", conn->ssl_key_file);
989 }
990
991 if (conn->ssl_ca_file != NULL) {
992 sql_log(DEBUG_INFO, " ssl: CA file = '%s'", conn->ssl_ca_file);
993 }
994
995 if (conn->ssl_ca_dir != NULL) {
996 sql_log(DEBUG_INFO, " ssl: CA dir = '%s'", conn->ssl_ca_dir);
997 }
998
999 if (conn->ssl_ciphers != NULL) {
1000 sql_log(DEBUG_INFO, " ssl: ciphers = '%s'", conn->ssl_ciphers);
1001 }
1002
1003 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_defineconnection");
1004 return PR_HANDLED(cmd);
1005 }
1006
1007 /*
1008 * cmd_exit: closes all open connections.
1009 *
1010 * Inputs:
1011 * None
1012 *
1013 * Returns:
1014 * A simple non-error modret_t.
1015 */
cmd_exit(cmd_rec * cmd)1016 static modret_t *cmd_exit(cmd_rec *cmd) {
1017 register unsigned int i = 0;
1018
1019 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_exit");
1020
1021 for (i = 0; i < conn_cache->nelts; i++) {
1022 conn_entry_t *entry;
1023
1024 entry = ((conn_entry_t **) conn_cache->elts)[i];
1025 if (entry->connections > 0) {
1026 cmd_rec *close_cmd;
1027
1028 close_cmd = sql_make_cmd(conn_pool, 2, entry->name, "1");
1029 cmd_close(close_cmd);
1030 destroy_pool(close_cmd->pool);
1031 }
1032 }
1033
1034 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_exit");
1035 return PR_HANDLED(cmd);
1036 }
1037
1038 /*
1039 * cmd_select: executes a SELECT query. properly constructing the query
1040 * based on the inputs. See mod_sql.h for the definition of the _sql_data
1041 * structure which is used to return the result data.
1042 *
1043 * cmd_select takes either exactly two inputs, or more than two. If only
1044 * two inputs are given, the second is a monolithic query string. See
1045 * the examples below.
1046 *
1047 * Inputs:
1048 * cmd->argv[0]: connection name
1049 * cmd->argv[1]: table
1050 * cmd->argv[2]: select string
1051 * Optional:
1052 * cmd->argv[3]: where clause
1053 * cmd->argv[4]: requested number of return rows (LIMIT)
1054 *
1055 * etc. : other options, such as "GROUP BY", "ORDER BY",
1056 * and "DISTINCT" will start at cmd->arg[5]. All
1057 * backends MUST support 'DISTINCT', the other
1058 * arguments are optional (but encouraged).
1059 *
1060 * Returns:
1061 * either a properly filled error modret_t if the select failed, or a
1062 * modret_t with the result data filled in.
1063 *
1064 * Example:
1065 * These are example queries that would be executed for MySQL; other
1066 * backends will have different SQL syntax.
1067 *
1068 * argv[] = "default","user","userid, count", "userid='aah'","2"
1069 * query = "SELECT userid, count FROM user WHERE userid='aah' LIMIT 2"
1070 *
1071 * argv[] = "default","usr1, usr2","usr1.foo, usr2.bar"
1072 * query = "SELECT usr1.foo, usr2.bar FROM usr1, usr2"
1073 *
1074 * argv[] = "default","usr1","foo",,,"DISTINCT"
1075 * query = "SELECT DISTINCT foo FROM usr1"
1076 *
1077 * argv[] = "default","bar FROM usr1 WHERE tmp=1 ORDER BY bar"
1078 * query = "SELECT bar FROM usr1 WHERE tmp=1 ORDER BY bar"
1079 *
1080 * Notes:
1081 * certain selects could return huge amounts of data. do whatever is
1082 * possible to minimize the amount of data copying here.
1083 */
cmd_select(cmd_rec * cmd)1084 MODRET cmd_select(cmd_rec *cmd) {
1085 conn_entry_t *entry = NULL;
1086 db_conn_t *conn = NULL;
1087 modret_t *cmr = NULL;
1088 modret_t *dmr = NULL;
1089 char *query = NULL;
1090 cmd_rec *close_cmd;
1091
1092 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_select");
1093
1094 sql_check_cmd(cmd, "cmd_select");
1095
1096 if (cmd->argc < 2) {
1097 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_select");
1098 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
1099 }
1100
1101 entry = sql_get_connection(cmd->argv[0]);
1102 if (entry == NULL) {
1103 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_select");
1104 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
1105 pstrcat(cmd->tmp_pool, "unknown named connection: ", cmd->argv[0], NULL));
1106 }
1107
1108 conn = (db_conn_t *) entry->data;
1109
1110 cmr = cmd_open(cmd);
1111 if (MODRET_ERROR(cmr)) {
1112 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_select");
1113 return cmr;
1114 }
1115
1116 /* construct the query string */
1117 if (cmd->argc == 2) {
1118 query = pstrcat(cmd->tmp_pool, "SELECT ", cmd->argv[1], NULL);
1119
1120 } else {
1121 /* Make sure to properly quote the table name, as it might be a reserved
1122 * keyword; see Issue #1212.
1123 */
1124 query = pstrcat(cmd->tmp_pool, cmd->argv[2], " FROM `", cmd->argv[1], "`",
1125 NULL);
1126
1127 if (cmd->argc > 3 &&
1128 cmd->argv[3]) {
1129 query = pstrcat(cmd->tmp_pool, query, " WHERE ", cmd->argv[3], NULL);
1130 }
1131
1132 if (cmd->argc > 4 &&
1133 cmd->argv[4]) {
1134 query = pstrcat(cmd->tmp_pool, query, " LIMIT ", cmd->argv[4], NULL);
1135 }
1136
1137 if (cmd->argc > 5) {
1138 register unsigned int i;
1139
1140 /* Handle the optional arguments -- they're rare, so in this case
1141 * we'll play with the already constructed query string, but in
1142 * general we should probably take optional arguments into account
1143 * and put the query string together later once we know what they are.
1144 */
1145 for (i = 5; i < cmd->argc; i++) {
1146 if (cmd->argv[i] != NULL &&
1147 strcasecmp("DISTINCT", cmd->argv[i]) == 0) {
1148 query = pstrcat(cmd->tmp_pool, "DISTINCT ", query, NULL);
1149 }
1150 }
1151 }
1152
1153 query = pstrcat(cmd->tmp_pool, "SELECT ", query, NULL);
1154 }
1155
1156 /* Log the query string */
1157 sql_log(DEBUG_INFO, "query \"%s\"", query);
1158
1159 /* Perform the query. if it doesn't work, log the error, close the
1160 * connection then return the error from the query processing.
1161 */
1162 if (mysql_real_query(conn->mysql, query, strlen(query)) != 0) {
1163 dmr = build_error(cmd, conn);
1164
1165 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1166 cmd_close(close_cmd);
1167 SQL_FREE_CMD(close_cmd);
1168
1169 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_select");
1170 return dmr;
1171 }
1172
1173 /* Get the data. if it doesn't work, log the error, close the
1174 * connection then return the error from the data processing.
1175 */
1176 dmr = build_data(cmd, conn);
1177 if (MODRET_ERROR(dmr)) {
1178 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_select");
1179
1180 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1181 cmd_close(close_cmd);
1182 SQL_FREE_CMD(close_cmd);
1183
1184 return dmr;
1185 }
1186
1187 /* close the connection, return the data. */
1188 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1189 cmd_close(close_cmd);
1190 SQL_FREE_CMD(close_cmd);
1191
1192 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_select");
1193 return dmr;
1194 }
1195
1196 /*
1197 * cmd_insert: executes an INSERT query, properly constructing the query
1198 * based on the inputs.
1199 *
1200 * cmd_insert takes either exactly two inputs, or exactly four. If only
1201 * two inputs are given, the second is a monolithic query string. See
1202 * the examples below.
1203 *
1204 * Inputs:
1205 * cmd->argv[0]: connection name
1206 * cmd->argv[1]: table
1207 * cmd->argv[2]: field string
1208 * cmd->argv[3]: value string
1209 *
1210 * Returns:
1211 * either a properly filled error modret_t if the insert failed, or a
1212 * simple non-error modret_t.
1213 *
1214 * Example:
1215 * These are example queries that would be executed for MySQL; other
1216 * backends will have different SQL syntax.
1217 *
1218 * argv[] = "default","log","userid, date, count", "'aah', now(), 2"
1219 * query = "INSERT INTO log (userid, date, count) VALUES ('aah', now(), 2)"
1220 *
1221 * argv[] = "default"," INTO foo VALUES ('do','re','mi','fa')"
1222 * query = "INSERT INTO foo VALUES ('do','re','mi','fa')"
1223 *
1224 * Notes:
1225 * none
1226 */
cmd_insert(cmd_rec * cmd)1227 MODRET cmd_insert(cmd_rec *cmd) {
1228 conn_entry_t *entry = NULL;
1229 db_conn_t *conn = NULL;
1230 modret_t *cmr = NULL;
1231 modret_t *dmr = NULL;
1232 char *query = NULL;
1233 cmd_rec *close_cmd;
1234
1235 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_insert");
1236
1237 sql_check_cmd(cmd, "cmd_insert");
1238
1239 if ((cmd->argc != 2) && (cmd->argc != 4)) {
1240 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_insert");
1241 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
1242 }
1243
1244 entry = sql_get_connection(cmd->argv[0]);
1245 if (entry == NULL) {
1246 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_insert");
1247 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
1248 pstrcat(cmd->tmp_pool, "unknown named connection: ", cmd->argv[0], NULL));
1249 }
1250
1251 conn = (db_conn_t *) entry->data;
1252
1253 cmr = cmd_open(cmd);
1254 if (MODRET_ERROR(cmr)) {
1255 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_insert");
1256 return cmr;
1257 }
1258
1259 /* construct the query string */
1260 if (cmd->argc == 2) {
1261 query = pstrcat(cmd->tmp_pool, "INSERT ", cmd->argv[1], NULL);
1262
1263 } else {
1264 /* Make sure to properly quote the table name, as it might be a reserved
1265 * keyword; see Issue #1212.
1266 */
1267 query = pstrcat(cmd->tmp_pool, "INSERT INTO `", cmd->argv[1], "` (",
1268 cmd->argv[2], ") VALUES (", cmd->argv[3], ")", NULL);
1269 }
1270
1271 sql_log(DEBUG_INFO, "query \"%s\"", query);
1272
1273 /* perform the query. if it doesn't work, log the error, close the
1274 * connection (and log any errors there, too) then return the error
1275 * from the query processing.
1276 */
1277 if (mysql_real_query(conn->mysql, query, strlen(query)) != 0) {
1278 dmr = build_error(cmd, conn);
1279
1280 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1281 cmd_close(close_cmd);
1282 SQL_FREE_CMD(close_cmd);
1283
1284 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_insert");
1285 return dmr;
1286 }
1287
1288 /* close the connection and return HANDLED. */
1289 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1290 cmd_close(close_cmd);
1291 SQL_FREE_CMD(close_cmd);
1292
1293 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_insert");
1294 return PR_HANDLED(cmd);
1295 }
1296
1297 /*
1298 * cmd_update: executes an UPDATE query, properly constructing the query
1299 * based on the inputs.
1300 *
1301 * cmd_update takes either exactly two, three, or four inputs. If only
1302 * two inputs are given, the second is a monolithic query string. See
1303 * the examples below.
1304 *
1305 * Inputs:
1306 * cmd->argv[0]: connection name
1307 * cmd->argv[1]: table
1308 * cmd->argv[2]: update string
1309 * Optional:
1310 * cmd->argv[3]: where string
1311 *
1312 * Returns:
1313 * either a properly filled error modret_t if the update failed, or a
1314 * simple non-error modret_t. *
1315 *
1316 * Example:
1317 * These are example queries that would be executed for MySQL; other
1318 * backends will have different SQL syntax.
1319 *
1320 * argv[] = "default","user","count=count+1", "userid='joesmith'"
1321 * query = "UPDATE user SET count=count+1 WHERE userid='joesmith'"
1322 *
1323 * Notes:
1324 * argv[3] is optional -- it may be NULL, or it may not exist at all.
1325 * make sure this is handled correctly.
1326 */
cmd_update(cmd_rec * cmd)1327 MODRET cmd_update(cmd_rec *cmd) {
1328 conn_entry_t *entry = NULL;
1329 db_conn_t *conn = NULL;
1330 modret_t *cmr = NULL;
1331 modret_t *dmr = NULL;
1332 char *query = NULL;
1333 cmd_rec *close_cmd;
1334
1335 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_update");
1336
1337 sql_check_cmd(cmd, "cmd_update");
1338
1339 if ((cmd->argc < 2) || (cmd->argc > 4)) {
1340 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_update");
1341 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
1342 }
1343
1344 entry = sql_get_connection(cmd->argv[0]);
1345 if (entry == NULL) {
1346 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_update");
1347 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
1348 pstrcat(cmd->tmp_pool, "unknown named connection: ", cmd->argv[0], NULL));
1349 }
1350
1351 conn = (db_conn_t *) entry->data;
1352
1353 cmr = cmd_open(cmd);
1354 if (MODRET_ERROR(cmr)) {
1355 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_update");
1356 return cmr;
1357 }
1358
1359 if (cmd->argc == 2) {
1360 query = pstrcat(cmd->tmp_pool, "UPDATE ", cmd->argv[1], NULL);
1361
1362 } else {
1363 /* Make sure to properly quote the table name, as it might be a reserved
1364 * keyword; see Issue #1212.
1365 */
1366 query = pstrcat(cmd->tmp_pool, "UPDATE `", cmd->argv[1], "` SET ",
1367 cmd->argv[2], NULL);
1368 if (cmd->argc > 3 &&
1369 cmd->argv[3]) {
1370 query = pstrcat(cmd->tmp_pool, query, " WHERE ", cmd->argv[3], NULL);
1371 }
1372 }
1373
1374 /* Log the query string */
1375 sql_log(DEBUG_INFO, "query \"%s\"", query);
1376
1377 /* Perform the query. if it doesn't work close the connection, then
1378 * return the error from the query processing.
1379 */
1380 if (mysql_real_query(conn->mysql, query, strlen(query)) != 0) {
1381 dmr = build_error(cmd, conn);
1382
1383 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1384 cmd_close(close_cmd);
1385 SQL_FREE_CMD(close_cmd);
1386
1387 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_update");
1388 return dmr;
1389 }
1390
1391 /* Close the connection, return HANDLED. */
1392 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1393 cmd_close(close_cmd);
1394 SQL_FREE_CMD(close_cmd);
1395
1396 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_update");
1397 return PR_HANDLED(cmd);
1398 }
1399
1400 /*
1401 * cmd_procedure: executes a stored procedure.
1402 *
1403 * Inputs:
1404 * cmd->argv[0]: connection name
1405 * cmd->argv[1]: procedure name
1406 * cmd->argv[2]: procedure string
1407 *
1408 * Returns:
1409 * either a properly filled error modret_t if the procedure failed in
1410 * some way, or a modret_t with the result data. If a procedure
1411 * returns data, it should be returned in the same way as cmd_select.
1412 *
1413 * Notes:
1414 * not every backend will support stored procedures. Backends which do
1415 * not support stored procedures should return an error with a descriptive
1416 * error message (something like 'backend does not support procedures').
1417 */
cmd_procedure(cmd_rec * cmd)1418 MODRET cmd_procedure(cmd_rec *cmd) {
1419 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_procedure");
1420
1421 sql_check_cmd(cmd, "cmd_procedure");
1422
1423 if (cmd->argc != 3) {
1424 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_procedure");
1425 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
1426 }
1427
1428 /* MySQL does not support procedures. Nothing to do. */
1429
1430 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_procedure");
1431
1432 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
1433 "backend does not support procedures");
1434 }
1435
1436 /*
1437 * cmd_query: executes a freeform query string, with no syntax checking.
1438 *
1439 * cmd_query takes exactly two inputs, the connection and the query string.
1440 *
1441 * Inputs:
1442 * cmd->argv[0]: connection name
1443 * cmd->argv[1]: query string
1444 *
1445 * Returns:
1446 * depending on the query type, returns a modret_t with data, a non-error
1447 * modret_t, or a properly filled error modret_t if the query failed.
1448 *
1449 * Example:
1450 * None. The query should be passed directly to the backend database.
1451 *
1452 * Notes:
1453 * None.
1454 */
cmd_query(cmd_rec * cmd)1455 MODRET cmd_query(cmd_rec *cmd) {
1456 conn_entry_t *entry = NULL;
1457 db_conn_t *conn = NULL;
1458 modret_t *cmr = NULL;
1459 modret_t *dmr = NULL;
1460 char *query = NULL;
1461 cmd_rec *close_cmd;
1462
1463 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_query");
1464
1465 sql_check_cmd(cmd, "cmd_query");
1466
1467 if (cmd->argc != 2) {
1468 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_query");
1469 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
1470 }
1471
1472 entry = sql_get_connection(cmd->argv[0]);
1473 if (entry == NULL) {
1474 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_query");
1475 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
1476 pstrcat(cmd->tmp_pool, "unknown named connection: ", cmd->argv[0], NULL));
1477 }
1478
1479 conn = (db_conn_t *) entry->data;
1480
1481 cmr = cmd_open(cmd);
1482 if (MODRET_ERROR(cmr)) {
1483 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_query");
1484 return cmr;
1485 }
1486
1487 query = pstrcat(cmd->tmp_pool, cmd->argv[1], NULL);
1488
1489 /* Log the query string */
1490 sql_log(DEBUG_INFO, "query \"%s\"", query);
1491
1492 /* Perform the query. if it doesn't work close the connection, then
1493 * return the error from the query processing.
1494 */
1495 if (mysql_real_query(conn->mysql, query, strlen(query)) != 0) {
1496 dmr = build_error(cmd, conn);
1497
1498 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1499 cmd_close(close_cmd);
1500 SQL_FREE_CMD(close_cmd);
1501
1502 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_query");
1503 return dmr;
1504 }
1505
1506 /* Get data if necessary. if it doesn't work, log the error, close the
1507 * connection then return the error from the data processing.
1508 */
1509
1510 if (mysql_field_count(conn->mysql) > 0) {
1511 dmr = build_data(cmd, conn);
1512 if (MODRET_ERROR(dmr)) {
1513 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_query");
1514 }
1515
1516 } else {
1517 dmr = PR_HANDLED(cmd);
1518 }
1519
1520 /* close the connection, return the data. */
1521 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1522 cmd_close(close_cmd);
1523 SQL_FREE_CMD(close_cmd);
1524
1525 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_query");
1526 return dmr;
1527 }
1528
1529 /*
1530 * cmd_escapestring: certain strings sent to a database should be properly
1531 * escaped -- for instance, quotes need to be escaped to insure that
1532 * a query string is properly formatted. cmd_escapestring does whatever
1533 * is necessary to escape the special characters in a string.
1534 *
1535 * Inputs:
1536 * cmd->argv[0]: connection name
1537 * cmd->argv[1]: string to escape
1538 *
1539 * Returns:
1540 * this command CANNOT fail. The return string is null-terminated and
1541 * stored in the data field of the modret_t structure.
1542 *
1543 * Notes:
1544 * Different languages may escape different characters in different ways.
1545 * A backend should handle this correctly, where possible. If there is
1546 * no client library function to do the string conversion, it is strongly
1547 * recommended that the backend module writer do whatever is necessry (read
1548 * the database documentation and figure it out) to do the conversion
1549 * themselves in this function.
1550 *
1551 * A backend MUST supply a working escapestring implementation. Simply
1552 * copying the data from argv[0] into the data field of the modret allows
1553 * for possible SQL injection attacks when this backend is used.
1554 */
cmd_escapestring(cmd_rec * cmd)1555 MODRET cmd_escapestring(cmd_rec * cmd) {
1556 conn_entry_t *entry = NULL;
1557 db_conn_t *conn = NULL;
1558 modret_t *cmr = NULL;
1559 char *unescaped = NULL;
1560 char *escaped = NULL;
1561 cmd_rec *close_cmd;
1562
1563 sql_log(DEBUG_FUNC, "%s", "entering \tmysql cmd_escapestring");
1564
1565 sql_check_cmd(cmd, "cmd_escapestring");
1566
1567 if (cmd->argc != 2) {
1568 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_escapestring");
1569 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION, "badly formed request");
1570 }
1571
1572 entry = sql_get_connection(cmd->argv[0]);
1573 if (entry == NULL) {
1574 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_escapestring");
1575 return PR_ERROR_MSG(cmd, MOD_SQL_MYSQL_VERSION,
1576 pstrcat(cmd->tmp_pool, "unknown named connection: ", cmd->argv[0], NULL));
1577 }
1578
1579 conn = (db_conn_t *) entry->data;
1580
1581 /* Make sure the connection is open. */
1582 cmr = cmd_open(cmd);
1583 if (MODRET_ERROR(cmr)) {
1584 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_escapestring");
1585 return cmr;
1586 }
1587
1588 unescaped = cmd->argv[1];
1589 escaped = (char *) pcalloc(cmd->tmp_pool, sizeof(char) *
1590 (strlen(unescaped) * 2) + 1);
1591
1592 /* Note: the mysql_real_escape_string() function appeared in the C API
1593 * as of MySQL 3.23.14; this macro allows functioning with older mysql
1594 * installations.
1595 */
1596 #if MYSQL_VERSION_ID >= 32314
1597 mysql_real_escape_string(conn->mysql, escaped, unescaped, strlen(unescaped));
1598 #else
1599 mysql_escape_string(escaped, unescaped, strlen(unescaped));
1600 #endif
1601
1602 close_cmd = sql_make_cmd(cmd->tmp_pool, 1, entry->name);
1603 cmd_close(close_cmd);
1604 SQL_FREE_CMD(close_cmd);
1605
1606 sql_log(DEBUG_FUNC, "%s", "exiting \tmysql cmd_escapestring");
1607 return mod_create_data(cmd, (void *) escaped);
1608 }
1609
1610 /* Per the MySQL docs for the PASSWORD function, MySQL pre-4.1 passwords
1611 * are always 16 bytes; MySQL 4.1 passwords are 41 bytes AND start with '*'.
1612 * See:
1613 * http://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password
1614 */
1615
1616 #define MYSQL_PASSWD_FMT_UNKNOWN -1
1617 #define MYSQL_PASSWD_FMT_PRE41 1
1618 #define MYSQL_PASSWD_FMT_41 2
1619 #define MYSQL_PASSWD_FMT_SHA256 3
1620
get_mysql_passwd_fmt(const char * txt,size_t txt_len)1621 static int get_mysql_passwd_fmt(const char *txt, size_t txt_len) {
1622 if (txt_len == 16) {
1623 return MYSQL_PASSWD_FMT_PRE41;
1624 }
1625
1626 if (txt_len == 41 &&
1627 txt[0] == '*') {
1628 return MYSQL_PASSWD_FMT_41;
1629 }
1630
1631 if (txt_len > 3 &&
1632 txt[0] == '$' &&
1633 txt[1] == '5' &&
1634 txt[2] == '$') {
1635 return MYSQL_PASSWD_FMT_SHA256;
1636 }
1637
1638 return MYSQL_PASSWD_FMT_UNKNOWN;
1639 }
1640
match_mysql_passwds(const char * hashed,size_t hashed_len,const char * scrambled,size_t scrambled_len,const char * scramble_func)1641 static int match_mysql_passwds(const char *hashed, size_t hashed_len,
1642 const char *scrambled, size_t scrambled_len, const char *scramble_func) {
1643 int hashed_fmt = 0, scrambled_fmt = 0, matched = FALSE;
1644
1645 if (pr_trace_get_level(trace_channel) >= 7) {
1646 const char *hashed_fmt_name, *scrambled_fmt_name;
1647
1648 hashed_fmt = get_mysql_passwd_fmt(hashed, hashed_len);
1649 scrambled_fmt = get_mysql_passwd_fmt(scrambled, scrambled_len);
1650
1651 switch (hashed_fmt) {
1652 case MYSQL_PASSWD_FMT_PRE41:
1653 hashed_fmt_name = "pre-4.1";
1654 break;
1655
1656 case MYSQL_PASSWD_FMT_41:
1657 hashed_fmt_name = "4.1";
1658 break;
1659
1660 case MYSQL_PASSWD_FMT_SHA256:
1661 hashed_fmt_name = "SHA256";
1662 break;
1663
1664 default:
1665 hashed_fmt_name = "unknown";
1666 break;
1667 }
1668
1669 switch (scrambled_fmt) {
1670 case MYSQL_PASSWD_FMT_PRE41:
1671 scrambled_fmt_name = "pre-4.1";
1672 break;
1673
1674 case MYSQL_PASSWD_FMT_41:
1675 scrambled_fmt_name = "4.1";
1676 break;
1677
1678 case MYSQL_PASSWD_FMT_SHA256:
1679 scrambled_fmt_name = "SHA256";
1680 break;
1681
1682 default:
1683 scrambled_fmt_name = "unknown";
1684 break;
1685 }
1686
1687 pr_trace_msg(trace_channel, 7,
1688 "SQLAuthType Backend: database password format = %s, "
1689 "client library password format = %s (using %s())", hashed_fmt_name,
1690 scrambled_fmt_name, scramble_func);
1691 }
1692
1693 /* Note here that if the scrambled value has a different length than our
1694 * expected hash, it might be a completely different format (i.e. not the
1695 * 4.1 or whatever format provided by the db). Log if this the case!
1696 *
1697 * Consider that using PASSWORD() on the server might make a 4.1 format
1698 * value, but the client lib might make a SHA256 format value. Or
1699 * vice versa.
1700 */
1701 if (scrambled_len == hashed_len) {
1702 matched = (strncmp(scrambled, hashed, hashed_len) == 0);
1703 }
1704
1705 if (matched == FALSE) {
1706 if (hashed_fmt == 0) {
1707 hashed_fmt = get_mysql_passwd_fmt(hashed, hashed_len);
1708 }
1709
1710 if (scrambled_fmt == 0) {
1711 scrambled_fmt = get_mysql_passwd_fmt(scrambled, scrambled_len);
1712 }
1713
1714 if (hashed_fmt != scrambled_fmt) {
1715 if (scrambled_fmt == MYSQL_PASSWD_FMT_SHA256) {
1716 sql_log(DEBUG_FUNC, "MySQL client library used MySQL SHA256 password format, and Backend SQLAuthType cannot succeed; consider using MD5/SHA1/SHA256 SQLAuthType using mod_sql_passwd");
1717 switch (hashed_fmt) {
1718 case MYSQL_PASSWD_FMT_PRE41:
1719 sql_log(DEBUG_FUNC, "MySQL server used MySQL pre-4.1 password format for PASSWORD() value");
1720 break;
1721
1722 case MYSQL_PASSWD_FMT_41:
1723 sql_log(DEBUG_FUNC, "MySQL server used MySQL 4.1 password format for PASSWORD() value");
1724 break;
1725
1726 default:
1727 pr_trace_msg(trace_channel, 19,
1728 "unknown MySQL PASSWORD() format used on server");
1729 break;
1730 }
1731 }
1732 }
1733
1734 pr_trace_msg(trace_channel, 9,
1735 "expected '%.*s' (%lu), got '%.*s' (%lu) using MySQL %s()",
1736 (int) hashed_len, hashed, (unsigned long) hashed_len,
1737 (int) scrambled_len, scrambled, (unsigned long) scrambled_len,
1738 scramble_func);
1739 }
1740
1741 return matched;
1742 }
1743
sql_mysql_password(cmd_rec * cmd,const char * plaintext,const char * ciphertext)1744 static modret_t *sql_mysql_password(cmd_rec *cmd, const char *plaintext,
1745 const char *ciphertext) {
1746 char scrambled[256] = {'\0'};
1747 size_t plaintext_len = 0, ciphertext_len = 0, scrambled_len = 0;
1748 int success = 0;
1749
1750 plaintext_len = strlen(plaintext);
1751 ciphertext_len = strlen(ciphertext);
1752
1753 /* Checking order (damn MySQL API changes):
1754 *
1755 * my_make_scrambled_password (if available)
1756 * my_make_scrambled_password_323 (if available)
1757 * make_scrambled_password (if available)
1758 * make_scrammbed_password_323 (if available)
1759 */
1760
1761 #if defined(HAVE_MYSQL_MY_MAKE_SCRAMBLED_PASSWORD)
1762 if (success == FALSE) {
1763 memset(scrambled, '\0', sizeof(scrambled));
1764
1765 my_make_scrambled_password(scrambled, plaintext, plaintext_len);
1766 scrambled_len = strlen(scrambled);
1767
1768 success = match_mysql_passwds(ciphertext, ciphertext_len, scrambled,
1769 scrambled_len, "my_make_scrambled_password");
1770 }
1771 #endif /* HAVE_MYSQL_MY_MAKE_SCRAMBLED_PASSWORD */
1772
1773 #if defined(HAVE_MYSQL_MY_MAKE_SCRAMBLED_PASSWORD_323)
1774 if (success == FALSE) {
1775 memset(scrambled, '\0', sizeof(scrambled));
1776
1777 sql_log(DEBUG_FUNC, "%s",
1778 "checking again using deprecated legacy MySQL password algorithm (my_make_scrambled_password_323 function)");
1779 sql_log(DEBUG_FUNC, "%s",
1780 "warning: support for this legacy MySQ-3.xL password algorithm will be dropped from MySQL in the future");
1781
1782 my_make_scrambled_password_323(scrambled, plaintext, plaintext_len);
1783 scrambled_len = strlen(scrambled);
1784
1785 success = match_mysql_passwds(ciphertext, ciphertext_len, scrambled,
1786 scrambled_len, "my_make_scrambled_password_323");
1787 }
1788 #endif /* HAVE_MYSQL_MY_MAKE_SCRAMBLED_PASSWORD_323 */
1789
1790 #if defined(HAVE_MYSQL_MAKE_SCRAMBLED_PASSWORD)
1791 if (success == FALSE) {
1792 memset(scrambled, '\0', sizeof(scrambled));
1793
1794 # if MYSQL_VERSION_ID >= 40100 && MYSQL_VERSION_ID < 40101
1795 make_scrambled_password(scrambled, plaintext, 1, NULL);
1796 # else
1797 make_scrambled_password(scrambled, plaintext);
1798 # endif
1799 scrambled_len = strlen(scrambled);
1800
1801 success = match_mysql_passwds(ciphertext, ciphertext_len, scrambled,
1802 scrambled_len, "make_scrambled_password");
1803 }
1804 #endif /* HAVE_MYSQL_MAKE_SCRAMBLED_PASSWORD */
1805
1806 #if defined(HAVE_MYSQL_MAKE_SCRAMBLED_PASSWORD_323)
1807 if (success == FALSE) {
1808 memset(scrambled, '\0', sizeof(scrambled));
1809
1810 sql_log(DEBUG_FUNC, "%s",
1811 "checking again using deprecated legacy MySQL password algorithm (make_scrambled_password_323 function)");
1812 sql_log(DEBUG_FUNC, "%s",
1813 "warning: support for this legacy MySQ-3.xL password algorithm will be dropped from MySQL in the future");
1814
1815 make_scrambled_password_323(scrambled, plaintext);
1816 scrambled_len = strlen(scrambled);
1817
1818 success = match_mysql_passwds(ciphertext, ciphertext_len, scrambled,
1819 scrambled_len, "make_scrambled_password_323");
1820 }
1821 #endif /* HAVE_MYSQL_MAKE_SCRAMBLED_PASSWORD_323 */
1822
1823 if (success == FALSE) {
1824 sql_log(DEBUG_FUNC, "%s", "password mismatch");
1825 }
1826
1827 return success ? PR_HANDLED(cmd) : PR_ERROR_INT(cmd, PR_AUTH_BADPWD);
1828 }
1829
1830 /*
1831 * cmd_identify: returns API information and an identification string for
1832 * the backend handler. mod_sql will call this at initialization and
1833 * display the identification string. The API version information is
1834 * used by mod_sql to identify available command handlers.
1835 *
1836 * Inputs:
1837 * None. The cmd->tmp_pool can be used to construct the return data, but
1838 * do not depend on any other portion of the cmd_rec to be useful in any way.
1839 *
1840 * Returns:
1841 * A sql_data_t of *exactly* this form:
1842 * sql_data_t->rnum = 1;
1843 * sql_data_t->fnum = 2;
1844 * sql_data_t->data[0] = "identification string"
1845 * sql_data_t->data[0] = "API version"
1846 *
1847 * Notes:
1848 * See mod_sql.h for currently accepted APIs.
1849 */
cmd_identify(cmd_rec * cmd)1850 MODRET cmd_identify(cmd_rec * cmd) {
1851 sql_data_t *sd = NULL;
1852
1853 sql_check_cmd(cmd, "cmd_identify");
1854
1855 sd = (sql_data_t *) pcalloc(cmd->tmp_pool, sizeof(sql_data_t));
1856 sd->data = (char **) pcalloc(cmd->tmp_pool, sizeof(char *) * 2);
1857
1858 sd->rnum = 1;
1859 sd->fnum = 2;
1860
1861 sd->data[0] = MOD_SQL_MYSQL_VERSION;
1862 sd->data[1] = MOD_SQL_API_V1;
1863
1864 return mod_create_data(cmd, (void *) sd);
1865 }
1866
1867 /*
1868 * cmd_prepare: prepares this mod_sql_mysql module for running.
1869 *
1870 * Inputs:
1871 * cmd->argv[0]: A pool to be used for any necessary preparations.
1872 *
1873 * Returns:
1874 * Success.
1875 */
cmd_prepare(cmd_rec * cmd)1876 MODRET cmd_prepare(cmd_rec *cmd) {
1877 if (cmd->argc != 1) {
1878 return PR_ERROR(cmd);
1879 }
1880
1881 conn_pool = (pool *) cmd->argv[0];
1882
1883 if (conn_cache == NULL) {
1884 conn_cache = make_array(conn_pool, DEF_CONN_POOL_SIZE,
1885 sizeof(conn_entry_t *));
1886 }
1887
1888 return mod_create_data(cmd, NULL);
1889 }
1890
1891 /*
1892 * cmd_cleanup: cleans up any initialisations made during module preparations
1893 * (see cmd_prepre).
1894 *
1895 * Inputs:
1896 * None.
1897 *
1898 * Returns:
1899 * Success.
1900 */
cmd_cleanup(cmd_rec * cmd)1901 MODRET cmd_cleanup(cmd_rec *cmd) {
1902 destroy_pool(conn_pool);
1903 conn_pool = NULL;
1904 conn_cache = NULL;
1905
1906 return mod_create_data(cmd, NULL);
1907 }
1908
1909 /* SQL cmdtable: mod_sql requires each backend module to define a cmdtable
1910 * with this exact name. ALL these functions must be defined; mod_sql checks
1911 * that they all exist on startup and ProFTPD will refuse to start if they
1912 * aren't defined.
1913 */
1914 static cmdtable sql_mysql_cmdtable[] = {
1915 { CMD, "sql_close", G_NONE, cmd_close, FALSE, FALSE },
1916 { CMD, "sql_cleanup", G_NONE, cmd_cleanup, FALSE, FALSE },
1917 { CMD, "sql_defineconnection", G_NONE, cmd_defineconnection, FALSE, FALSE },
1918 { CMD, "sql_escapestring", G_NONE, cmd_escapestring, FALSE, FALSE },
1919 { CMD, "sql_exit", G_NONE, cmd_exit, FALSE, FALSE },
1920 { CMD, "sql_identify", G_NONE, cmd_identify, FALSE, FALSE },
1921 { CMD, "sql_insert", G_NONE, cmd_insert, FALSE, FALSE },
1922 { CMD, "sql_open", G_NONE, cmd_open, FALSE, FALSE },
1923 { CMD, "sql_prepare", G_NONE, cmd_prepare, FALSE, FALSE },
1924 { CMD, "sql_procedure", G_NONE, cmd_procedure, FALSE, FALSE },
1925 { CMD, "sql_query", G_NONE, cmd_query, FALSE, FALSE },
1926 { CMD, "sql_select", G_NONE, cmd_select, FALSE, FALSE },
1927 { CMD, "sql_update", G_NONE, cmd_update, FALSE, FALSE },
1928
1929 { 0, NULL }
1930 };
1931
1932 /* Configuration handlers
1933 */
1934
set_sqlauthtypes(cmd_rec * cmd)1935 MODRET set_sqlauthtypes(cmd_rec *cmd) {
1936 #if MYSQL_VERSION_ID >= 50600 && \
1937 !defined(HAVE_MYSQL_MAKE_SCRAMBLED_PASSWORD) && \
1938 !defined(HAVE_MYSQL_MAKE_SCRAMBLED_PASSWORD_323) && \
1939 !defined(HAVE_MYSQL_MY_MAKE_SCRAMBLED_PASSWORD_323)
1940 register unsigned int i;
1941
1942 /* If we are using MySQL 5.6.x or later, AND we only have the
1943 * my_make_scrambled_password() MySQL function available, AND the Backend
1944 * SQLAuthType is used, then we must fail the directive; see Bug#4281.
1945 */
1946
1947 for (i = 1; i < cmd->argc; i++) {
1948 const char *auth_type;
1949
1950 auth_type = cmd->argv[i];
1951 if (strcasecmp(auth_type, "Backend") == 0) {
1952 pr_log_pri(PR_LOG_NOTICE, "%s: WARNING: MySQL client library uses MySQL SHA256 password format, and Backend SQLAuthType cannot succeed; consider using MD5/SHA1/SHA256 SQLAuthType using mod_sql_passwd", (char *) cmd->argv[0]);
1953 break;
1954 }
1955 }
1956 #endif
1957
1958 return PR_DECLINED(cmd);
1959 }
1960
1961 /* Event handlers
1962 */
1963
sql_mysql_mod_load_ev(const void * event_data,void * user_data)1964 static void sql_mysql_mod_load_ev(const void *event_data, void *user_data) {
1965
1966 if (strcmp("mod_sql_mysql.c", (const char *) event_data) == 0) {
1967 /* Register ourselves with mod_sql. */
1968 if (sql_register_backend("mysql", sql_mysql_cmdtable) < 0) {
1969 pr_log_pri(PR_LOG_NOTICE, MOD_SQL_MYSQL_VERSION
1970 ": notice: error registering backend: %s", strerror(errno));
1971 pr_session_end(0);
1972 }
1973 }
1974 }
1975
sql_mysql_mod_unload_ev(const void * event_data,void * user_data)1976 static void sql_mysql_mod_unload_ev(const void *event_data, void *user_data) {
1977 if (strcmp("mod_sql_mysql.c", (const char *) event_data) == 0) {
1978 /* Unregister ourselves from all events. */
1979 pr_event_unregister(&sql_mysql_module, NULL, NULL);
1980
1981 /* Unegister ourselves with mod_sql. */
1982 (void) sql_unregister_authtype("Backend");
1983
1984 if (sql_unregister_backend("mysql") < 0) {
1985 pr_log_pri(PR_LOG_NOTICE, MOD_SQL_MYSQL_VERSION
1986 ": notice: error unregistering backend: %s", strerror(errno));
1987 pr_session_end(0);
1988 }
1989 }
1990 }
1991
1992 /* Initialization routines
1993 */
1994
sql_mysql_init(void)1995 static int sql_mysql_init(void) {
1996
1997 /* Register listeners for the load and unload events. */
1998 pr_event_register(&sql_mysql_module, "core.module-load",
1999 sql_mysql_mod_load_ev, NULL);
2000 pr_event_register(&sql_mysql_module, "core.module-unload",
2001 sql_mysql_mod_unload_ev, NULL);
2002
2003 /* Register our auth handler. */
2004 (void) sql_register_authtype("Backend", sql_mysql_password);
2005 return 0;
2006 }
2007
sql_mysql_sess_init(void)2008 static int sql_mysql_sess_init(void) {
2009 if (conn_pool != NULL) {
2010 destroy_pool(conn_pool);
2011 conn_cache = NULL;
2012 }
2013
2014 conn_pool = make_sub_pool(session.pool);
2015 pr_pool_tag(conn_pool, "MySQL connection pool");
2016
2017 if (conn_cache == NULL) {
2018 conn_cache = make_array(conn_pool, DEF_CONN_POOL_SIZE,
2019 sizeof(conn_entry_t *));
2020 }
2021
2022 return 0;
2023 }
2024
2025 static conftable sql_mysql_conftab[] = {
2026 { "SQLAuthTypes", set_sqlauthtypes, NULL },
2027
2028 { NULL, NULL, NULL }
2029 };
2030
2031 /* sql_mysql_module: The standard module struct for all ProFTPD modules.
2032 * We use the pre-fork handler to initialize the conn_cache array header.
2033 * Other backend modules may not need any init functions, or may need
2034 * to extend the init functions to initialize other internal variables.
2035 */
2036 module sql_mysql_module = {
2037 /* Always NULL */
2038 NULL, NULL,
2039
2040 /* Module API version */
2041 0x20,
2042
2043 /* Module name */
2044 "sql_mysql",
2045
2046 /* Module configuration directive handlers */
2047 sql_mysql_conftab,
2048
2049 /* Module command handlers */
2050 NULL,
2051
2052 /* Module authentication handlers */
2053 NULL,
2054
2055 /* Module initialization */
2056 sql_mysql_init,
2057
2058 /* Session initialization */
2059 sql_mysql_sess_init,
2060
2061 /* Module version */
2062 MOD_SQL_MYSQL_VERSION
2063 };
2064