1 /* $NetBSD: dict_sqlite.c,v 1.3 2022/10/08 16:12: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
dict_sqlite_quote(DICT * dict,const char * raw_text,VSTRING * result)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
dict_sqlite_close(DICT * dict)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
dict_sqlite_lookup(DICT * dict,const char * name)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-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
sqlite_parse_config(DICT_SQLITE * dict_sqlite,const char * sqlitecf)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
dict_sqlite_open(const char * name,int open_flags,int dict_flags)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