Special syntax for where-statement?

[Stefan Steuer]

Hi Cornelius,

as you see I'm working with PI intense ;-)

Is there a special syntax for the where-statement in the SQL-resolver?

I tried some different formats without success.

e.g.

id = 3

users.id = 3

WHERE users.id = 3

when I click on the test-button I can see the same number of users on every time.

[Cornelius Kölbel]

Hi Stefan,

Am 05.05.2015 um 16:13 schrieb Stefan Steuer:

Hi Cornelius,

as you see I'm working with PI intense ;-)

that's fine!

Is there a special syntax for the where-statement in the SQL-resolver?

The where statement is not used in the testconnection
see https://github.com/privacyidea/privacyidea/blob/master/privacyidea/lib/resolvers/SQLIdResolver.py#L630

The testconnection was originally only intented to see, if the IP config worked and the credentials are right.
Especially with the LDAP connector, there were many problems in the past.

I tried some different formats without success.

e.g.

id = 3

users.id = 3

WHERE users.id = 3

The where statement is only honoured during operational mode.
(see https://github.com/privacyidea/privacyidea/blob/master/privacyidea/lib/resolvers/SQLIdResolver.py#L463)

You can set it like

id == 3
name like %stefan%
money > 5000
vacation < 10

No and or or working!

Kind regards
Cornelius

when I click on the test-button I can see the same number of users on every time.

--
You received this message because you are subscribed to the Google Groups "privacyidea" group.
To unsubscribe from this group and stop receiving emails from it, send an email to privacyidea...@googlegroups.com.
To post to this group, send email to priva...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/privacyidea/813147f9-29c6-40ff-b405-867c1cc9d188%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
Cornelius Kölbel
corneliu...@netknights.it
+49 151 2960 1417

NetKnights GmbH
http://www.netknights.it
Landgraf-Karl-Str. 19, 34131 Kassel, Germany
Tel: +49 561 3166797, Fax: +49 561 3166798

Amtsgericht Kassel, HRB 16405
Geschäftsführer: Cornelius Kölbel

[Stefan Steuer]

Hi Cornelius,

I tried it on several system but with no success.

Error log:

[2015-05-05 18:36:38,173][2776][140296524211968][ERROR][privacyidea.lib.user:487] ValueError('need more than 1 value to unpack',)

[2015-05-05 18:36:38,182][2776][140296524211968][ERROR][privacyidea.lib.user:488] Traceback (most recent call last):

  File "/opt/privacyidea/privacyidea-venv/local/lib/python2.7/site-packages/privacyidea/lib/user.py", line 477, in get_user_list

    ulist = y.getUserList(searchDict)

  File "/opt/privacyidea/privacyidea-venv/local/lib/python2.7/site-packages/privacyidea/lib/resolvers/SQLIdResolver.py", line 463, in getUserList

    (w_column, w_cond, w_value) = self.where.split()

ValueError: need more than 1 value to unpack

[Cornelius Kölbel]

Look at this nice comment ;-)

if self.where:
    # this might result in errors if the
    # administrator enters nonsense

    (w_column, w_cond, w_value) = self

.where.split()
    if w_cond.lower() == "like":
        conditions.append(getattr(self.TABLE, w_column).like(w_value))
    elif w_cond == "==":
        conditions.append(getattr(self.TABLE, w_column) == w_value)
    elif w_cond == ">":
        conditions.append(getattr(self.TABLE, w_column) > w_value)
    elif w_cond == "<":
        conditions.append(getattr(self.TABLE, w_column) < w_value)
filter_condition = and_(*conditions)

What did you enter as where statement?
I suppose you have no blank between the words:

    id [blank] == [blank] 3

Regards
Cornelius

--
You received this message because you are subscribed to the Google Groups "privacyidea" group.
To unsubscribe from this group and stop receiving emails from it, send an email to privacyidea...@googlegroups.com.
To post to this group, send email to priva...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/privacyidea/cdbf929b-a1d3-4b5b-9d5e-e5ff84f13277%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Stefan Steuer]

Hi Cornelius,

is it possible to separate the query strictly with the where-statement?

In the moment the where statement is to separate the user-liste in PI - but during the authentification PI is trying to find the user in the db.

[Cornelius Kölbel]

Hi Stefan,

you are right.

This should not be a big deal.
It is located in:
https://github.com/privacyidea/privacyidea/blob/master/privacyidea/lib/resolvers/SQLIdResolver.py

self.where is used in getUserList.

But it should also be used in getUsedId and getUserInfo.

I will create an issue for this.

Kind regards
Cornelius

> --
> You received this message because you are subscribed to the Google
> Groups "privacyidea" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to privacyidea...@googlegroups.com.
> To post to this group, send email to priva...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/privacyidea/1f12347b-35d2-431a-a96a-dd353f45ebc4%40googlegroups.com.

[Stefan Steuer]

Sounds good!

When this "issue" is resolved - it would be easier for an admin to choose multiple data-sources (AD, SQL, ...) to authentificate the users against one SQL (which contains all users of each sources) :)

[Stefan Steuer]

Hi Cornelius,

I'll hope you're well - Are there any news regarding this feature? :)