source3/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 = 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
 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         default:
 205                 field_string = "unknown";
 206                 break;
 207         }
 208
 209         asprintf(&query,
 210                          "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'",
 211                          config_value_read(data, "logon time column",
 212                                                            CONFIG_LOGON_TIME_DEFAULT),
 213                          config_value_read(data, "logoff time column",
 214                                                            CONFIG_LOGOFF_TIME_DEFAULT),
 215                          config_value_read(data, "kickoff time column",
 216                                                            CONFIG_KICKOFF_TIME_DEFAULT),
 217                          config_value_read(data, "pass last set time column",
 218                                                            CONFIG_PASS_LAST_SET_TIME_DEFAULT),
 219                          config_value_read(data, "pass can change time column",
 220                                                            CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT),
 221                          config_value_read(data, "pass must change time column",
 222                                                            CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT),
 223                          config_value_read(data, "username column",
 224                                                            CONFIG_USERNAME_DEFAULT),
 225                          config_value_read(data, "domain column",
 226                                                            CONFIG_DOMAIN_DEFAULT),
 227                          config_value_read(data, "nt username column",
 228                                                            CONFIG_NT_USERNAME_DEFAULT),
 229                          config_value_read(data, "fullname column",
 230                                                            CONFIG_FULLNAME_DEFAULT),
 231                          config_value_read(data, "home dir column",
 232                                                            CONFIG_HOME_DIR_DEFAULT),
 233                          config_value_read(data, "dir drive column",
 234                                                            CONFIG_DIR_DRIVE_DEFAULT),
 235                          config_value_read(data, "logon script column",
 236                                                            CONFIG_LOGON_SCRIPT_DEFAULT),
 237                          config_value_read(data, "profile path column",
 238                                                            CONFIG_PROFILE_PATH_DEFAULT),
 239                          config_value_read(data, "acct desc column",
 240                                                            CONFIG_ACCT_DESC_DEFAULT),
 241                          config_value_read(data, "workstations column",
 242                                                            CONFIG_WORKSTATIONS_DEFAULT),
 243                          config_value_read(data, "unknown string column",
 244                                                            CONFIG_UNKNOWN_STR_DEFAULT),
 245                          config_value_read(data, "munged dial column",
 246                                                            CONFIG_MUNGED_DIAL_DEFAULT),
 247                          config_value_read(data, "user sid column",
 248                                                            CONFIG_USER_SID_DEFAULT),
 249                          config_value_read(data, "group sid column",
 250                                                            CONFIG_GROUP_SID_DEFAULT),
 251                          config_value_read(data, "lanman pass column",
 252                                                            CONFIG_LM_PW_DEFAULT),
 253                          config_value_read(data, "nt pass column",
 254                                                            CONFIG_NT_PW_DEFAULT),
 255                          config_value_read(data, "plain pass column",
 256                                                            CONFIG_PLAIN_PW_DEFAULT),
 257                          config_value_read(data, "acct ctrl column",
 258                                                            CONFIG_ACCT_CTRL_DEFAULT),
 259                          config_value_read(data, "logon divs column",
 260                                                            CONFIG_LOGON_DIVS_DEFAULT),
 261                          config_value_read(data, "hours len column",
 262                                                            CONFIG_HOURS_LEN_DEFAULT),
 263                          config_value_read(data, "bad password count column",
 264                                                            CONFIG_BAD_PASSWORD_COUNT_DEFAULT),
 265                          config_value_read(data, "logon count column",
 266                                                            CONFIG_LOGON_COUNT_DEFAULT),
 267                          config_value_read(data, "unknown 6 column",
 268                                                            CONFIG_UNKNOWN_6_DEFAULT),
 269                          config_value(data, "table", CONFIG_TABLE_DEFAULT),
 270                          field_string, value
 271                                  );
 272          return query;
 273 }
 274
 275 char *sql_account_query_delete(const char *data, const char *esc)
 276 {
 277         char *query;
 278
 279         asprintf(&query, "DELETE FROM %s WHERE %s = '%s'",
 280                          config_value(data, "table", CONFIG_TABLE_DEFAULT),
 281                          config_value_read(data, "username column",
 282                                                            CONFIG_USERNAME_DEFAULT), esc);
 283         return query;
 284 }
 285
 286 char *sql_account_query_update(const char *location, const SAM_ACCOUNT *newpwd, char isupdate)
 287 {
 288         char *ret;
 289         pstring temp;
 290         pdb_sql_query query;
 291         fstring sid_str;
 292
 293         query.update = isupdate;
 294
 295         /* I know this is somewhat overkill but only the talloc
 296          * functions have asprint_append and the 'normal' asprintf
 297          * is a GNU extension */
 298         query.mem_ctx = talloc_init("sql_query_update");
 299         query.part2 = talloc_asprintf(query.mem_ctx, "%s", "");
 300         if (query.update) {
 301                 query.part1 =
 302                         talloc_asprintf(query.mem_ctx, "UPDATE %s SET ",
 303                                                         config_value(location, "table",
 304                                                                                  CONFIG_TABLE_DEFAULT));
 305         } else {
 306                 query.part1 =
 307                         talloc_asprintf(query.mem_ctx, "INSERT INTO %s (",
 308                                                         config_value(location, "table",
 309                                                                                  CONFIG_TABLE_DEFAULT));
 310         }
 311
 312         pdb_sql_int_field(&query,
 313                                                 config_value_write(location, "acct ctrl column",
 314                                                                                    CONFIG_ACCT_CTRL_DEFAULT),
 315                                                 pdb_get_acct_ctrl(newpwd));
 316
 317         if (pdb_get_init_flags(newpwd, PDB_LOGONTIME) != PDB_DEFAULT) {
 318                 pdb_sql_int_field(&query,
 319                                                         config_value_write(location,
 320                                                                                            "logon time column",
 321                                                                                            CONFIG_LOGON_TIME_DEFAULT),
 322                                                         pdb_get_logon_time(newpwd));
 323         }
 324
 325         if (pdb_get_init_flags(newpwd, PDB_LOGOFFTIME) != PDB_DEFAULT) {
 326                 pdb_sql_int_field(&query,
 327                                                         config_value_write(location,
 328                                                                                            "logoff time column",
 329                                                                                            CONFIG_LOGOFF_TIME_DEFAULT),
 330                                                         pdb_get_logoff_time(newpwd));
 331         }
 332
 333         if (pdb_get_init_flags(newpwd, PDB_KICKOFFTIME) != PDB_DEFAULT) {
 334                 pdb_sql_int_field(&query,
 335                                                         config_value_write(location,
 336                                                                                            "kickoff time column",
 337                                                                                            CONFIG_KICKOFF_TIME_DEFAULT),
 338                                                         pdb_get_kickoff_time(newpwd));
 339         }
 340
 341         if (pdb_get_init_flags(newpwd, PDB_CANCHANGETIME) != PDB_DEFAULT) {
 342                 pdb_sql_int_field(&query,
 343                                                         config_value_write(location,
 344                                                                                            "pass can change time column",
 345                                                                                            CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT),
 346                                                         pdb_get_pass_can_change_time(newpwd));
 347         }
 348
 349         if (pdb_get_init_flags(newpwd, PDB_MUSTCHANGETIME) != PDB_DEFAULT) {
 350                 pdb_sql_int_field(&query,
 351                                                         config_value_write(location,
 352                                                                                            "pass must change time column",
 353                                                                                            CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT),
 354                                                         pdb_get_pass_must_change_time(newpwd));
 355         }
 356
 357         if (pdb_get_pass_last_set_time(newpwd)) {
 358                 pdb_sql_int_field(&query,
 359                                                         config_value_write(location,
 360                                                                                            "pass last set time column",
 361                                                                                            CONFIG_PASS_LAST_SET_TIME_DEFAULT),
 362                                                         pdb_get_pass_last_set_time(newpwd));
 363         }
 364
 365         if (pdb_get_hours_len(newpwd)) {
 366                 pdb_sql_int_field(&query,
 367                                                         config_value_write(location,
 368                                                                                            "hours len column",
 369                                                                                            CONFIG_HOURS_LEN_DEFAULT),
 370                                                         pdb_get_hours_len(newpwd));
 371         }
 372
 373         if (pdb_get_logon_divs(newpwd)) {
 374                 pdb_sql_int_field(&query,
 375                                                         config_value_write(location,
 376                                                                                            "logon divs column",
 377                                                                                            CONFIG_LOGON_DIVS_DEFAULT),
 378                                                         pdb_get_logon_divs(newpwd));
 379         }
 380
 381         pdb_sql_string_field(&query,
 382                                                    config_value_write(location, "user sid column",
 383                                                                                           CONFIG_USER_SID_DEFAULT),
 384                                                    sid_to_string(sid_str,
 385                                                                                  pdb_get_user_sid(newpwd)));
 386
 387         pdb_sql_string_field(&query,
 388                                                    config_value_write(location, "group sid column",
 389                                                                                           CONFIG_GROUP_SID_DEFAULT),
 390                                                    sid_to_string(sid_str,
 391                                                                                  pdb_get_group_sid(newpwd)));
 392
 393         pdb_sql_string_field(&query,
 394                                                    config_value_write(location, "username column",
 395                                                                                           CONFIG_USERNAME_DEFAULT),
 396                                                    pdb_get_username(newpwd));
 397
 398         pdb_sql_string_field(&query,
 399                                                    config_value_write(location, "domain column",
 400                                                                                           CONFIG_DOMAIN_DEFAULT),
 401                                                    pdb_get_domain(newpwd));
 402
 403         pdb_sql_string_field(&query,
 404                                                    config_value_write(location,
 405                                                                                           "nt username column",
 406                                                                                           CONFIG_NT_USERNAME_DEFAULT),
 407                                                    pdb_get_nt_username(newpwd));
 408
 409         pdb_sql_string_field(&query,
 410                                                    config_value_write(location, "fullname column",
 411                                                                                           CONFIG_FULLNAME_DEFAULT),
 412                                                    pdb_get_fullname(newpwd));
 413
 414         pdb_sql_string_field(&query,
 415                                                    config_value_write(location,
 416                                                                                           "logon script column",
 417                                                                                           CONFIG_LOGON_SCRIPT_DEFAULT),
 418                                                    pdb_get_logon_script(newpwd));
 419
 420         pdb_sql_string_field(&query,
 421                                                    config_value_write(location,
 422                                                                                           "profile path column",
 423                                                                                           CONFIG_PROFILE_PATH_DEFAULT),
 424                                                    pdb_get_profile_path(newpwd));
 425
 426         pdb_sql_string_field(&query,
 427                                                    config_value_write(location, "dir drive column",
 428                                                                                           CONFIG_DIR_DRIVE_DEFAULT),
 429                                                    pdb_get_dir_drive(newpwd));
 430
 431         pdb_sql_string_field(&query,
 432                                                    config_value_write(location, "home dir column",
 433                                                                                           CONFIG_HOME_DIR_DEFAULT),
 434                                                    pdb_get_homedir(newpwd));
 435
 436         pdb_sql_string_field(&query,
 437                                                    config_value_write(location,
 438                                                                                           "workstations column",
 439                                                                                           CONFIG_WORKSTATIONS_DEFAULT),
 440                                                    pdb_get_workstations(newpwd));
 441
 442         pdb_sql_string_field(&query,
 443                                                    config_value_write(location,
 444                                                                                           "unknown string column",
 445                                                                                           CONFIG_UNKNOWN_STR_DEFAULT),
 446                                                    pdb_get_workstations(newpwd));
 447
 448         pdb_sethexpwd(temp, pdb_get_lanman_passwd(newpwd),
 449                                   pdb_get_acct_ctrl(newpwd));
 450         pdb_sql_string_field(&query,
 451                                                    config_value_write(location,
 452                                                                                           "lanman pass column",
 453                                                                                           CONFIG_LM_PW_DEFAULT), temp);
 454
 455         pdb_sethexpwd(temp, pdb_get_nt_passwd(newpwd),
 456                                   pdb_get_acct_ctrl(newpwd));
 457         pdb_sql_string_field(&query,
 458                                                    config_value_write(location, "nt pass column",
 459                                                                                           CONFIG_NT_PW_DEFAULT), temp);
 460
 461         if (query.update) {
 462                 query.part1[strlen(query.part1) - 1] = '\0';
 463                 query.part1 =
 464                         talloc_asprintf_append(query.mem_ctx, query.part1,
 465                                                                    " WHERE %s = '%s'",
 466                                                                    config_value_read(location,
 467                                                                                                          "user sid column",
 468                                                                                                          CONFIG_USER_SID_DEFAULT),
 469                                                                    sid_to_string(sid_str, pdb_get_user_sid (newpwd)));
 470         } else {
 471                 query.part2[strlen(query.part2) - 1] = ')';
 472                 query.part1[strlen(query.part1) - 1] = ')';
 473                 query.part1 =
 474                         talloc_asprintf_append(query.mem_ctx, query.part1,
 475                                                                    " VALUES (%s", query.part2);
 476         }
 477
 478         ret = SMB_STRDUP(query.part1);
 479         talloc_destroy(query.mem_ctx);
 480         return ret;
 481 }
 482
 483 BOOL sql_account_config_valid(const char *data)
 484 {
 485         const char *sid_column, *username_column;
 486
 487     sid_column = config_value_read(data, "user sid column", CONFIG_USER_SID_DEFAULT);
 488     username_column = config_value_read(data, "username column", CONFIG_USERNAME_DEFAULT);
 489
 490     if(!strcmp(sid_column,"NULL") || !strcmp(username_column, "NULL")) {
 491         DEBUG(0,("Please specify both a valid 'user sid column' and a valid 'username column' in smb.conf\n"));
 492         return False;
 493     }
 494
 495         return True;
 496 }