1<?php 2// $Author: perlstalker $ 3// $Id: sasql_db.php,v 1.18 2006/11/08 15:24:39 perlstalker Exp $ 4 5require_once('DB.php'); 6 7if (!defined('SM_PATH')) { define('SM_PATH' , '../../'); } 8include_once(SM_PATH.'include/load_prefs.php'); 9include_once(SM_PATH.'plugins/sasql/sasql_conf.php'); 10 11global $columns; 12 13sqgetGlobalVar('color', $color, SQ_SESSION); 14 15if (!isset ($columns)) { 16 $columns = array(); 17} 18 19if (!isset($columns['username'])) { 20 $columns['username'] = 'username'; 21} 22 23if (!isset($columns['preference'])) { 24 $columns['preference'] = 'preference'; 25} 26 27if (!isset($columns['value'])) { 28 $columns['value'] = 'value'; 29} 30 31if (!isset($columns['prefid'])) { 32 $columns['id'] = 'prefid'; 33} 34 35function sasql_DBConnect ( $dsn ) { 36 global $color; 37 $dbh = DB::connect ($dsn, true); 38 if (DB::isError($dbh) ) { # || DB::isWarning($dbh)) { 39 include_once (SM_PATH.'functions/display_messages.php'); 40 /* To translators: %s's are the function name 41 and PEAR generated error message. 42 */ 43 error_box (sprintf ( _("Database error in %s: %s"), 44 'sasql_DBConnect', 45 DB::errorMessage($dbh)), 46 $color); 47 return ""; 48 } 49 50 return $dbh; 51} 52 53function sasql_DBDisconnect ( $dbh ) { 54 $dbh->disconnect(); 55 return ""; 56} 57 58function sasql_ReadWBList ( $dbh, $table, $username ) { 59 global $columns, $color; 60 $query = sprintf ("SELECT * from %s WHERE %s = '%s' AND (%s = 'whitelist_from' OR %s = 'unwhitelist_from' OR %s = 'blacklist_from' OR %s = 'unblacklist_from' OR %s = 'whitelist_to') order by %s,%s asc", 61 $table, 62 $columns['username'], 63 $username, 64 $columns['preference'], 65 $columns['preference'], 66 $columns['preference'], 67 $columns['preference'], 68 $columns['preference'], 69 $columns['preference'], 70 $columns['value'] 71 ); 72 $res = $dbh->query($query); 73 if (DB::isError($res)) { 74 sasql_DBDisconnect($dbh); 75 include_once (SM_PATH.'functions/display_messages.php'); 76 /* To translators: %s's are the function name 77 and PEAR generated error message. 78 */ 79 error_box (sprintf ( _("Database error in %s: %s"), 80 'sasql_ReadWBList', 81 $res->getDebugInfo()), 82 $color); 83 return ""; 84 } 85 86 $wbList = array('whitelist_from' => array(), 87 'blacklist_from' => array(), 88 'unwhitelist_from' => array(), 89 'unblacklist_from' => array(), 90 'whitelist_to' => array()); 91 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { 92 array_push ($wbList[strtolower($row['preference'])], 93 array('value' => $row['value'], 94 'prefid' => $row['prefid'], 95 'preference' => strtolower($row['preference']))); 96 } 97 return $wbList; 98} 99 100// See if $addr is in $username's white or blacklist. 101// $wblist = 'w' => Whitelist 102// $wblist = 'b' => Blacklist 103function sasql_AddrInWBList ($dbh, $table, $username, $wblist, $addr) 104{ 105 global $columns; 106 $wlquery = sprintf ("SELECT * from %s where %s = '%s' and (%s = 'whitelist_from' and %s = '%s')", 107 $table, 108 $columns['username'], 109 $username, 110 $columns['preference'], 111 $columns['value'], 112 $addr 113 ); 114 $blquery = sprintf ("SELECT * from %s where %s = '%s' and (%s = 'blacklist_from' and %s = '%s')", 115 $table, 116 $columns['username'], 117 $username, 118 $columns['preference'], 119 $columns['value'], 120 $addr 121 ); 122 123 $res = false; 124 if ($wblist == 'w') { 125 #error_log("W Query: $wlquery"); 126 $res = $dbh->query($wlquery); 127 } elseif ($wblist == 'b') { 128 #error_log("B Query: $blquery"); 129 $res = $dbh->query($blquery); 130 } 131 132 $found = 0; 133 if (DB::isError($res)) { 134 // bad stuff 135 } else { 136 $row = $res->fetchRow(DB_FETCHMODE_ASSOC); 137 $found = $row['value']; 138 } 139 140 if ($found) { return 1; } 141 else { return 0; } 142} 143 144// Same as above but useful if you don't have an existing connection. 145// This may be removed at some later date because I think it may be 146// better to use _DBConnect+_AddrInWBList+_DBDisconnect instead. 147function sasql_AddrInWBList_dsn ($dsn, $table, $username, $wblist, $addr) 148{ 149 global $columns; 150 //connect 151 $dbh = sasql_DBConnect ($dsn); 152 if (DB::isError($dbh)) { 153 } else { 154 $found = sasql_AddrInWBList ($dbh, $table, $username, $wblist, $addr); 155 } 156 //disconnect 157 sasql_DBDisconnect ($dbh); 158 return $found; 159} 160 161// Check if $domain is in $username's white/blacklist 162// $wblist = 'w' => Whitelist 163// $wblist = 'b' => Blacklist 164function sasql_DomainInWBList ($dbh, $table, $username, $wblist, $domain) { 165 global $colunms; 166 $wlquery = sprintf ("SELECT * from %s where %s = '%s' and (%s like 'whitelist_%%' and %s like '%%@%s')", 167 $table, 168 $columns['username'], 169 $username, 170 $columns['preference'], 171 $columns['value'], 172 $domain 173 ); 174 $blquery = sprintf ("SELECT * from %s where %s = '%s' and (%s like 'blacklist_%%' and %s like '%%@%s')", 175 $table, 176 $columns['username'], 177 $username, 178 $columns['preference'], 179 $columns['value'], 180 $domain 181 ); 182 183 $res = false; 184 if ($wblist == 'w') { 185 $res = $dbh->query($wlquery); 186 } elseif ($wblist == 'b') { 187 $res = $dbh->query($blquery); 188 } 189 190 $found = 0; 191 if (DB::isError($res)) { 192 // bad stuff 193 } else { 194 $row = $res->fetchRow(DB_FETCHMODE_ASSOC); 195 $found = $row['value']; 196 } 197 198 if ($found) { return 1; } 199 else { return 0; } 200} 201 202//function sasql_WriteWBList ($dsn, $table, $username, $wbList) {} 203 204function sasql_ReadPrefs ( $dbh, $table, $username ) { 205 global $columns; 206 global $sa_params; 207 global $color; 208 $query = sprintf ("SELECT * from %s WHERE %s = '%s' AND (%s != 'whitelist_from' OR %s != 'blacklist_from') order by %s desc", 209 $table, 210 $columns['username'], 211 $username, 212 $columns['preference'], 213 $columns['preference'], 214 $columns['preference'] 215 ); 216 $res = $dbh->query($query); 217 if (DB::isError($res)) { 218 sasql_DBDisconnect($dbh); 219 include_once (SM_PATH.'functions/display_messages.php'); 220 /* To translators: %s's are the function name 221 and PEAR generated error message. 222 */ 223 error_box (sprintf ( _("Database error in %s: %s"), 224 'sasql_ReadPrefs', 225 $res->getDebugInfo()), 226 $color); 227 return ""; 228 } 229 230 $prefs = array(); 231 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { 232 $p = $row['preference']; 233 if (isset($sa_params[$row['preference']]['multiple']) 234 and $sa_params[$row['preference']]['multiple']) { 235 if (! isset($prefs[$row['preference']])) { 236 $prefs[$row['preference']] = array ('value' => array(), 237 'prefid' => array()); 238 } 239 if ($sa_params[$row['preference']]['one_line']) { 240 $xValues = explode (' ', $row['value']); 241 foreach ($xValues as $v) { 242 array_push ($prefs[$row['preference']]['value'], $v); 243 array_push ($prefs[$row['preference']]['prefid'], $row['prefid']); 244 } 245 } else { 246 array_push ($prefs[$row['preference']]['value'], $row['value']); 247 array_push ($prefs[$row['preference']]['prefid'], $row['prefid']); 248 } 249 } else { 250 $prefs[$row['preference']] = array ('value' => $row['value'], 251 'prefid' => $row['prefid']); 252 } 253 } 254 return $prefs; 255} 256 257//function sasql_WritePrefs ( $dsn, $table, $username, $prefs ) {} 258 259function sasql_UpdatePref ( $dbh, $table, $username, $prefid, $preference, $value ) { 260 global $columns; 261 global $color; 262 $query = sprintf ("UPDATE %s set %s ='%s', %s ='%s' where %s ='%s' and %s ='%s'", 263 $table, 264 $columns['value'], 265 $value, 266 $columns['preference'], 267 strtolower($preference), 268 $columns['id'], 269 $prefid, 270 $columns['username'], 271 $username 272 ); 273 $res = $dbh->query($query); 274 if (DB::isError($res)) { 275 sasql_DBDisconnect($dbh); 276 include_once (SM_PATH.'functions/display_messages.php'); 277 /* To translators: %s's are the function name 278 and PEAR generated error message. 279 */ 280 error_box (sprintf ( _("Database error in %s: %s"), 281 'sasql_UpdatePref', 282 $res->getDebugInfo()), 283 $color); 284 } 285} 286 287function sasql_UpdatePref_multiple ( $dbh, $table, $username, 288 $preference, $value ) { 289 global $columns; 290 global $color; 291 // NOTE: $value is an array 292 // There is probably a cleaner way to do this. I'll worry about it later. 293 // Delete all values for this preference and user. 294 global $sa_params; 295 $query = sprintf ("DELETE from %s where %s = '%s' and %s='%s'", 296 $table, 297 $columns['username'], 298 $username, 299 $columns['preference'], 300 $preference); 301 $res = $dbh->query($query); 302 if (DB::isError($res)) { 303 sasql_DBDisconnect($dbh); 304 include_once (SM_PATH.'functions/display_messages.php'); 305 /* To translators: %s's are the function name 306 and PEAR generated error message. 307 */ 308 error_box (sprintf ( _("Database error: %s"), 309 'sasql_UpdatePref_multiple', 310 //DB::errorMessage($dbh))); 311 $res->getDebugInfo()), 312 $color); 313 } 314 // (Re)Add the new values. 315 sasql_AddPref_multiple ($dbh, $table, $username, $preference, $value); 316} 317 318function sasql_AddPref ( $dbh, $table, $username, $preference, $value ) { 319 global $columns; 320 global $color; 321 $query = sprintf ("INSERT INTO %s (%s,%s,%s) VALUES('%s', '%s', '%s')", 322 $table, 323 $columns['username'], 324 $columns['value'], 325 $columns['preference'], 326 $username, 327 $value, 328 strtolower($preference)); 329 $res = $dbh->query($query); 330 if (DB::isError($res)) { 331 sasql_DBDisconnect($dbh); 332 include_once (SM_PATH.'functions/display_messages.php'); 333 /* To translators: %s's are the function name 334 and PEAR generated error message. 335 */ 336 error_box (sprintf ( _("Database error in %s: %s"), 337 'sasql_AddPref', 338 $res->getDebugInfo()), 339 $color); 340 } 341} 342 343function sasql_AddPref_multiple ( $dbh, $table, $username, 344 $preference, $value ) { 345 // NOTE: $value is an array 346 global $sa_params; 347 global $color; 348 if (is_array($value)) { 349 if ($sa_params[$preference]['one_line']) { 350 $one_val = implode (' ', $value); 351 sasql_AddPref ($dbh, $table, $username, $preference, $one_val); 352 } else { 353 foreach ($value as $v) { 354 $query = sprintf ("INSERT INTO %s (%s, %s, %s) VALUES('%s', '%s', '%s')", 355 $table, 356 $columns['username'], 357 $columns['value'], 358 $columns['preference'], 359 $username, 360 $v, 361 strtolower($preference)); 362 $res = $dbh->query($query); 363 if (DB::isError($res)) { 364 sasql_DBDisconnect($dbh); 365 include_once (SM_PATH.'functions/display_messages.php'); 366 error_box (sprintf ( _("sasql_AddPref_multiple Database error: %s"), 367 $res->getDebugInfo()), 368 $color); 369 } 370 } 371 } 372 } else { 373 sasql_AddPref ($dbh, $table, $username, $preference, $value); 374 } 375} 376 377function sasql_DelPref ( $dbh, $table, $username, $prefid ) { 378 global $columns; 379 global $color; 380 $query = sprintf ("DELETE from %s where %s='%s' and %s='%s'", 381 $table, 382 $columns['id'], 383 $prefid, 384 $columns['username'], 385 $username); 386 $res = $dbh->query($query); 387 if (DB::isError($res)) { 388 sasql_DBDisconnect($dbh); 389 include_once (SM_PATH.'functions/display_messages.php'); 390 /* To translators: %s's are the function name 391 and PEAR generated error message. 392 */ 393 error_box (sprintf ( _("Database error in %s: %s"), 394 'sasql_DelPref', 395 $res->getDebugInfo()), 396 $color); 397 } 398} 399 400function sasql_DelPref_value ($dbh, $table, $username, $pref, $value) { 401 global $columns; 402 global $color; 403 $query = sprintf ("DELETE from %s where %s='%s'" 404 . "and %s = '%s' and %s='%s'", 405 $table, 406 $columns['preference'], 407 $pref, 408 $columns['value'], 409 $value, 410 $columns['username'], 411 $username); 412 $res = $dbh->query($query); 413 if (DB::isError($res)) { 414 sasql_DBDisconnect($dbh); 415 include_once (SM_PATH.'functions/display_messages.php'); 416 /* To translators: %s's are the function name 417 and PEAR generated error message. 418 */ 419 error_box (sprintf ( _("Database error in %s: %s"), 420 'sasql_DelPref_value', 421 $res->getDebugInfo()), 422 $color); 423 } 424} 425 426function sasql_DelPref_name ($dbh, $table, $username, $pref) { 427 global $columns; 428 global $color; 429 $query = sprintf ("DELETE from %s where %s='%s'" 430 . " and %s='%s'", 431 $table, 432 $columns['preference'], 433 $pref, 434 $columns['username'], 435 $username); 436 $res = $dbh->query($query); 437 if (DB::isError($res)) { 438 sasql_DBDisconnect($dbh); 439 include_once (SM_PATH.'functions/display_messages.php'); 440 /* To translators: %s's are the function name 441 and PEAR generated error message. 442 */ 443 error_box (sprintf ( _("Database error %s: %s"), 444 'sasql_DelPref_name', 445 $res->getDebugInfo()), 446 $color); 447 } 448} 449 450function sasql_GetPref ( $dbh, $table, $username, $prefid ) { 451 global $columns; 452 global $color; 453 $query = sprintf ("SELECT * from %s WHERE %s = '%s' AND %s = %s", 454 $table, 455 $columns['username'], 456 $username, 457 $columns['id'], 458 $prefid); 459 $res = $dbh->query($query); 460 if (DB::isError($res)) { 461 sasql_DBDisconnect($dbh); 462 include_once (SM_PATH.'functions/display_messages.php'); 463 /* To translators: %s's are the function name 464 and PEAR generated error message. 465 */ 466 error_box (sprintf ( _("Database error in %s: %s"), 467 'sasql_GetPref', 468 $res->getDebugInfo()), 469 $color); 470 return ""; 471 } 472 473 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { 474 $pref = array ('value' => $row['value'], 475 'preference' => strtolower($row['preference']), 476 'prefid' => $row['prefid']); 477 } else { 478 $pref = ""; 479 } 480 return $pref; 481} 482 483// if you know the list and email address, then return the prefid 484// so that can be used in other function calls such as to delete an 485// address from the DB. 486function sasql_GetID ( $dbh, $table, $username, $preference, $value) { 487 global $columns; 488 global $color; 489 $query = sprintf ("SELECT * from %s WHERE %s = '%s' AND " . 490 "%s='%s' AND %s='%s'", 491 $table, 492 $columns['username'], 493 $username, 494 $columns['preference'], 495 $preference, 496 $columns['value'], 497 $value); 498 $res = $dbh->query($query); 499 if (DB::isError($res)) { 500 sasql_DBDisconnect($dbh); 501 include_once (SM_PATH.'functions/display_messages.php'); 502 /* To translators: %s's are the function name 503 and PEAR generated error message. 504 */ 505 error_box (sprintf ( _("Database error in %s: %s"), 506 'sasql_GetID', 507 $res->getDebugInfo()), 508 $color); 509 return ""; 510 } 511 512 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { 513 $pref = $row['prefid']; 514 } else { 515 $pref = ""; 516 } 517 return $pref; 518} 519 520function sasql_GetBayesStats ($dbh, $table, $username) { 521 global $bayes_vars_cols, $color; 522 523 $query = sprintf ("SELECT %s, %s from %s where %s = '%s'", 524 $bayes_vars_cols['spam_count'], 525 $bayes_vars_cols['ham_count'], 526 $table, 527 $bayes_vars_cols['username'], 528 $username); 529 $res = $dbh->query($query); 530 if (DB::isError($res)) { 531 sasql_DBDisconnect($dbh); 532 include_once (SM_PATH.'functions/display_messages.php'); 533 /* To translators: %s's are the function name 534 and PEAR generated error message. 535 */ 536 error_box (sprintf ( _("Database error in %s: %s"), 537 'sasql_GetID', 538 $res->getDebugInfo()), 539 $color); 540 return ""; 541 } 542 543 $counts = array(); 544 545 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { 546 $counts['spam_count'] = $row[$bayes_vars_cols['spam_count']]; 547 $counts['ham_count'] = $row[$bayes_vars_cols['ham_count']]; 548 } else { 549 $counts['spam_count'] = 0; 550 $counts['ham_count'] = 0; 551 } 552 return $counts; 553} 554 555?> 556