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