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