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