1 /* $NetBSD: dict_sqlite.c,v 1.2 2017/02/14 01:16:45 christos Exp $ */ 2 3 /*++ 4 /* NAME 5 /* dict_sqlite 3 6 /* SUMMARY 7 /* dictionary manager interface to SQLite3 databases 8 /* SYNOPSIS 9 /* #include <dict_sqlite.h> 10 /* 11 /* DICT *dict_sqlite_open(name, open_flags, dict_flags) 12 /* const char *name; 13 /* int open_flags; 14 /* int dict_flags; 15 /* DESCRIPTION 16 /* dict_sqlite_open() creates a dictionary of type 'sqlite'. 17 /* This dictionary is an interface for the postfix key->value 18 /* mappings to SQLite. The result is a pointer to the installed 19 /* dictionary. 20 /* .PP 21 /* Arguments: 22 /* .IP name 23 /* Either the path to the SQLite configuration file (if it 24 /* starts with '/' or '.'), or the prefix which will be used 25 /* to obtain main.cf configuration parameters for this search. 26 /* 27 /* In the first case, the configuration parameters below are 28 /* specified in the file as \fIname\fR=\fIvalue\fR pairs. 29 /* 30 /* In the second case, the configuration parameters are prefixed 31 /* with the value of \fIname\fR and an underscore, and they 32 /* are specified in main.cf. For example, if this value is 33 /* \fIsqlitecon\fR, the parameters would look like 34 /* \fIsqlitecon_dbpath\fR, \fIsqlitecon_query\fR, and so on. 35 /* .IP open_flags 36 /* Must be O_RDONLY. 37 /* .IP dict_flags 38 /* See dict_open(3). 39 /* .PP 40 /* Configuration parameters: 41 /* .IP dbpath 42 /* Path to SQLite database 43 /* .IP query 44 /* Query template. Before the query is actually issued, variable 45 /* substitutions are performed. See sqlite_table(5) for details. 46 /* .IP result_format 47 /* The format used to expand results from queries. Substitutions 48 /* are performed as described in sqlite_table(5). Defaults to 49 /* returning the lookup result unchanged. 50 /* .IP expansion_limit 51 /* Limit (if any) on the total number of lookup result values. 52 /* Lookups which exceed the limit fail with dict->error=DICT_ERR_RETRY. 53 /* Note that each non-empty (and non-NULL) column of a 54 /* multi-column result row counts as one result. 55 /* .IP "select_field, where_field, additional_conditions" 56 /* Legacy query interface. 57 /* SEE ALSO 58 /* dict(3) generic dictionary manager 59 /* AUTHOR(S) 60 /* Axel Steiner 61 /* ast@treibsand.com 62 /* 63 /* Adopted and updated by: 64 /* Wietse Venema 65 /* IBM T.J. Watson Research 66 /* P.O. Box 704 67 /* Yorktown Heights, NY 10598, USA 68 /*--*/ 69 70 /* System library. */ 71 72 #include <sys_defs.h> 73 #include <string.h> 74 75 #ifdef HAS_SQLITE 76 #include <sqlite3.h> 77 78 #if !defined(SQLITE_VERSION_NUMBER) || (SQLITE_VERSION_NUMBER < 3005004) 79 #define sqlite3_prepare_v2 sqlite3_prepare 80 #endif 81 82 /* Utility library. */ 83 84 #include <msg.h> 85 #include <dict.h> 86 #include <vstring.h> 87 #include <stringops.h> 88 #include <mymalloc.h> 89 90 /* Global library. */ 91 92 #include <cfg_parser.h> 93 #include <db_common.h> 94 95 /* Application-specific. */ 96 97 #include <dict_sqlite.h> 98 99 typedef struct { 100 DICT dict; /* generic member */ 101 CFG_PARSER *parser; /* common parameter parser */ 102 sqlite3 *db; /* sqlite handle */ 103 char *query; /* db_common_expand() query */ 104 char *result_format; /* db_common_expand() result_format */ 105 void *ctx; /* db_common_parse() context */ 106 char *dbpath; /* dbpath config attribute */ 107 int expansion_limit; /* expansion_limit config attribute */ 108 } DICT_SQLITE; 109 110 /* dict_sqlite_quote - escape SQL metacharacters in input string */ 111 112 static void dict_sqlite_quote(DICT *dict, const char *raw_text, VSTRING *result) 113 { 114 char *quoted_text; 115 116 quoted_text = sqlite3_mprintf("%q", raw_text); 117 /* Fix 20100616 */ 118 if (quoted_text == 0) 119 msg_fatal("dict_sqlite_quote: out of memory"); 120 vstring_strcat(result, quoted_text); 121 sqlite3_free(quoted_text); 122 } 123 124 /* dict_sqlite_close - close the database */ 125 126 static void dict_sqlite_close(DICT *dict) 127 { 128 const char *myname = "dict_sqlite_close"; 129 DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict; 130 131 if (msg_verbose) 132 msg_info("%s: %s", myname, dict_sqlite->parser->name); 133 134 if (sqlite3_close(dict_sqlite->db) != SQLITE_OK) 135 msg_fatal("%s: close %s failed", myname, dict_sqlite->parser->name); 136 cfg_parser_free(dict_sqlite->parser); 137 myfree(dict_sqlite->dbpath); 138 myfree(dict_sqlite->query); 139 myfree(dict_sqlite->result_format); 140 if (dict_sqlite->ctx) 141 db_common_free_ctx(dict_sqlite->ctx); 142 if (dict->fold_buf) 143 vstring_free(dict->fold_buf); 144 dict_free(dict); 145 } 146 147 /* dict_sqlite_lookup - find database entry */ 148 149 static const char *dict_sqlite_lookup(DICT *dict, const char *name) 150 { 151 const char *myname = "dict_sqlite_lookup"; 152 DICT_SQLITE *dict_sqlite = (DICT_SQLITE *) dict; 153 sqlite3_stmt *sql_stmt; 154 const char *query_remainder; 155 static VSTRING *query; 156 static VSTRING *result; 157 const char *retval; 158 int expansion = 0; 159 int status; 160 int domain_rc; 161 162 /* 163 * In case of return without lookup (skipped key, etc.). 164 */ 165 dict->error = 0; 166 167 /* 168 * Don't frustrate future attempts to make Postfix UTF-8 transparent. 169 */ 170 if ((dict->flags & DICT_FLAG_UTF8_ACTIVE) == 0 171 && !valid_utf8_string(name, strlen(name))) { 172 if (msg_verbose) 173 msg_info("%s: %s: Skipping lookup of non-UTF-8 key '%s'", 174 myname, dict_sqlite->parser->name, name); 175 return (0); 176 } 177 178 /* 179 * Optionally fold the key. Folding may be enabled on on-the-fly. 180 */ 181 if (dict->flags & DICT_FLAG_FOLD_FIX) { 182 if (dict->fold_buf == 0) 183 dict->fold_buf = vstring_alloc(100); 184 vstring_strcpy(dict->fold_buf, name); 185 name = lowercase(vstring_str(dict->fold_buf)); 186 } 187 188 /* 189 * Apply the optional domain filter for email address lookups. 190 */ 191 if ((domain_rc = db_common_check_domain(dict_sqlite->ctx, name)) == 0) { 192 if (msg_verbose) 193 msg_info("%s: %s: Skipping lookup of '%s'", 194 myname, dict_sqlite->parser->name, name); 195 return (0); 196 } 197 if (domain_rc < 0) 198 DICT_ERR_VAL_RETURN(dict, domain_rc, (char *) 0); 199 200 /* 201 * Expand the query and query the database. 202 */ 203 #define INIT_VSTR(buf, len) do { \ 204 if (buf == 0) \ 205 buf = vstring_alloc(len); \ 206 VSTRING_RESET(buf); \ 207 VSTRING_TERMINATE(buf); \ 208 } while (0) 209 210 INIT_VSTR(query, 10); 211 212 if (!db_common_expand(dict_sqlite->ctx, dict_sqlite->query, 213 name, 0, query, dict_sqlite_quote)) 214 return (0); 215 216 if (msg_verbose) 217 msg_info("%s: %s: Searching with query %s", 218 myname, dict_sqlite->parser->name, vstring_str(query)); 219 220 if (sqlite3_prepare_v2(dict_sqlite->db, vstring_str(query), -1, 221 &sql_stmt, &query_remainder) != SQLITE_OK) 222 msg_fatal("%s: %s: SQL prepare failed: %s\n", 223 myname, dict_sqlite->parser->name, 224 sqlite3_errmsg(dict_sqlite->db)); 225 226 if (*query_remainder && msg_verbose) 227 msg_info("%s: %s: Ignoring text at end of query: %s", 228 myname, dict_sqlite->parser->name, query_remainder); 229 230 /* 231 * Retrieve and expand the result(s). 232 */ 233 INIT_VSTR(result, 10); 234 while ((status = sqlite3_step(sql_stmt)) != SQLITE_DONE) { 235 if (status == SQLITE_ROW) { 236 if (db_common_expand(dict_sqlite->ctx, dict_sqlite->result_format, 237 (const char *) sqlite3_column_text(sql_stmt, 0), 238 name, result, 0) 239 && dict_sqlite->expansion_limit > 0 240 && ++expansion > dict_sqlite->expansion_limit) { 241 msg_warn("%s: %s: Expansion limit exceeded for key '%s'", 242 myname, dict_sqlite->parser->name, name); 243 dict->error = DICT_ERR_RETRY; 244 break; 245 } 246 } 247 /* Fix 20100616 */ 248 else { 249 msg_warn("%s: %s: SQL step failed for query '%s': %s\n", 250 myname, dict_sqlite->parser->name, 251 vstring_str(query), sqlite3_errmsg(dict_sqlite->db)); 252 dict->error = DICT_ERR_RETRY; 253 break; 254 } 255 } 256 257 /* 258 * Clean up. 259 */ 260 if (sqlite3_finalize(sql_stmt)) 261 msg_fatal("%s: %s: SQL finalize failed for query '%s': %s\n", 262 myname, dict_sqlite->parser->name, 263 vstring_str(query), sqlite3_errmsg(dict_sqlite->db)); 264 265 return ((dict->error == 0 && *(retval = vstring_str(result)) != 0) ? 266 retval : 0); 267 } 268 269 /* sqlite_parse_config - parse sqlite configuration file */ 270 271 static void sqlite_parse_config(DICT_SQLITE *dict_sqlite, const char *sqlitecf) 272 { 273 VSTRING *buf; 274 275 /* 276 * Parse the primary configuration parameters, and emulate the legacy 277 * query interface if necessary. This simplifies migration from one SQL 278 * database type to another. 279 */ 280 dict_sqlite->dbpath = cfg_get_str(dict_sqlite->parser, "dbpath", "", 1, 0); 281 dict_sqlite->query = cfg_get_str(dict_sqlite->parser, "query", NULL, 0, 0); 282 if (dict_sqlite->query == 0) { 283 buf = vstring_alloc(100); 284 db_common_sql_build_query(buf, dict_sqlite->parser); 285 dict_sqlite->query = vstring_export(buf); 286 } 287 dict_sqlite->result_format = 288 cfg_get_str(dict_sqlite->parser, "result_format", "%s", 1, 0); 289 dict_sqlite->expansion_limit = 290 cfg_get_int(dict_sqlite->parser, "expansion_limit", 0, 0, 0); 291 292 /* 293 * Parse the query / result templates and the optional domain filter. 294 */ 295 dict_sqlite->ctx = 0; 296 (void) db_common_parse(&dict_sqlite->dict, &dict_sqlite->ctx, 297 dict_sqlite->query, 1); 298 (void) db_common_parse(0, &dict_sqlite->ctx, dict_sqlite->result_format, 0); 299 db_common_parse_domain(dict_sqlite->parser, dict_sqlite->ctx); 300 301 /* 302 * Maps that use substring keys should only be used with the full input 303 * key. 304 */ 305 if (db_common_dict_partial(dict_sqlite->ctx)) 306 dict_sqlite->dict.flags |= DICT_FLAG_PATTERN; 307 else 308 dict_sqlite->dict.flags |= DICT_FLAG_FIXED; 309 } 310 311 /* dict_sqlite_open - open sqlite database */ 312 313 DICT *dict_sqlite_open(const char *name, int open_flags, int dict_flags) 314 { 315 DICT_SQLITE *dict_sqlite; 316 CFG_PARSER *parser; 317 318 /* 319 * Sanity checks. 320 */ 321 if (open_flags != O_RDONLY) 322 return (dict_surrogate(DICT_TYPE_SQLITE, name, open_flags, dict_flags, 323 "%s:%s map requires O_RDONLY access mode", 324 DICT_TYPE_SQLITE, name)); 325 326 /* 327 * Open the configuration file. 328 */ 329 if ((parser = cfg_parser_alloc(name)) == 0) 330 return (dict_surrogate(DICT_TYPE_SQLITE, name, open_flags, dict_flags, 331 "open %s: %m", name)); 332 333 dict_sqlite = (DICT_SQLITE *) dict_alloc(DICT_TYPE_SQLITE, name, 334 sizeof(DICT_SQLITE)); 335 dict_sqlite->dict.lookup = dict_sqlite_lookup; 336 dict_sqlite->dict.close = dict_sqlite_close; 337 dict_sqlite->dict.flags = dict_flags; 338 339 dict_sqlite->parser = parser; 340 sqlite_parse_config(dict_sqlite, name); 341 342 if (sqlite3_open(dict_sqlite->dbpath, &dict_sqlite->db)) 343 msg_fatal("%s:%s: Can't open database: %s\n", 344 DICT_TYPE_SQLITE, name, sqlite3_errmsg(dict_sqlite->db)); 345 346 dict_sqlite->dict.owner = cfg_get_owner(dict_sqlite->parser); 347 348 return (DICT_DEBUG (&dict_sqlite->dict)); 349 } 350 351 #endif 352