> On 12 Jul 2021, at 11:53 am, post...@ptld.com wrote:
>
> query = SELECT CONCAT(IF(spfVerify, 'verify_spfpolicy,', ''),
> IF(senderVerify, 'reject_unverified_sender,', '')) AS restrictions FROM
> settings WHERE email='%s' LIMIT 1
This is not a well thought out query. Avoid CONCAT, instead you could use:
SELECT 'verify_spfpolicy'
FROM settings
WHERE email='%s' and spfVerify LIMIT 1
UNION
SELECT 'reject_unverified_sender'
FROM settings
WHERE email='%s' and senderVerify LIMIT 1
but frankly part of the problem is the schema. Instead of a row with two
boolean
fields, why not one row per restriction with a priority ordinal to ensure
correct
ordering:
SCHEMA:
CREATE TABLE mailbox_restrictions
( email text
, priority int
, restriction text
, PRIMARY KEY (email, priority)
);
EXAMPLE DATA:
INSERT INTO mailbox_restrictions (email, priority, restriction) VALUES
( 'j...@example.net', 100, 'verify_spfpolicy' ),
( 'j...@example.net', 200, 'reject_unverified_sender');
( 'f...@example.net', 100, 'verify_spfpolicy' );
QUERY:
SELECT T.restriction
FROM (SELECT restriction, priority
FROM mailbox_restrictions
WHERE email = '%s'
ORDER BY priority) AS T;
--
Viktor.
