1: <?php
  2: /**
  3:  * Sam SQL storage implementation using Horde_Db.
  4:  *
  5:  * Copyright 2003-2012 Horde LLC (http://www.horde.org/)
  6:  *
  7:  * See the enclosed file COPYING for license information (GPL). If you
  8:  * did not receive this file, see http://www.horde.org/licenses/gpl.
  9:  *
 10:  * @author  Max Kalika <max@horde.org>
 11:  * @author  Jan Schneider <jan@horde.org>
 12:  * @package Sam
 13:  */
 14: class Sam_Driver_Amavisd_Sql extends Sam_Driver_Base
 15: {
 16:     /**
 17:      * Handle for the current database connection.
 18:      *
 19:      * @var Horde_Db_Adapter
 20:      */
 21:     protected $_db;
 22: 
 23:     /**
 24:      * List of the capabilities supported by this driver.
 25:      *
 26:      * @var array
 27:      */
 28:     protected $_capabilities = array('tag_level',
 29:                                      'hit_level',
 30:                                      'kill_level',
 31:                                      'rewrite_sub',
 32:                                      'spam_extension',
 33:                                      'virus_extension',
 34:                                      'banned_extension',
 35:                                      'spam_quarantine',
 36:                                      'allow_virus',
 37:                                      'allow_spam',
 38:                                      'allow_banned',
 39:                                      'allow_header',
 40:                                      'skip_virus',
 41:                                      'skip_spam',
 42:                                      'skip_banned',
 43:                                      'skip_header',
 44:                                      'whitelist_from',
 45:                                      'blacklist_from');
 46: 
 47:     /**
 48:      * Constructor.
 49:      *
 50:      * @param string $user   A user name.
 51:      * @param array $params  Class parameters:
 52:      *                       - db:    (Horde_Db_Adapater) A database handle.
 53:      *                       - table_map: (array) A map of table and field
 54:      *                         names. See config/backends.php.
 55:      */
 56:     public function __construct($user, $params = array())
 57:     {
 58:         foreach (array('db', 'table_map') as $param) {
 59:             if (!isset($params[$param])) {
 60:                 throw new InvalidArgumentException(
 61:                     sprintf('"%s" parameter is missing', $param));
 62:             }
 63:         }
 64: 
 65:         $this->_db = $params['db'];
 66:         unset($params['db']);
 67: 
 68:         parent::__construct($user, $params);
 69:     }
 70: 
 71:     /**
 72:      * Retrieves user preferences from the backend.
 73:      *
 74:      * @throws Sam_Exception
 75:      */
 76:     public function retrieve()
 77:     {
 78:         /* Find the user id. */
 79:         $userID = $this->_lookupUserID();
 80: 
 81:         /* Find the policy id. */
 82:         $policyID = $this->_lookupPolicyID();
 83: 
 84:         /* Query for SPAM policy. */
 85:         try {
 86:             $result = $this->_db->selectOne(
 87:                 sprintf('SELECT * FROM %s WHERE %s = ?',
 88:                         $this->_mapNameToTable('policies'),
 89:                         $this->_mapAttributeToField('policies', 'id')),
 90:                 array($policyID));
 91:         } catch (Horde_Db_Exception $e) {
 92:             throw new Sam_Exception($e);
 93:         }
 94: 
 95:         /* Loop through elements of the result, retrieving options. */
 96:         if ($result) {
 97:             foreach ($result as $field => $value) {
 98:                 $attribute = $this->_mapFieldToAttribute('policies', $field);
 99:                 if ($this->hasCapability($attribute) && !is_null($value)) {
100:                     $this->_options[$attribute] = $value;
101:                 }
102:             }
103:         }
104: 
105:         /* Query for whitelists and blacklists. */
106:         try {
107:             $result = $this->_db->select(
108:                 sprintf('SELECT %s, %s FROM %s WHERE %s = ?',
109:                         $this->_mapAttributeToField('wblists', 'sender'),
110:                         $this->_mapAttributeToField('wblists', 'type'),
111:                         $this->_mapNameToTable('wblists'),
112:                         $this->_mapAttributeToField('wblists', 'recipient')),
113:                 array($userID));
114:         } catch (Horde_Db_Exception $e) {
115:             throw new Sam_Exception($e);
116:         }
117: 
118:         /* Loop through results, retrieving whitelists and blacklists. */
119:         foreach ($result as $row) {
120:             $type = $row[$this->_mapAttributeToField('wblists', 'type')];
121:             $senderID = $row[$this->_mapAttributeToField('wblists', 'sender')];
122: 
123:             /* Only proceed if sender is listed white or black. */
124:             if (preg_match('/[WYBN]/i', $type)) {
125:                 try {
126:                     $sender = $this->_db->selectValue(
127:                         sprintf('SELECT %s FROM %s WHERE %s = ?',
128:                                 $this->_mapAttributeToField('senders', 'email'),
129:                                 $this->_mapNameToTable('senders'),
130:                                 $this->_mapAttributeToField('senders', 'id')),
131:                         array($senderID));
132:                 } catch (Horde_Db_Exception $e) {
133:                     throw new Sam_Exception($e);
134:                 }
135: 
136:                 $list = preg_match('/[WY]/i', $type)
137:                     ? 'whitelist_from'
138:                     : 'blacklist_from';
139:                 if (isset($this->_options[$list])) {
140:                     if (!in_array($sender, $this->_options[$list])) {
141:                         $this->_options[$list][] = $sender;
142:                     }
143:                 } else {
144:                     $this->_options[$list] = array($sender);
145:                 }
146:             }
147:         }
148:     }
149: 
150:     /**
151:      * Stores user preferences and default values in the backend.
152:      *
153:      * @param boolean $defaults  Whether to store the global defaults instead
154:      *                           of user options. Unused.
155:      *
156:      * @throws Sam_Exception
157:      */
158:     public function store($defaults = false)
159:     {
160:         /* Check if the policy already exists. */
161:         $policyID = $this->_lookupPolicyID();
162: 
163:         /* Delete existing policy. */
164:         if ($policyID !== false) {
165:             try {
166:                 $this->_db->delete(
167:                     sprintf('DELETE FROM %s WHERE %s = ?',
168:                             $this->_mapNameToTable('policies'),
169:                             $this->_mapAttributeToField('policies', 'name')),
170:                     array($this->_user));
171:             } catch (Horde_Db_Exception $e) {
172:                 throw new Sam_Exception($e);
173:             }
174:         }
175: 
176:         /* Insert new policy (everything but whitelists and blacklists). */
177:         $insertKeys = $insertVals = array();
178:         foreach ($this->_options as $attribute => $value) {
179:             if ($attribute != 'whitelist_from' &&
180:                 $attribute != 'blacklist_from') {
181:                 $insertKeys[] = $this->_mapAttributeToField('policies', $attribute);
182:                 $insertVals[] = strlen($value) ? $value : null;
183:             }
184:         }
185:         if (count($insertKeys)) {
186:             try {
187:                 $this->_db->insert(
188:                     sprintf('INSERT INTO %s (%s, %s) VALUES (%s)',
189:                             $this->_mapNameToTable('policies'),
190:                             $this->_mapAttributeToField('policies', 'name'),
191:                             implode(', ', $insertKeys),
192:                             implode(', ', array_fill(0, count($insertVals) + 1, '?'))),
193:                     array_merge(array($this->_user), $insertVals));
194:             } catch (Horde_Db_Exception $e) {
195:                 throw new Sam_Exception($e);
196:             }
197:         }
198: 
199:         /* Get the new policy id for the recipients table. */
200:         $policyID = $this->_lookupPolicyID();
201: 
202:         /* Update recipients with new policy id. */
203:         try {
204:             $this->_db->update(
205:                 sprintf('UPDATE %s SET %s = ? WHERE %s = ?',
206:                          $this->_mapNameToTable('recipients'),
207:                          $this->_mapAttributeToField('recipients', 'policy_id'),
208:                          $this->_mapAttributeToField('recipients', 'email')),
209:                 array($policyID, $this->_user));
210:         } catch (Horde_Db_Exception $e) {
211:             throw new Sam_Exception($e);
212:         }
213: 
214:         /* Find the user id. */
215:         $userID = $this->_lookupUserID();
216: 
217:         /* Query for whitelists and blacklists. */
218:         try {
219:             $result = $this->_db->select(
220:                 sprintf('SELECT %s, %s FROM %s WHERE %s = ?',
221:                         $this->_mapAttributeToField('wblists', 'sender'),
222:                         $this->_mapAttributeToField('wblists', 'type'),
223:                         $this->_mapNameToTable('wblists'),
224:                         $this->_mapAttributeToField('wblists', 'recipient')),
225:                 array($userID));
226:         } catch (Horde_Db_Exception $e) {
227:             throw new Sam_Exception($e);
228:         }
229: 
230:         /* Loop through results, retrieving whitelists and blacklists. */
231:         $existing = array('whitelist_from' => array(),
232:                           'blacklist_from' => array());
233:         foreach ($result as $row) {
234:             $type = $row[$this->_mapAttributeToField('wblists', 'type')];
235:             $senderID = $row[$this->_mapAttributeToField('wblists', 'sender')];
236: 
237:             /* Only proceed if sender is listed white or black. */
238:             if (preg_match('/[WYBN]/i', $type)) {
239:                 try {
240:                     $sender = $this->_db->selectValue(
241:                         sprintf('SELECT %s FROM %s WHERE %s = ?',
242:                                 $this->_mapAttributeToField('senders', 'email'),
243:                                 $this->_mapNameToTable('senders'),
244:                                 $this->_mapAttributeToField('senders', 'id')),
245:                         array($senderID));
246:                 } catch (Horde_Db_Exception $e) {
247:                     throw new Sam_Exception($e);
248:                 }
249: 
250:                 $list = preg_match('/[WY]/i', $type)
251:                     ? 'whitelist_from'
252:                     : 'blacklist_from';
253: 
254:                 if (isset($this->_options[$list]) &&
255:                     in_array($sender, $this->_options[$list])) {
256:                     $existing[$list][] = $sender;
257:                 } else {
258:                     /* User removed an address from a list. */
259:                     try {
260:                         $this->_db->delete(
261:                             sprintf('DELETE FROM %s WHERE %s = ? AND %s = ?',
262:                                     $this->_mapNameToTable('wblists'),
263:                                     $this->_mapAttributeToField('wblists', 'sender'),
264:                                     $this->_mapAttributeToField('wblists', 'recipient')),
265:                             array($senderID, $userID));
266:                     } catch (Horde_Db_Exception $e) {
267:                         throw new Sam_Exception($e);
268:                     }
269: 
270:                     /* Check if there is anyone else using this sender
271:                      * address. */
272:                     $query = sprintf('SELECT 1 FROM %s WHERE %s = ?',
273:                                      $this->_mapNameToTable('wblists'),
274:                                      $this->_mapAttributeToField('wblists', 'sender'));
275:                     if (!$this->_db->selectValue($query, array($senderID))) {
276:                         /* No one else needs this sender address, delete it
277:                          * from senders table. */
278:                         try {
279:                             $this->_db->delete(
280:                                 sprintf('DELETE FROM %s WHERE %s = ?',
281:                                          $this->_mapNameToTable('senders'),
282:                                          $this->_mapAttributeToField('senders', 'id')),
283:                                 array($senderID));
284:                         } catch (Horde_Db_Exception $e) {
285:                             throw new Sam_Exception($e);
286:                         }
287:                     }
288:                 }
289:             }
290:         }
291: 
292:         /* Check any additions to the lists. */
293:         foreach (array('whitelist_from' => 'W', 'blacklist_from' => 'B') as $list => $type) {
294:             if (!isset($this->_options[$list])) {
295:                 continue;
296:             }
297: 
298:             foreach ($this->_options[$list] as $sender) {
299:                 if (in_array($sender, $existing[$list])) {
300:                     continue;
301:                 }
302: 
303:                 /* Check if this sender address exists already. */
304:                 $wb_result = $this->_db->selectValue(
305:                     sprintf('SELECT %s FROM %s WHERE %s = ?',
306:                             $this->_mapAttributeToField('senders', 'id'),
307:                             $this->_mapNameToTable('senders'),
308:                             $this->_mapAttributeToField('senders', 'email')),
309:                     array($sender));
310: 
311:                 if ($wb_result !== false) {
312:                     /* Address exists, use it's ID */
313:                     $senderID = $wb_result;
314:                 } else {
315:                     /* Address doesn't exist, add it. */
316:                     try {
317:                         $this->_db->insert(
318:                             sprintf('INSERT INTO %s (%s) VALUES (?)',
319:                                     $this->_mapNameToTable('senders'),
320:                                     $this->_mapAttributeToField('senders', 'email')),
321:                             array($sender));
322:                     } catch (Horde_Db_Exception $e) {
323:                         throw new Sam_Exception($e);
324:                     }
325: 
326:                     try {
327:                         $senderID = $this->_db->selectValue(
328:                             sprintf('SELECT %s FROM %s WHERE %s = ?',
329:                                     $this->_mapAttributeToField('senders', 'id'),
330:                                     $this->_mapNameToTable('senders'),
331:                                     $this->_mapAttributeToField('senders', 'email')),
332:                             array($sender));
333:                     } catch (Horde_Db_Exception $e) {
334:                         throw new Sam_Exception($e);
335:                     }
336:                 }
337: 
338:                 try {
339:                     $this->_db->insert(
340:                         sprintf('INSERT INTO %s (%s, %s, %s) VALUES (?, ?, ?)',
341:                                 $this->_mapNameToTable('wblists'),
342:                                 $this->_mapAttributeToField('wblists', 'recipient'),
343:                                 $this->_mapAttributeToField('wblists', 'sender'),
344:                                 $this->_mapAttributeToField('wblists', 'type')),
345:                         array($userID, $senderID, $type));
346:                 } catch (Horde_Db_Exception $e) {
347:                     throw new Sam_Exception($e);
348:                 }
349:             }
350:         }
351: 
352:         /* Remove any disjoined sender IDs. */
353:         try {
354:             $this->_db->delete(
355:                 sprintf('DELETE FROM %s WHERE %s = ?',
356:                         $this->_mapNameToTable('wblists'),
357:                         $this->_mapAttributeToField('wblists', 'recipient')),
358:                 array(''));
359:         } catch (Horde_Db_Exception $e) {
360:             throw new Sam_Exception($e);
361:         }
362:     }
363: 
364:     /**
365:      * Converts a boolean option to a backend specific value.
366:      *
367:      * @param boolean $boolean  The value to convert.
368:      *
369:      * @return mixed  Y if true and N if false.
370:      */
371:     public function booleanToOption($boolean)
372:     {
373:         return $boolean ? 'Y' : 'N';
374:     }
375: 
376:     /**
377:      * Converts a Sam table name to a table name that Amavisd-new will use.
378:      *
379:      * @param string $table  The Sam table to lookup.
380:      *
381:      * @return string  The converted Amavisd-new table or the original name if
382:      *                 no match is found.
383:      */
384:     protected function _mapNameToTable($table)
385:     {
386:         return isset($this->_params['table_map'][$table]['name'])
387:             ? $this->_params['table_map'][$table]['name']
388:             : $table;
389:     }
390: 
391:     /**
392:      * Converts a Sam attribute from a specific table to a field that
393:      * Amavisd-new will use.
394:      *
395:      * @param string $table      The Sam table to lookup.
396:      * @param string $attribute  The Sam attribute to convert.
397:      *
398:      * @return string  The converted Amavisd-new field or the original
399:      *                 attribute if no match is found.
400:      */
401:     protected function _mapAttributeToField($table, $attribute)
402:     {
403:         return isset($this->_params['table_map'][$table]['field_map'][$attribute])
404:             ? $this->_params['table_map'][$table]['field_map'][$attribute]
405:             : $attribute;
406:     }
407: 
408:     /**
409:      * Converts a Amavisd-new field from a specific table to a Sam attribute.
410:      *
411:      * @param string $table  The Sam table to lookup.
412:      * @param string $field  The Amavisd-new field to convert.
413:      *
414:      * @return string  The converted Sam attribute or the original field if no
415:      *                 match is found.
416:      */
417:     protected function _mapFieldToAttribute($table, $field)
418:     {
419:         $attribute_map = array();
420:         if (isset($this->_params['table_map'][$table]['field_map'])) {
421:             $attribute_map = array_flip($this->_params['table_map'][$table]['field_map']);
422:         }
423: 
424:         return isset($attribute_map[$field]) ? $attribute_map[$field] : $field;
425:     }
426: 
427:     /**
428:      * Creates an Amavisd-new recipient for policy, whitelist and blacklist
429:      * storage and retrieval.
430:      *
431:      * @return string  The id of the newly created recipient.
432:      * @throws Sam_Exception
433:      */
434:     protected function _createUserID()
435:     {
436:         try {
437:             $this->_db->insert(
438:                 sprintf('INSERT INTO %s (%s) VALUES (?)',
439:                         $this->_mapNameToTable('recipients'),
440:                         $this->_mapAttributeToField('recipients', 'email')),
441:                 array($this->_user));
442:         } catch (Horde_Db_Exception $e) {
443:             throw new Sam_Exception(sprintf(_("Cannot create recipient %s: %s"),
444:                                             $this->_user, $e->getMessage()));
445:         }
446:         $GLOBALS['notification']->push(sprintf(_("Recipient created: %s"),
447:                                                $this->_user), 'horde.success');
448:         return $this->_lookupUserID();
449:     }
450: 
451:     /**
452:      * Returns an Amavisd-new recipient for policy, whitelist and blacklist
453:      * storage and retrieval.
454:      *
455:      * @return string  The ID of the found or newly created recipient.
456:      * @throws Sam_Exception
457:      */
458:     protected function _lookupUserID()
459:     {
460:         try {
461:             $userID = $this->_db->selectValue(
462:                 sprintf('SELECT %s FROM %s WHERE %s = ?',
463:                         $this->_mapAttributeToField('recipients', 'id'),
464:                         $this->_mapNameToTable('recipients'),
465:                         $this->_mapAttributeToField('recipients', 'email')),
466:                 array($this->_user));
467:         } catch (Horde_Db_Exception $e) {
468:             throw new Sam_Exception($e);
469:         }
470: 
471:         if ($userID === false) {
472:             $userID = $this->_createUserID();
473:         }
474: 
475:         return $userID;
476:     }
477: 
478:     /**
479:      * Returns an Amavisd-new policy for storage and retrieval.
480:      *
481:      * @return string  The results of the of the policy lookup. Can be the ID
482:      *                 of the policy, false if not found.
483:      * @throws Sam_Exception
484:      */
485:     protected function _lookupPolicyID()
486:     {
487:         try {
488:             return $this->_db->selectValue(
489:                 sprintf('SELECT %s FROM %s WHERE %s = ?',
490:                         $this->_mapAttributeToField('policies', 'id'),
491:                         $this->_mapNameToTable('policies'),
492:                         $this->_mapAttributeToField('policies', 'name')),
493:                 array($this->_user));
494:         } catch (Horde_Db_Exception $e) {
495:             return false;
496:         }
497:     }
498: }
499: