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