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
82 typedef 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
dict_sqlite_quote(DICT * dict,const char * raw_text,VSTRING * result)95 static 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
dict_sqlite_close(DICT * dict)109 static 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
dict_sqlite_lookup(DICT * dict,const char * name)132 static 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
sqlite_parse_config(DICT_SQLITE * dict_sqlite,const char * sqlitecf)254 static 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
dict_sqlite_open(const char * name,int open_flags,int dict_flags)296 DICT *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