about mysql query and input keys

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

about mysql query and input keys

Manuel Mely-2
Hi there,

I'm designing a mail system with virtual domains and have the following requirement:

A virtual domain is able to send/receive mails to/from the Internet or to/from custom "external domains".
 
For that i'm using a MySQL table called  "domain_external_domain_association"  which connects my virtual domains table and another external_domain table.

Somewhere in my set of smtpd_restrictions_classes there is one class that uses a check_sender_access where i need to check if the sender domain (external domain) is in this "domain_external_domain_association" when the recipient domain is X (my virtual domain)

So the query looks like this:

query= SELECT [OK OR REJECT GOES HERE] FROM domain_external_domain_association AS deda JOIN domain as d ON deda.domain_id = d.id JOIN external_domain as ed ON deda.external_domain_id = ed.id WHERE d.name = 'HERE MY VDOMAIN' AND deda.receive = 1 AND ed.name = '%d'

Also, the sql query explains the problem by itself :)

I think this is not possible to do, at least in the way i want to do it, because i'm trying to use in the query some information that the check_sender_access is not aware of.

Am i right? is there something i'm missing? A workaround?

Greetings and good start in the week!

Manuel Mely


Reply | Threaded
Open this post in threaded view
|

Re: about mysql query and input keys

Phil Stracchino
On 10/7/18 7:45 PM, Manuel Mely wrote:

> Hi there,
>
> I'm designing a mail system with virtual domains and have the following
> requirement:
>
> A virtual domain is able to send/receive mails to/from the Internet or
> to/from custom "external domains".
>  
> For that i'm using a MySQL table called 
> "domain_external_domain_association"  which connects my virtual domains
> table and another external_domain table.
>
> Somewhere in my set of smtpd_restrictions_classes there is one class
> that uses a check_sender_access where i need to check if the sender
> domain (external domain) is in this "domain_external_domain_association"
> when the recipient domain is X (my virtual domain)
>
> So the query looks like this:
>
> query= SELECT [OK OR REJECT GOES HERE] FROM domain_external_domain_association  AS deda
> JOIN domain as d ON deda.domain_id = d.id
> JOIN external_domain as ed ON deda.external_domain_id = ed.id
> WHERE d.name = 'HERE MY VDOMAIN' AND deda.receive = 1 AND ed.name = '%d'
>
> Also, the sql query explains the problem by itself :)


Actually, no, it doesn't, entirely.  That isn't very clear at all.

What do the tables look like?
Is that '%d' a formatting placeholder intended to be replaced before the
query is evaluated, or are you trying to match ed.name against a string
containing a wildcard?  (If so, you need to be using ed.name LIKE '%d',
not ed.name = '%d'.)
Is ed.name an integer or s string?  If it's a string, you probably want
%s there, not %d.  %d is the formatting code for an integer.
And what do you mean by [OK OR REJECT GOES HERE]?  Are you trying to
SELECT a column, a string value, a string literal...?


> I think this is not possible to do, at least in the way i want to do it,
> because i'm trying to use in the query some information that the
> check_sender_access is not aware of.

Since you haven't provided enough information to make sense of your
query, it's rather difficult to tell that.



--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: about mysql query and input keys

Manuel Mely-2
Well the tables look basically like this:

domain: id (binary), name (string), ... other fields
external_domain: id (binary), name (string)
domain_external_domain_association: id(binary), domain__id(binary), external_domain(binary), receive (int), send (int)

So, the idea is that virtual domains (domain table) can be configured to send or receive from the internet or from/to allowed external domains. Therefore the domain_external_domain_association table.
So i can ask something like, for the virtual domain X, is there any association to external_domain entries.

query= SELECT [OK OR REJECT GOES HERE] FROM domain_external_domain_association  AS deda JOIN domain as d ON deda.domain_id = d.id JOIN external_domain as ed ON deda.external_domain_id = ed.id WHERE d.name = 'HERE MY VDOMAIN' AND deda.receive = 1 AND ed.name = '%d'

d.name => should be recipient domain.
ed.name => is the sender domain, which works fine with %d, as I have another check before with a similar query.

The problem is, I want to use in a check_sender_access information about the recipient (for the joins in the query), which i think does not work.

[OK OR REJECT GOES HERE] , it is just a place holder for this e-mail :)

On Mon, Oct 8, 2018 at 3:55 AM Phil Stracchino <[hidden email]> wrote:
On 10/7/18 7:45 PM, Manuel Mely wrote:
> Hi there,
>
> I'm designing a mail system with virtual domains and have the following
> requirement:
>
> A virtual domain is able to send/receive mails to/from the Internet or
> to/from custom "external domains".
>  
> For that i'm using a MySQL table called 
> "domain_external_domain_association"  which connects my virtual domains
> table and another external_domain table.
>
> Somewhere in my set of smtpd_restrictions_classes there is one class
> that uses a check_sender_access where i need to check if the sender
> domain (external domain) is in this "domain_external_domain_association"
> when the recipient domain is X (my virtual domain)
>
> So the query looks like this:
>
> query= SELECT [OK OR REJECT GOES HERE] FROM domain_external_domain_association  AS deda
> JOIN domain as d ON deda.domain_id = d.id
> JOIN external_domain as ed ON deda.external_domain_id = ed.id
> WHERE d.name = 'HERE MY VDOMAIN' AND deda.receive = 1 AND ed.name = '%d'
>
> Also, the sql query explains the problem by itself :)


Actually, no, it doesn't, entirely.  That isn't very clear at all.

What do the tables look like?
Is that '%d' a formatting placeholder intended to be replaced before the
query is evaluated, or are you trying to match ed.name against a string
containing a wildcard?  (If so, you need to be using ed.name LIKE '%d',
not ed.name = '%d'.)
Is ed.name an integer or s string?  If it's a string, you probably want
%s there, not %d.  %d is the formatting code for an integer.
And what do you mean by [OK OR REJECT GOES HERE]?  Are you trying to
SELECT a column, a string value, a string literal...?


> I think this is not possible to do, at least in the way i want to do it,
> because i'm trying to use in the query some information that the
> check_sender_access is not aware of.

Since you haven't provided enough information to make sense of your
query, it's rather difficult to tell that.



--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: about mysql query and input keys

Phil Stracchino
On 10/8/18 6:42 AM, Manuel Mely wrote:

> Well the tables look basically like this:
>
> domain: id (binary), name (string), ... other fields
> external_domain: id (binary), name (string)
> domain_external_domain_association: id(binary), domain__id(binary),
> external_domain(binary), receive (int), send (int)
>
> So, the idea is that virtual domains (domain table) can be configured to
> send or receive from the internet or from/to allowed external domains.
> Therefore the domain_external_domain_association table.
> So i can ask something like, for the virtual domain X, is there any
> association to external_domain entries.
>
> query= SELECT [OK OR REJECT GOES
> HERE] FROM domain_external_domain_association  AS deda
> JOIN domain as d ON deda.domain_id = d.id <http://d.id>
> JOIN external_domain as ed ON deda.external_domain_id = ed.id
> <http://ed.id> WHERE d.name <http://d.name> = 'HERE MY VDOMAIN' AND
> deda.receive = 1 AND ed.name <http://ed.name> = '%d'
>
> d.name <http://d.name> => should be recipient domain.
> ed.name <http://ed.name> => is the sender domain, which works fine with
> %d, as I have another check before with a similar query.

OK, so %d gets substituted with domain name?


> The problem is, I want to use in a check_sender_access information about
> the recipient (for the joins in the query), which i think does not work.

Yeah, I don't see anything wrong with the query itself, but whether
it'll work in a check_sender_access rule is another question, which I
can't answer because I've never tried to do that.


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958
Reply | Threaded
Open this post in threaded view
|

Re: about mysql query and input keys

Manuel Mely-2
I think I will have to implement my own SMTP Access Policy [1] in this case.



Phil Stracchino <[hidden email]> schrieb am Mo. 8. Okt. 2018 um 14:34:
On 10/8/18 6:42 AM, Manuel Mely wrote:
> Well the tables look basically like this:
>
> domain: id (binary), name (string), ... other fields
> external_domain: id (binary), name (string)
> domain_external_domain_association: id(binary), domain__id(binary),
> external_domain(binary), receive (int), send (int)
>
> So, the idea is that virtual domains (domain table) can be configured to
> send or receive from the internet or from/to allowed external domains.
> Therefore the domain_external_domain_association table.
> So i can ask something like, for the virtual domain X, is there any
> association to external_domain entries.
>
> query= SELECT [OK OR REJECT GOES
> HERE] FROM domain_external_domain_association  AS deda
> JOIN domain as d ON deda.domain_id = d.id <http://d.id>
> JOIN external_domain as ed ON deda.external_domain_id = ed.id
> <http://ed.id> WHERE d.name <http://d.name> = 'HERE MY VDOMAIN' AND
> deda.receive = 1 AND ed.name <http://ed.name> = '%d'
>
> d.name <http://d.name> => should be recipient domain.
> ed.name <http://ed.name> => is the sender domain, which works fine with
> %d, as I have another check before with a similar query.

OK, so %d gets substituted with domain name?


> The problem is, I want to use in a check_sender_access information about
> the recipient (for the joins in the query), which i think does not work.

Yeah, I don't see anything wrong with the query itself, but whether
it'll work in a check_sender_access rule is another question, which I
can't answer because I've never tried to do that.


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958