import HEAD into svn+ssh://svn.samba.org/home/svn/samba/trunk
[metze/old/v3-2-winbind-ndr.git] / source / passdb / pdb_sql.c
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
55 typedef struct pdb_sql_query {
56         char update;
57         TALLOC_CTX *mem_ctx;
58         char *part1;
59         char *part2;
60 } pdb_sql_query;
61
62 static 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
79 char *sql_escape_string(const char *unesc)
80 {
81         char *esc = 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
101 static 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
131 static 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                 return v;
146
147         swrite++;
148
149         /* If the field is 0 chars long, we shouldn't write to it */
150         if (!strlen(swrite) || !strcmp(swrite, "NULL"))
151                 return NULL;
152
153         /* Otherwise, use the additionally specified */
154         return swrite;
155 }
156
157 static const char * config_value_read(const char *location, const char *name, const char *default_value)
158 {
159         char *v = NULL;
160         char *swrite;
161
162         v = lp_parm_talloc_string(GLOBAL_SECTION_SNUM, location, name, default_value);
163
164         if (!v)
165                 return "NULL";
166
167         swrite = strrchr(v, ':');
168
169         /* If no write is specified, there are no problems */
170         if (!swrite) {
171                 if (strlen(v) == 0)
172                         return "NULL";
173                 return (const char *)v;
174         }
175
176         /* Otherwise, we have to cut the ':write_part' */
177         *swrite = '\0';
178         if (strlen(v) == 0)
179                 return "NULL";
180
181         return (const char *)v;
182 }
183
184 char *sql_account_query_select(const char *data, BOOL update, enum sql_search_field field, const char *value)
185 {
186         const char *field_string;
187         char *query;
188
189         switch(field) {
190         case SQL_SEARCH_NONE: 
191                 field_string = "'1'"; 
192                 value = "1"; 
193                 break;
194                 
195         case SQL_SEARCH_USER_SID: 
196                 field_string = config_value_read(data, "user sid column", 
197                                                                                  CONFIG_USER_SID_DEFAULT); 
198                 break;
199                 
200         case SQL_SEARCH_USER_NAME: 
201                 field_string = config_value_read(data, "username column", 
202                                                                                  CONFIG_USERNAME_DEFAULT);
203                 break;
204         }
205
206         asprintf(&query,
207                          "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'",
208                          config_value_read(data, "logon time column",
209                                                            CONFIG_LOGON_TIME_DEFAULT),
210                          config_value_read(data, "logoff time column",
211                                                            CONFIG_LOGOFF_TIME_DEFAULT),
212                          config_value_read(data, "kickoff time column",
213                                                            CONFIG_KICKOFF_TIME_DEFAULT),
214                          config_value_read(data, "pass last set time column",
215                                                            CONFIG_PASS_LAST_SET_TIME_DEFAULT),
216                          config_value_read(data, "pass can change time column",
217                                                            CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT),
218                          config_value_read(data, "pass must change time column",
219                                                            CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT),
220                          config_value_read(data, "username column",
221                                                            CONFIG_USERNAME_DEFAULT),
222                          config_value_read(data, "domain column",
223                                                            CONFIG_DOMAIN_DEFAULT),
224                          config_value_read(data, "nt username column",
225                                                            CONFIG_NT_USERNAME_DEFAULT),
226                          config_value_read(data, "fullname column",
227                                                            CONFIG_FULLNAME_DEFAULT),
228                          config_value_read(data, "home dir column",
229                                                            CONFIG_HOME_DIR_DEFAULT),
230                          config_value_read(data, "dir drive column",
231                                                            CONFIG_DIR_DRIVE_DEFAULT),
232                          config_value_read(data, "logon script column",
233                                                            CONFIG_LOGON_SCRIPT_DEFAULT),
234                          config_value_read(data, "profile path column",
235                                                            CONFIG_PROFILE_PATH_DEFAULT),
236                          config_value_read(data, "acct desc column",
237                                                            CONFIG_ACCT_DESC_DEFAULT),
238                          config_value_read(data, "workstations column",
239                                                            CONFIG_WORKSTATIONS_DEFAULT),
240                          config_value_read(data, "unknown string column",
241                                                            CONFIG_UNKNOWN_STR_DEFAULT),
242                          config_value_read(data, "munged dial column",
243                                                            CONFIG_MUNGED_DIAL_DEFAULT),
244                          config_value_read(data, "user sid column",
245                                                            CONFIG_USER_SID_DEFAULT),
246                          config_value_read(data, "group sid column",
247                                                            CONFIG_GROUP_SID_DEFAULT),
248                          config_value_read(data, "lanman pass column",
249                                                            CONFIG_LM_PW_DEFAULT),
250                          config_value_read(data, "nt pass column",
251                                                            CONFIG_NT_PW_DEFAULT),
252                          config_value_read(data, "plain pass column",
253                                                            CONFIG_PLAIN_PW_DEFAULT),
254                          config_value_read(data, "acct ctrl column",
255                                                            CONFIG_ACCT_CTRL_DEFAULT),
256                          config_value_read(data, "logon divs column",
257                                                            CONFIG_LOGON_DIVS_DEFAULT),
258                          config_value_read(data, "hours len column",
259                                                            CONFIG_HOURS_LEN_DEFAULT),
260                          config_value_read(data, "bad password count column",
261                                                            CONFIG_BAD_PASSWORD_COUNT_DEFAULT),
262                          config_value_read(data, "logon count column",
263                                                            CONFIG_LOGON_COUNT_DEFAULT),
264                          config_value_read(data, "unknown 6 column",
265                                                            CONFIG_UNKNOWN_6_DEFAULT),
266                          config_value(data, "table", CONFIG_TABLE_DEFAULT), 
267                          field_string, value
268                                  );
269          return query;
270 }
271
272 char *sql_account_query_delete(const char *data, const char *esc) 
273 {
274         char *query;
275         
276         asprintf(&query, "DELETE FROM %s WHERE %s = '%s'",
277                          config_value(data, "table", CONFIG_TABLE_DEFAULT),
278                          config_value_read(data, "username column",
279                                                            CONFIG_USERNAME_DEFAULT), esc);
280         return query;
281 }
282
283 char *sql_account_query_update(const char *location, const SAM_ACCOUNT *newpwd, char isupdate)
284 {
285         char *ret;
286         pstring temp;
287         pdb_sql_query query;
288         fstring sid_str;
289
290         query.update = isupdate;
291
292         /* I know this is somewhat overkill but only the talloc 
293          * functions have asprint_append and the 'normal' asprintf 
294          * is a GNU extension */
295         query.mem_ctx = talloc_init("sql_query_update");
296         query.part2 = talloc_asprintf(query.mem_ctx, "%s", "");
297         if (query.update) {
298                 query.part1 =
299                         talloc_asprintf(query.mem_ctx, "UPDATE %s SET ",
300                                                         config_value(location, "table",
301                                                                                  CONFIG_TABLE_DEFAULT));
302         } else {
303                 query.part1 =
304                         talloc_asprintf(query.mem_ctx, "INSERT INTO %s (",
305                                                         config_value(location, "table",
306                                                                                  CONFIG_TABLE_DEFAULT));
307         }
308
309         pdb_sql_int_field(&query,
310                                                 config_value_write(location, "acct ctrl column",
311                                                                                    CONFIG_ACCT_CTRL_DEFAULT),
312                                                 pdb_get_acct_ctrl(newpwd));
313
314         if (pdb_get_init_flags(newpwd, PDB_LOGONTIME) != PDB_DEFAULT) {
315                 pdb_sql_int_field(&query,
316                                                         config_value_write(location,
317                                                                                            "logon time column",
318                                                                                            CONFIG_LOGON_TIME_DEFAULT),
319                                                         pdb_get_logon_time(newpwd));
320         }
321
322         if (pdb_get_init_flags(newpwd, PDB_LOGOFFTIME) != PDB_DEFAULT) {
323                 pdb_sql_int_field(&query,
324                                                         config_value_write(location,
325                                                                                            "logoff time column",
326                                                                                            CONFIG_LOGOFF_TIME_DEFAULT),
327                                                         pdb_get_logoff_time(newpwd));
328         }
329
330         if (pdb_get_init_flags(newpwd, PDB_KICKOFFTIME) != PDB_DEFAULT) {
331                 pdb_sql_int_field(&query,
332                                                         config_value_write(location,
333                                                                                            "kickoff time column",
334                                                                                            CONFIG_KICKOFF_TIME_DEFAULT),
335                                                         pdb_get_kickoff_time(newpwd));
336         }
337
338         if (pdb_get_init_flags(newpwd, PDB_CANCHANGETIME) != PDB_DEFAULT) {
339                 pdb_sql_int_field(&query,
340                                                         config_value_write(location,
341                                                                                            "pass can change time column",
342                                                                                            CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT),
343                                                         pdb_get_pass_can_change_time(newpwd));
344         }
345
346         if (pdb_get_init_flags(newpwd, PDB_MUSTCHANGETIME) != PDB_DEFAULT) {
347                 pdb_sql_int_field(&query,
348                                                         config_value_write(location,
349                                                                                            "pass must change time column",
350                                                                                            CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT),
351                                                         pdb_get_pass_must_change_time(newpwd));
352         }
353
354         if (pdb_get_pass_last_set_time(newpwd)) {
355                 pdb_sql_int_field(&query,
356                                                         config_value_write(location,
357                                                                                            "pass last set time column",
358                                                                                            CONFIG_PASS_LAST_SET_TIME_DEFAULT),
359                                                         pdb_get_pass_last_set_time(newpwd));
360         }
361
362         if (pdb_get_hours_len(newpwd)) {
363                 pdb_sql_int_field(&query,
364                                                         config_value_write(location,
365                                                                                            "hours len column",
366                                                                                            CONFIG_HOURS_LEN_DEFAULT),
367                                                         pdb_get_hours_len(newpwd));
368         }
369
370         if (pdb_get_logon_divs(newpwd)) {
371                 pdb_sql_int_field(&query,
372                                                         config_value_write(location,
373                                                                                            "logon divs column",
374                                                                                            CONFIG_LOGON_DIVS_DEFAULT),
375                                                         pdb_get_logon_divs(newpwd));
376         }
377
378         pdb_sql_string_field(&query,
379                                                    config_value_write(location, "user sid column",
380                                                                                           CONFIG_USER_SID_DEFAULT),
381                                                    sid_to_string(sid_str, 
382                                                                                  pdb_get_user_sid(newpwd)));
383
384         pdb_sql_string_field(&query,
385                                                    config_value_write(location, "group sid column",
386                                                                                           CONFIG_GROUP_SID_DEFAULT),
387                                                    sid_to_string(sid_str,
388                                                                                  pdb_get_group_sid(newpwd)));
389
390         pdb_sql_string_field(&query,
391                                                    config_value_write(location, "username column",
392                                                                                           CONFIG_USERNAME_DEFAULT),
393                                                    pdb_get_username(newpwd));
394
395         pdb_sql_string_field(&query,
396                                                    config_value_write(location, "domain column",
397                                                                                           CONFIG_DOMAIN_DEFAULT),
398                                                    pdb_get_domain(newpwd));
399
400         pdb_sql_string_field(&query,
401                                                    config_value_write(location,
402                                                                                           "nt username column",
403                                                                                           CONFIG_NT_USERNAME_DEFAULT),
404                                                    pdb_get_nt_username(newpwd));
405
406         pdb_sql_string_field(&query,
407                                                    config_value_write(location, "fullname column",
408                                                                                           CONFIG_FULLNAME_DEFAULT),
409                                                    pdb_get_fullname(newpwd));
410
411         pdb_sql_string_field(&query,
412                                                    config_value_write(location,
413                                                                                           "logon script column",
414                                                                                           CONFIG_LOGON_SCRIPT_DEFAULT),
415                                                    pdb_get_logon_script(newpwd));
416
417         pdb_sql_string_field(&query,
418                                                    config_value_write(location,
419                                                                                           "profile path column",
420                                                                                           CONFIG_PROFILE_PATH_DEFAULT),
421                                                    pdb_get_profile_path(newpwd));
422
423         pdb_sql_string_field(&query,
424                                                    config_value_write(location, "dir drive column",
425                                                                                           CONFIG_DIR_DRIVE_DEFAULT),
426                                                    pdb_get_dir_drive(newpwd));
427
428         pdb_sql_string_field(&query,
429                                                    config_value_write(location, "home dir column",
430                                                                                           CONFIG_HOME_DIR_DEFAULT),
431                                                    pdb_get_homedir(newpwd));
432
433         pdb_sql_string_field(&query,
434                                                    config_value_write(location,
435                                                                                           "workstations column",
436                                                                                           CONFIG_WORKSTATIONS_DEFAULT),
437                                                    pdb_get_workstations(newpwd));
438
439         pdb_sql_string_field(&query,
440                                                    config_value_write(location,
441                                                                                           "unknown string column",
442                                                                                           CONFIG_UNKNOWN_STR_DEFAULT),
443                                                    pdb_get_workstations(newpwd));
444
445         pdb_sethexpwd(temp, pdb_get_lanman_passwd(newpwd),
446                                   pdb_get_acct_ctrl(newpwd));
447         pdb_sql_string_field(&query,
448                                                    config_value_write(location,
449                                                                                           "lanman pass column",
450                                                                                           CONFIG_LM_PW_DEFAULT), temp);
451
452         pdb_sethexpwd(temp, pdb_get_nt_passwd(newpwd),
453                                   pdb_get_acct_ctrl(newpwd));
454         pdb_sql_string_field(&query,
455                                                    config_value_write(location, "nt pass column",
456                                                                                           CONFIG_NT_PW_DEFAULT), temp);
457
458         if (query.update) {
459                 query.part1[strlen(query.part1) - 1] = '\0';
460                 query.part1 =
461                         talloc_asprintf_append(query.mem_ctx, query.part1,
462                                                                    " WHERE %s = '%s'",
463                                                                    config_value_read(location,
464                                                                                                          "user sid column",
465                                                                                                          CONFIG_USER_SID_DEFAULT),
466                                                                    sid_to_string(sid_str, pdb_get_user_sid (newpwd)));
467         } else {
468                 query.part2[strlen(query.part2) - 1] = ')';
469                 query.part1[strlen(query.part1) - 1] = ')';
470                 query.part1 =
471                         talloc_asprintf_append(query.mem_ctx, query.part1,
472                                                                    " VALUES (%s", query.part2);
473         }
474
475         ret = strdup(query.part1);
476         talloc_destroy(query.mem_ctx);
477         return ret;
478 }
479
480 BOOL sql_account_config_valid(const char *data)
481 {
482         const char *sid_column, *username_column;
483         
484     sid_column = config_value_read(data, "user sid column", CONFIG_USER_SID_DEFAULT);
485     username_column = config_value_read(data, "username column", CONFIG_USERNAME_DEFAULT);
486         
487     if(!strcmp(sid_column,"NULL") || !strcmp(username_column, "NULL")) {
488         DEBUG(0,("Please specify both a valid 'user sid column' and a valid 'username column' in smb.conf\n"));
489         return False;
490     }
491
492         return True;
493 }