1/* 2 * libwebsockets - small server side websockets and web server implementation 3 * 4 * Copyright (C) 2010 - 2020 Andy Green <andy@warmcat.com> 5 * 6 * Permission is hereby granted, free of charge, to any person obtaining a copy 7 * of this software and associated documentation files (the "Software"), to 8 * deal in the Software without restriction, including without limitation the 9 * rights to use, copy, modify, merge, publish, distribute, sublicense, and/or 10 * sell copies of the Software, and to permit persons to whom the Software is 11 * furnished to do so, subject to the following conditions: 12 * 13 * The above copyright notice and this permission notice shall be included in 14 * all copies or substantial portions of the Software. 15 * 16 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 17 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 18 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 19 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 20 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 21 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS 22 * IN THE SOFTWARE. 23 */ 24 25#include <libwebsockets.h> 26#include <private-lib-core.h> 27 28#include <sqlite3.h> 29 30/* 31 * we get one of these per matching result from the query 32 */ 33 34static int 35lws_struct_sq3_deser_cb(void *priv, int cols, char **cv, char **cn) 36{ 37 lws_struct_args_t *a = (lws_struct_args_t *)priv; 38 char *u = lwsac_use_zero(&a->ac, a->dest_len, a->ac_block_size); 39 lws_dll2_owner_t *o = (lws_dll2_owner_t *)a->cb_arg; 40 const lws_struct_map_t *map = a->map_st[0]; 41 int n, mems = (int)(ssize_t)a->map_entries_st[0]; 42 long long li; 43 size_t lim; 44 char **pp; 45 char *s; 46 47 if (!u) { 48 lwsl_err("OOM\n"); 49 50 return 1; 51 } 52 53 lws_dll2_add_tail((lws_dll2_t *)((char *)u + a->toplevel_dll2_ofs), o); 54 55 while (mems--) { 56 for (n = 0; n < cols; n++) { 57 if (!cv[n] || strcmp(cn[n], map->colname)) 58 continue; 59 60 switch (map->type) { 61 case LSMT_SIGNED: 62 if (map->aux == sizeof(signed char)) { 63 signed char *pc; 64 pc = (signed char *)(u + map->ofs); 65 *pc = (signed char)atoi(cv[n]); 66 break; 67 } 68 if (map->aux == sizeof(short)) { 69 short *ps; 70 ps = (short *)(u + map->ofs); 71 *ps = (short)atoi(cv[n]); 72 break; 73 } 74 if (map->aux == sizeof(int)) { 75 int *pi; 76 pi = (int *)(u + map->ofs); 77 *pi = (int)atoll(cv[n]); /* 32-bit OS */ 78 break; 79 } 80 if (map->aux == sizeof(long)) { 81 long *pl; 82 pl = (long *)(u + map->ofs); 83 *pl = (long)atoll(cv[n]); /* 32-bit OS */ 84 break; 85 } 86 { 87 long long *pll; 88 pll = (long long *)(u + map->ofs); 89 *pll = atoll(cv[n]); 90 } 91 break; 92 93 case LSMT_UNSIGNED: 94 if (map->aux == sizeof(unsigned char)) { 95 unsigned char *pc; 96 pc = (unsigned char *)(u + map->ofs); 97 *pc = (unsigned char)(unsigned int)atoi(cv[n]); 98 break; 99 } 100 if (map->aux == sizeof(unsigned short)) { 101 unsigned short *ps; 102 ps = (unsigned short *)(u + map->ofs); 103 *ps = (unsigned short)atoi(cv[n]); 104 break; 105 } 106 if (map->aux == sizeof(unsigned int)) { 107 unsigned int *pi; 108 pi = (unsigned int *)(u + map->ofs); 109 *pi = (unsigned int)atoi(cv[n]); 110 break; 111 } 112 if (map->aux == sizeof(unsigned long)) { 113 unsigned long *pl; 114 pl = (unsigned long *)(u + map->ofs); 115 *pl = (unsigned long)atol(cv[n]); 116 break; 117 } 118 { 119 unsigned long long *pll; 120 pll = (unsigned long long *)(u + map->ofs); 121 *pll = (unsigned long long)atoll(cv[n]); 122 } 123 break; 124 125 case LSMT_BOOLEAN: 126 li = 0; 127 if (!strcmp(cv[n], "true") || 128 !strcmp(cv[n], "TRUE") || cv[n][0] == '1') 129 li = 1; 130 if (map->aux == sizeof(char)) { 131 char *pc; 132 pc = (char *)(u + map->ofs); 133 *pc = (char)li; 134 break; 135 } 136 if (map->aux == sizeof(int)) { 137 int *pi; 138 pi = (int *)(u + map->ofs); 139 *pi = (int)li; 140 } else { 141 uint64_t *p64; 142 p64 = (uint64_t *)(u + map->ofs); 143 *p64 = (uint64_t)li; 144 } 145 break; 146 147 case LSMT_STRING_CHAR_ARRAY: 148 s = (char *)(u + map->ofs); 149 lim = map->aux; 150 lws_strncpy(s, cv[n], lim); 151 break; 152 153 case LSMT_STRING_PTR: 154 pp = (char **)(u + map->ofs); 155 lim = strlen(cv[n]); 156 s = lwsac_use(&a->ac, lim + 1, a->ac_block_size); 157 if (!s) 158 return 1; 159 *pp = s; 160 memcpy(s, cv[n], lim); 161 s[lim] = '\0'; 162 break; 163 default: 164 break; 165 } 166 } 167 map++; 168 } 169 170 return 0; 171} 172 173/* 174 * Call this with an LSM_SCHEMA map, its colname is the table name and its 175 * type information describes the toplevel type. Schema is dereferenced and 176 * put in args before the actual sq3 query, which is given the child map. 177 */ 178 179int 180lws_struct_sq3_deserialize(sqlite3 *pdb, const char *filter, const char *order, 181 const lws_struct_map_t *schema, lws_dll2_owner_t *o, 182 struct lwsac **ac, int start, int _limit) 183{ 184 int limit = _limit < 0 ? -_limit : _limit; 185 char s[768], results[512], where[250]; 186 lws_struct_args_t a; 187 int n, m; 188 189 if (!order) 190 order = "_lws_idx"; 191 192 memset(&a, 0, sizeof(a)); 193 a.ac = *ac; 194 a.cb_arg = o; /* lws_dll2_owner tracking query result objects */ 195 a.map_st[0] = schema->child_map; 196 a.map_entries_st[0] = schema->child_map_size; 197 a.dest_len = schema->aux; /* size of toplevel object to allocate */ 198 a.toplevel_dll2_ofs = schema->ofs; 199 200 lws_dll2_owner_clear(o); 201 202 /* 203 * Explicitly list the columns instead of use *, so we can skip blobs 204 */ 205 206 m = 0; 207 for (n = 0; n < (int)schema->child_map_size; n++) 208 m += lws_snprintf(&results[m], sizeof(results) - (unsigned int)n - 1, 209 "%s%c", schema->child_map[n].colname, 210 n + 1 == (int)schema->child_map_size ? ' ' : ','); 211 212 where[0] = '\0'; 213 lws_snprintf(where, sizeof(where), " where _lws_idx >= %llu %s", 214 (unsigned long long)start, filter ? filter : ""); 215 216 lws_snprintf(s, sizeof(s) - 1, "select %s " 217 "from %s %s order by %s %slimit %d;", results, 218 schema->colname, where, order, 219 _limit < 0 ? "desc " : "", limit); 220 221 222 223 if (sqlite3_exec(pdb, s, lws_struct_sq3_deser_cb, &a, NULL) != SQLITE_OK) { 224 lwsl_err("%s: %s: fail %s\n", __func__, sqlite3_errmsg(pdb), s); 225 lwsac_free(&a.ac); 226 return -1; 227 } 228 229 *ac = a.ac; 230 231 return 0; 232} 233 234/* 235 * This takes a struct and turns it into an sqlite3 UPDATE, using the given 236 * schema... which has one LSM_SCHEMA_DLL2 entry wrapping the actual schema 237 */ 238 239static int 240_lws_struct_sq3_ser_one(sqlite3 *pdb, const lws_struct_map_t *schema, 241 uint32_t idx, void *st) 242{ 243 const lws_struct_map_t *map = schema->child_map; 244 int n, m, pk = 0, nentries = (int)(ssize_t)schema->child_map_size, nef = 0, did; 245 size_t sql_est = 46 + strlen(schema->colname) + 1; 246 /* "insert into (_lws_idx, ) values (00000001,);" ... 247 * plus the table name */ 248 uint8_t *stb = (uint8_t *)st; 249 const char *p; 250 char *sql; 251 252 /* 253 * Figure out effective number of columns, exluding BLOB. 254 * 255 * The first UNSIGNED is a hidden index. Blobs are not handled by 256 * lws_struct except to create the column in the schema. 257 */ 258 259 pk = 0; 260 nef = 0; 261 for (n = 0; n < nentries; n++) { 262 if (!pk && map[n].type == LSMT_UNSIGNED) { 263 pk = 1; 264 continue; 265 } 266 if (map[n].type == LSMT_BLOB_PTR) 267 continue; 268 269 nef++; 270 } 271 272 /* 273 * Figure out an estimate for the length of the populated sqlite 274 * command, and then malloc it up 275 */ 276 277 for (n = 0; n < nentries; n++) { 278 sql_est += strlen(map[n].colname) + 2; 279 switch (map[n].type) { 280 case LSMT_SIGNED: 281 case LSMT_UNSIGNED: 282 case LSMT_BOOLEAN: 283 284 switch (map[n].aux) { 285 case 1: 286 sql_est += 3 + 2; 287 break; 288 case 2: 289 sql_est += 5 + 2; 290 break; 291 case 4: 292 sql_est += 10 + 2; 293 break; 294 case 8: 295 sql_est += 20 + 2; 296 break; 297 } 298 299 if (map[n].type == LSMT_SIGNED) 300 sql_est++; /* minus sign */ 301 302 break; 303 case LSMT_STRING_CHAR_ARRAY: 304 sql_est += (unsigned int)lws_sql_purify_len((const char *)st + 305 map[n].ofs) + 2; 306 break; 307 308 case LSMT_STRING_PTR: 309 p = *((const char * const *)&stb[map[n].ofs]); 310 sql_est += (unsigned int)((p ? lws_sql_purify_len(p) : 0) + 2); 311 break; 312 313 case LSMT_BLOB_PTR: 314 /* we don't deal with blobs actually */ 315 sql_est -= strlen(map[n].colname) + 2; 316 break; 317 318 default: 319 lwsl_err("%s: unsupported type\n", __func__); 320 assert(0); 321 break; 322 } 323 } 324 325 sql = malloc(sql_est); 326 if (!sql) 327 return -1; 328 329 m = lws_snprintf(sql, sql_est, "insert into %s(_lws_idx, ", 330 schema->colname); 331 332 /* 333 * First explicit integer type is primary key autoincrement, should 334 * not be specified 335 */ 336 337 pk = 0; 338 did = 0; 339 for (n = 0; n < nentries; n++) { 340 if (!pk && map[n].type == LSMT_UNSIGNED) { 341 pk = 1; 342 continue; 343 } 344 if (map[n].type == LSMT_BLOB_PTR) 345 continue; 346 347 did++; 348 m += lws_snprintf(sql + m, sql_est - (unsigned int)m, 349 did == nef ? "%s" : "%s, ", 350 map[n].colname); 351 } 352 353 m += lws_snprintf(sql + m, sql_est - (unsigned int)m, ") values(%u, ", idx); 354 355 pk = 0; 356 did = 0; 357 for (n = 0; n < nentries; n++) { 358 uint64_t uu64; 359 size_t q; 360 361 if (!pk && map[n].type == LSMT_UNSIGNED) { 362 pk = 1; 363 continue; 364 } 365 366 switch (map[n].type) { 367 case LSMT_SIGNED: 368 case LSMT_UNSIGNED: 369 case LSMT_BOOLEAN: 370 371 uu64 = 0; 372 for (q = 0; q < map[n].aux; q++) 373 uu64 |= ((uint64_t)stb[map[n].ofs + q] << 374 (q << 3)); 375 376 if (map[n].type == LSMT_SIGNED) 377 m += lws_snprintf(sql + m, sql_est - (unsigned int)m, "%lld", 378 (long long)(int64_t)uu64); 379 else 380 m += lws_snprintf(sql + m, sql_est - (unsigned int)m, "%llu", 381 (unsigned long long)uu64); 382 break; 383 384 case LSMT_STRING_CHAR_ARRAY: 385 sql[m++] = '\''; 386 lws_sql_purify(sql + m, (const char *)&stb[map[n].ofs], 387 sql_est - (size_t)(ssize_t)m - 4); 388 m += (int)(ssize_t)strlen(sql + m); 389 sql[m++] = '\''; 390 break; 391 case LSMT_STRING_PTR: 392 p = *((const char * const *)&stb[map[n].ofs]); 393 sql[m++] = '\''; 394 if (p) { 395 lws_sql_purify(sql + m, p, sql_est - (unsigned int)m - 4); 396 m += (int)(ssize_t)strlen(sql + m); 397 } 398 sql[m++] = '\''; 399 break; 400 401 case LSMT_BLOB_PTR: 402 continue; 403 404 default: 405 lwsl_err("%s: unsupported type\n", __func__); 406 assert(0); 407 break; 408 } 409 410 did++; 411 if (did != nef) { 412 if (sql_est - (unsigned int)m < 6) 413 return -1; 414 sql[m++] = ','; 415 sql[m++] = ' '; 416 } 417 } 418 419 lws_snprintf(sql + m, sql_est - (unsigned int)m, ");"); 420 421 n = sqlite3_exec(pdb, sql, NULL, NULL, NULL); 422 if (n != SQLITE_OK) { 423 lwsl_err("%s\n", sql); 424 free(sql); 425 lwsl_err("%s: %s: fail\n", __func__, sqlite3_errmsg(pdb)); 426 return -1; 427 } 428 free(sql); 429 430 return 0; 431} 432 433int 434lws_struct_sq3_serialize(sqlite3 *pdb, const lws_struct_map_t *schema, 435 lws_dll2_owner_t *owner, uint32_t manual_idx) 436{ 437 uint32_t idx = manual_idx; 438 439 lws_start_foreach_dll(struct lws_dll2 *, p, owner->head) { 440 void *item = (void *)((uint8_t *)p - schema->ofs_clist); 441 if (_lws_struct_sq3_ser_one(pdb, schema, idx++, item)) 442 return 1; 443 444 } lws_end_foreach_dll(p); 445 446 return 0; 447} 448 449int 450lws_struct_sq3_create_table(sqlite3 *pdb, const lws_struct_map_t *schema) 451{ 452 const lws_struct_map_t *map = schema->child_map; 453 int map_size = (int)(ssize_t)schema->child_map_size, subsequent = 0; 454 char s[2048], *p = s, *end = &s[sizeof(s) - 1], 455 *pri = " primary key autoincrement", *use; 456 457 p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p), 458 "create table if not exists %s (_lws_idx integer, ", 459 schema->colname); 460 461 while (map_size--) { 462 if (map->type > LSMT_STRING_PTR && map->type != LSMT_BLOB_PTR) { 463 map++; 464 continue; 465 } 466 if (subsequent && (end - p) > 4) { 467 *p++ = ','; 468 *p++ = ' '; 469 } 470 subsequent = 1; 471 if (map->type == LSMT_BLOB_PTR) { 472 473 p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p), "%s blob", map->colname); 474 475 } else { 476 if (map->type < LSMT_STRING_CHAR_ARRAY) { 477 use = ""; 478 if (map->colname[0] != '_') /* _lws_idx is not primary key */ 479 use = pri; 480 p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p), "%s integer%s", 481 map->colname, use); 482 if (map->colname[0] != '_') 483 pri = ""; 484 } else 485 p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p), "%s varchar", 486 map->colname); 487 } 488 489 map++; 490 } 491 492 p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p), ");"); 493 494 if (sqlite3_exec(pdb, s, NULL, NULL, NULL) != SQLITE_OK) { 495 lwsl_err("%s: %s: fail\n", __func__, sqlite3_errmsg(pdb)); 496 497 return -1; 498 } 499 500 return 0; 501} 502 503int 504lws_struct_sq3_open(struct lws_context *context, const char *sqlite3_path, 505 char create_if_missing, sqlite3 **pdb) 506{ 507#if !defined(WIN32) 508 uid_t uid = 0; 509 gid_t gid = 0; 510#endif 511 512 if (sqlite3_open_v2(sqlite3_path, pdb, 513 SQLITE_OPEN_READWRITE | 514 (create_if_missing ? SQLITE_OPEN_CREATE : 0), 515 NULL) != SQLITE_OK) { 516 lwsl_info("%s: Unable to open db %s: %s\n", 517 __func__, sqlite3_path, sqlite3_errmsg(*pdb)); 518 519 return 1; 520 } 521 522#if !defined(WIN32) 523 lws_get_effective_uid_gid(context, &uid, &gid); 524 if (uid) 525 if (chown(sqlite3_path, uid, gid)) 526 lwsl_err("%s: failed to chown %s\n", __func__, sqlite3_path); 527 chmod(sqlite3_path, 0600); 528 529 lwsl_debug("%s: created %s owned by %u:%u mode 0600\n", __func__, 530 sqlite3_path, (unsigned int)uid, (unsigned int)gid); 531#else 532 lwsl_debug("%s: created %s\n", __func__, sqlite3_path); 533#endif 534 sqlite3_extended_result_codes(*pdb, 1); 535 536 return 0; 537} 538 539int 540lws_struct_sq3_close(sqlite3 **pdb) 541{ 542 int n; 543 544 if (!*pdb) 545 return 0; 546 547 n = sqlite3_close(*pdb); 548 if (n != SQLITE_OK) { 549 /* 550 * trouble... 551 */ 552 lwsl_err("%s: failed to close: %d\n", __func__, n); 553 return 1; 554 } 555 *pdb = NULL; 556 557 return 0; 558} 559