1/*
2 * Common PDB SQL backend functions
3 * Copyright (C) Jelmer Vernooij 2003-2004
4 *
5 * This program is free software; you can redistribute it and/or modify it under
6 * the terms of the GNU General Public License as published by the Free
7 * Software Foundation; either version 2 of the License, or (at your option)
8 * any later version.
9 *
10 * This program is distributed in the hope that it will be useful, but WITHOUT
11 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
12 * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
13 * more details.
14 *
15 * You should have received a copy of the GNU General Public License along with
16 * this program; if not, write to the Free Software Foundation, Inc., 675
17 * Mass Ave, Cambridge, MA 02139, USA.
18 */
19
20#include "includes.h"
21
22#define CONFIG_TABLE_DEFAULT				"user"
23#define CONFIG_LOGON_TIME_DEFAULT			"logon_time"
24#define CONFIG_LOGOFF_TIME_DEFAULT			"logoff_time"
25#define CONFIG_KICKOFF_TIME_DEFAULT			"kickoff_time"
26#define CONFIG_PASS_LAST_SET_TIME_DEFAULT		"pass_last_set_time"
27#define CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT		"pass_can_change_time"
28#define CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT 		"pass_must_change_time"
29#define CONFIG_USERNAME_DEFAULT 			"username"
30#define CONFIG_DOMAIN_DEFAULT				"domain"
31#define CONFIG_NT_USERNAME_DEFAULT  			"nt_username"
32#define CONFIG_FULLNAME_DEFAULT				"nt_fullname"
33#define CONFIG_HOME_DIR_DEFAULT				"home_dir"
34#define CONFIG_DIR_DRIVE_DEFAULT			"dir_drive"
35#define CONFIG_LOGON_SCRIPT_DEFAULT			"logon_script"
36#define CONFIG_PROFILE_PATH_DEFAULT			"profile_path"
37#define CONFIG_ACCT_DESC_DEFAULT			"acct_desc"
38#define CONFIG_WORKSTATIONS_DEFAULT			"workstations"
39#define CONFIG_UNKNOWN_STR_DEFAULT			"unknown_str"
40#define CONFIG_MUNGED_DIAL_DEFAULT			"munged_dial"
41#define CONFIG_USER_SID_DEFAULT				"user_sid"
42#define CONFIG_GROUP_SID_DEFAULT			"group_sid"
43#define CONFIG_LM_PW_DEFAULT				"lm_pw"
44#define CONFIG_NT_PW_DEFAULT				"nt_pw"
45#define CONFIG_PLAIN_PW_DEFAULT				"NULL"
46#define CONFIG_ACCT_CTRL_DEFAULT			"acct_ctrl"
47#define CONFIG_LOGON_DIVS_DEFAULT			"logon_divs"
48#define CONFIG_HOURS_LEN_DEFAULT			"hours_len"
49#define CONFIG_BAD_PASSWORD_COUNT_DEFAULT		"bad_password_count"
50#define CONFIG_LOGON_COUNT_DEFAULT			"logon_count"
51#define CONFIG_UNKNOWN_6_DEFAULT			"unknown_6"
52
53/* Used to construct insert and update queries */
54
55typedef struct pdb_sql_query {
56	char update;
57	TALLOC_CTX *mem_ctx;
58	char *part1;
59	char *part2;
60} pdb_sql_query;
61
62static void pdb_sql_int_field(struct pdb_sql_query *q, const char *name, int value)
63{
64	if (!name || strchr(name, '\''))
65		return;                 /* This field shouldn't be set by us */
66
67	if (q->update) {
68		q->part1 =
69			talloc_asprintf_append(q->mem_ctx, q->part1,
70								   "%s = %d,", name, value);
71	} else {
72		q->part1 =
73			talloc_asprintf_append(q->mem_ctx, q->part1, "%s,", name);
74		q->part2 =
75			talloc_asprintf_append(q->mem_ctx, q->part2, "%d,", value);
76	}
77}
78
79char *sql_escape_string(const char *unesc)
80{
81	char *esc = SMB_MALLOC(strlen(unesc) * 2 + 3);
82	size_t pos_unesc = 0, pos_esc = 0;
83
84	for(pos_unesc = 0; unesc[pos_unesc]; pos_unesc++) {
85		switch(unesc[pos_unesc]) {
86		case '\\':
87		case '\'':
88		case '"':
89			esc[pos_esc] = '\\'; pos_esc++;
90		default:
91			esc[pos_esc] = unesc[pos_unesc]; pos_esc++;
92			break;
93		}
94	}
95
96	esc[pos_esc] = '\0';
97
98	return esc;
99}
100
101static NTSTATUS pdb_sql_string_field(struct pdb_sql_query *q,
102					   const char *name, const char *value)
103{
104	char *esc_value;
105
106	if (!name || !value || !strcmp(value, "") || strchr(name, '\''))
107		return NT_STATUS_INVALID_PARAMETER;   /* This field shouldn't be set by module */
108
109	esc_value = sql_escape_string(value);
110
111	if (q->update) {
112		q->part1 =
113			talloc_asprintf_append(q->mem_ctx, q->part1,
114								   "%s = '%s',", name, esc_value);
115	} else {
116		q->part1 =
117			talloc_asprintf_append(q->mem_ctx, q->part1, "%s,", name);
118		q->part2 =
119			talloc_asprintf_append(q->mem_ctx, q->part2, "'%s',",
120								   esc_value);
121	}
122
123	SAFE_FREE(esc_value);
124
125	return NT_STATUS_OK;
126}
127
128#define config_value(data,name,default_value) \
129	lp_parm_const_string(GLOBAL_SECTION_SNUM, data, name, default_value)
130
131static const char * config_value_write(const char *location, const char *name, const char *default_value)
132{
133	char const *v = NULL;
134	char const *swrite = NULL;
135
136	v = lp_parm_const_string(GLOBAL_SECTION_SNUM, location, name, default_value);
137
138	if (!v)
139		return NULL;
140
141	swrite = strrchr(v, ':');
142
143	/* Default to the same field as read field */
144	if (!swrite) {
145
146		/* Updating NULL does not make much sense */
147		if (!strcmp(v, "NULL"))
148			return NULL;
149
150		return v;
151	}
152
153	swrite++;
154
155	/* If the field is 0 chars long, we shouldn't write to it */
156	if (!strlen(swrite) || !strcmp(swrite, "NULL"))
157		return NULL;
158
159	/* Otherwise, use the additionally specified */
160	return swrite;
161}
162
163static const char * config_value_read(const char *location, const char *name, const char *default_value)
164{
165	char *v = NULL;
166	char *swrite;
167
168	v = lp_parm_talloc_string(GLOBAL_SECTION_SNUM, location, name, default_value);
169
170	if (!v)
171		return "NULL";
172
173	swrite = strrchr(v, ':');
174
175	/* If no write is specified, there are no problems */
176	if (!swrite) {
177		if (strlen(v) == 0)
178			return "NULL";
179		return (const char *)v;
180	}
181
182	/* Otherwise, we have to cut the ':write_part' */
183	*swrite = '\0';
184	if (strlen(v) == 0)
185		return "NULL";
186
187	return (const char *)v;
188}
189
190char *sql_account_query_select(const char *data, BOOL update, enum sql_search_field field, const char *value)
191{
192	const char *field_string;
193	char *query;
194
195	switch(field) {
196	case SQL_SEARCH_NONE:
197		field_string = "'1'";
198		value = "1";
199		break;
200
201	case SQL_SEARCH_USER_SID:
202		field_string = config_value_read(data, "user sid column",
203										 CONFIG_USER_SID_DEFAULT);
204		break;
205
206	case SQL_SEARCH_USER_NAME:
207		field_string = config_value_read(data, "username column",
208										 CONFIG_USERNAME_DEFAULT);
209		break;
210	default:
211		field_string = "unknown";
212		break;
213	}
214
215	asprintf(&query,
216			 "SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s FROM %s WHERE %s = '%s'",
217			 config_value_read(data, "logon time column",
218							   CONFIG_LOGON_TIME_DEFAULT),
219			 config_value_read(data, "logoff time column",
220							   CONFIG_LOGOFF_TIME_DEFAULT),
221			 config_value_read(data, "kickoff time column",
222							   CONFIG_KICKOFF_TIME_DEFAULT),
223			 config_value_read(data, "pass last set time column",
224							   CONFIG_PASS_LAST_SET_TIME_DEFAULT),
225			 config_value_read(data, "pass can change time column",
226							   CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT),
227			 config_value_read(data, "pass must change time column",
228							   CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT),
229			 config_value_read(data, "username column",
230							   CONFIG_USERNAME_DEFAULT),
231			 config_value_read(data, "domain column",
232							   CONFIG_DOMAIN_DEFAULT),
233			 config_value_read(data, "nt username column",
234							   CONFIG_NT_USERNAME_DEFAULT),
235			 config_value_read(data, "fullname column",
236							   CONFIG_FULLNAME_DEFAULT),
237			 config_value_read(data, "home dir column",
238							   CONFIG_HOME_DIR_DEFAULT),
239			 config_value_read(data, "dir drive column",
240							   CONFIG_DIR_DRIVE_DEFAULT),
241			 config_value_read(data, "logon script column",
242							   CONFIG_LOGON_SCRIPT_DEFAULT),
243			 config_value_read(data, "profile path column",
244							   CONFIG_PROFILE_PATH_DEFAULT),
245			 config_value_read(data, "acct desc column",
246							   CONFIG_ACCT_DESC_DEFAULT),
247			 config_value_read(data, "workstations column",
248							   CONFIG_WORKSTATIONS_DEFAULT),
249			 config_value_read(data, "unknown string column",
250							   CONFIG_UNKNOWN_STR_DEFAULT),
251			 config_value_read(data, "munged dial column",
252							   CONFIG_MUNGED_DIAL_DEFAULT),
253			 config_value_read(data, "user sid column",
254							   CONFIG_USER_SID_DEFAULT),
255			 config_value_read(data, "group sid column",
256							   CONFIG_GROUP_SID_DEFAULT),
257			 config_value_read(data, "lanman pass column",
258							   CONFIG_LM_PW_DEFAULT),
259			 config_value_read(data, "nt pass column",
260							   CONFIG_NT_PW_DEFAULT),
261			 config_value_read(data, "plain pass column",
262							   CONFIG_PLAIN_PW_DEFAULT),
263			 config_value_read(data, "acct ctrl column",
264							   CONFIG_ACCT_CTRL_DEFAULT),
265			 config_value_read(data, "logon divs column",
266							   CONFIG_LOGON_DIVS_DEFAULT),
267			 config_value_read(data, "hours len column",
268							   CONFIG_HOURS_LEN_DEFAULT),
269			 config_value_read(data, "bad password count column",
270							   CONFIG_BAD_PASSWORD_COUNT_DEFAULT),
271			 config_value_read(data, "logon count column",
272							   CONFIG_LOGON_COUNT_DEFAULT),
273			 config_value_read(data, "unknown 6 column",
274							   CONFIG_UNKNOWN_6_DEFAULT),
275			 config_value(data, "table", CONFIG_TABLE_DEFAULT),
276			 field_string, value
277				 );
278	 return query;
279}
280
281char *sql_account_query_delete(const char *data, const char *esc)
282{
283	char *query;
284
285	asprintf(&query, "DELETE FROM %s WHERE %s = '%s'",
286			 config_value(data, "table", CONFIG_TABLE_DEFAULT),
287			 config_value_read(data, "username column",
288							   CONFIG_USERNAME_DEFAULT), esc);
289	return query;
290}
291
292char *sql_account_query_update(const char *location, const SAM_ACCOUNT *newpwd, char isupdate)
293{
294	char *ret;
295	pstring temp;
296	pdb_sql_query query;
297	fstring sid_str;
298
299	query.update = isupdate;
300
301	/* I know this is somewhat overkill but only the talloc
302	 * functions have asprint_append and the 'normal' asprintf
303	 * is a GNU extension */
304	query.mem_ctx = talloc_init("sql_query_update");
305	query.part2 = talloc_asprintf(query.mem_ctx, "%s", "");
306	if (query.update) {
307		query.part1 =
308			talloc_asprintf(query.mem_ctx, "UPDATE %s SET ",
309							config_value(location, "table",
310										 CONFIG_TABLE_DEFAULT));
311	} else {
312		query.part1 =
313			talloc_asprintf(query.mem_ctx, "INSERT INTO %s (",
314							config_value(location, "table",
315										 CONFIG_TABLE_DEFAULT));
316	}
317
318	if (IS_SAM_CHANGED(newpwd, PDB_ACCTCTRL)) {
319		pdb_sql_int_field(&query,
320						config_value_write(location, "acct ctrl column",
321										   CONFIG_ACCT_CTRL_DEFAULT),
322						pdb_get_acct_ctrl(newpwd));
323	}
324
325	if (IS_SAM_CHANGED(newpwd, PDB_LOGONTIME)) {
326		pdb_sql_int_field(&query,
327							config_value_write(location,
328											   "logon time column",
329											   CONFIG_LOGON_TIME_DEFAULT),
330							pdb_get_logon_time(newpwd));
331	}
332
333	if (IS_SAM_CHANGED(newpwd, PDB_LOGOFFTIME)) {
334		pdb_sql_int_field(&query,
335							config_value_write(location,
336											   "logoff time column",
337											   CONFIG_LOGOFF_TIME_DEFAULT),
338							pdb_get_logoff_time(newpwd));
339	}
340
341	if (IS_SAM_CHANGED(newpwd, PDB_KICKOFFTIME)) {
342		pdb_sql_int_field(&query,
343							config_value_write(location,
344											   "kickoff time column",
345											   CONFIG_KICKOFF_TIME_DEFAULT),
346							pdb_get_kickoff_time(newpwd));
347	}
348
349	if (IS_SAM_CHANGED(newpwd, PDB_CANCHANGETIME)) {
350		pdb_sql_int_field(&query,
351							config_value_write(location,
352											   "pass can change time column",
353											   CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT),
354							pdb_get_pass_can_change_time(newpwd));
355	}
356
357	if (IS_SAM_CHANGED(newpwd, PDB_MUSTCHANGETIME)) {
358		pdb_sql_int_field(&query,
359							config_value_write(location,
360											   "pass must change time column",
361											   CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT),
362							pdb_get_pass_must_change_time(newpwd));
363	}
364
365	if (IS_SAM_CHANGED(newpwd, PDB_PASSLASTSET)) {
366		pdb_sql_int_field(&query,
367							config_value_write(location,
368											   "pass last set time column",
369											   CONFIG_PASS_LAST_SET_TIME_DEFAULT),
370							pdb_get_pass_last_set_time(newpwd));
371	}
372
373	if (IS_SAM_CHANGED(newpwd, PDB_HOURSLEN)) {
374		pdb_sql_int_field(&query,
375							config_value_write(location,
376											   "hours len column",
377											   CONFIG_HOURS_LEN_DEFAULT),
378							pdb_get_hours_len(newpwd));
379	}
380
381	if (IS_SAM_CHANGED(newpwd, PDB_LOGONDIVS)) {
382		pdb_sql_int_field(&query,
383							config_value_write(location,
384											   "logon divs column",
385											   CONFIG_LOGON_DIVS_DEFAULT),
386							pdb_get_logon_divs(newpwd));
387	}
388
389	if (IS_SAM_CHANGED(newpwd, PDB_USERSID)) {
390		pdb_sql_string_field(&query,
391						   config_value_write(location, "user sid column",
392											  CONFIG_USER_SID_DEFAULT),
393						   sid_to_string(sid_str,
394										 pdb_get_user_sid(newpwd)));
395	}
396
397	if (IS_SAM_CHANGED(newpwd, PDB_GROUPSID)) {
398		pdb_sql_string_field(&query,
399						   config_value_write(location, "group sid column",
400											  CONFIG_GROUP_SID_DEFAULT),
401						   sid_to_string(sid_str,
402										 pdb_get_group_sid(newpwd)));
403	}
404
405	if (IS_SAM_CHANGED(newpwd, PDB_USERNAME)) {
406		pdb_sql_string_field(&query,
407						   config_value_write(location, "username column",
408											  CONFIG_USERNAME_DEFAULT),
409						   pdb_get_username(newpwd));
410	}
411
412	if (IS_SAM_CHANGED(newpwd, PDB_DOMAIN)) {
413		pdb_sql_string_field(&query,
414						   config_value_write(location, "domain column",
415											  CONFIG_DOMAIN_DEFAULT),
416						   pdb_get_domain(newpwd));
417	}
418
419	if (IS_SAM_CHANGED(newpwd, PDB_USERNAME)) {
420		pdb_sql_string_field(&query,
421						   config_value_write(location,
422											  "nt username column",
423											  CONFIG_NT_USERNAME_DEFAULT),
424						   pdb_get_nt_username(newpwd));
425	}
426
427	if (IS_SAM_CHANGED(newpwd, PDB_FULLNAME)) {
428		pdb_sql_string_field(&query,
429						   config_value_write(location, "fullname column",
430											  CONFIG_FULLNAME_DEFAULT),
431						   pdb_get_fullname(newpwd));
432	}
433
434	if (IS_SAM_CHANGED(newpwd, PDB_LOGONSCRIPT)) {
435		pdb_sql_string_field(&query,
436						   config_value_write(location,
437											  "logon script column",
438											  CONFIG_LOGON_SCRIPT_DEFAULT),
439						   pdb_get_logon_script(newpwd));
440	}
441
442	if (IS_SAM_CHANGED(newpwd, PDB_PROFILE)) {
443		pdb_sql_string_field(&query,
444						   config_value_write(location,
445											  "profile path column",
446											  CONFIG_PROFILE_PATH_DEFAULT),
447						   pdb_get_profile_path(newpwd));
448	}
449
450	if (IS_SAM_CHANGED(newpwd, PDB_DRIVE)) {
451		pdb_sql_string_field(&query,
452						   config_value_write(location, "dir drive column",
453											  CONFIG_DIR_DRIVE_DEFAULT),
454						   pdb_get_dir_drive(newpwd));
455	}
456
457	if (IS_SAM_CHANGED(newpwd, PDB_SMBHOME)) {
458		pdb_sql_string_field(&query,
459						   config_value_write(location, "home dir column",
460											  CONFIG_HOME_DIR_DEFAULT),
461						   pdb_get_homedir(newpwd));
462	}
463
464	if (IS_SAM_CHANGED(newpwd, PDB_WORKSTATIONS)) {
465		pdb_sql_string_field(&query,
466						   config_value_write(location,
467											  "workstations column",
468											  CONFIG_WORKSTATIONS_DEFAULT),
469						   pdb_get_workstations(newpwd));
470	}
471
472	if (IS_SAM_CHANGED(newpwd, PDB_UNKNOWNSTR)) {
473		pdb_sql_string_field(&query,
474						   config_value_write(location,
475											  "unknown string column",
476											  CONFIG_UNKNOWN_STR_DEFAULT),
477						   pdb_get_workstations(newpwd));
478	}
479
480	if (IS_SAM_CHANGED(newpwd, PDB_LMPASSWD)) {
481		pdb_sethexpwd(temp, pdb_get_lanman_passwd(newpwd),
482					  pdb_get_acct_ctrl(newpwd));
483		pdb_sql_string_field(&query,
484						   config_value_write(location,
485											  "lanman pass column",
486											  CONFIG_LM_PW_DEFAULT), temp);
487	}
488
489	if (IS_SAM_CHANGED(newpwd, PDB_NTPASSWD)) {
490		pdb_sethexpwd(temp, pdb_get_nt_passwd(newpwd),
491					  pdb_get_acct_ctrl(newpwd));
492		pdb_sql_string_field(&query,
493						   config_value_write(location, "nt pass column",
494											  CONFIG_NT_PW_DEFAULT), temp);
495	}
496
497	if (query.update) {
498		query.part1[strlen(query.part1) - 1] = '\0';
499		query.part1 =
500			talloc_asprintf_append(query.mem_ctx, query.part1,
501								   " WHERE %s = '%s'",
502								   config_value_read(location,
503													 "user sid column",
504													 CONFIG_USER_SID_DEFAULT),
505								   sid_to_string(sid_str, pdb_get_user_sid (newpwd)));
506	} else {
507		query.part2[strlen(query.part2) - 1] = ')';
508		query.part1[strlen(query.part1) - 1] = ')';
509		query.part1 =
510			talloc_asprintf_append(query.mem_ctx, query.part1,
511								   " VALUES (%s", query.part2);
512	}
513
514	ret = SMB_STRDUP(query.part1);
515	talloc_destroy(query.mem_ctx);
516	return ret;
517}
518
519BOOL sql_account_config_valid(const char *data)
520{
521	const char *sid_column, *username_column;
522
523    sid_column = config_value_read(data, "user sid column", CONFIG_USER_SID_DEFAULT);
524    username_column = config_value_read(data, "username column", CONFIG_USERNAME_DEFAULT);
525
526    if(!strcmp(sid_column,"NULL") || !strcmp(username_column, "NULL")) {
527        DEBUG(0,("Please specify both a valid 'user sid column' and a valid 'username column' in smb.conf\n"));
528        return False;
529    }
530
531	return True;
532}
533