search

turbot / steampipe-plugin-ldap

Use SQL to instantly query users, groups, OUs and more from LDAP. Open source CLI. No DB required.
https://hub.steampipe.io/plugins/turbot/ldap
Apache License 2.0
9 stars 1 forks source link

Error: unsupported Unicode escape sequence (SQLSTATE 22P05)" when querying Active directory #3

Closed matteoredaelli closed 2 years ago

matteoredaelli commented 2 years ago

Hello I get the error: "Error: unsupported Unicode escape sequence (SQLSTATE 22P05)" when querying Active directory How can I solve it?

select a.mail 
from ldap_user a 
where dn in (
  select 
    jsonb_array_elements_text(attributes -> 'manager')
  from adgroup.ldap_user where sam_account_name='matteo');

The single queries work fine

select  jsonb_array_elements_text(attributes -> 'manager')
from
  adgroup.ldap_user where sam_account_name='matteo';
+------------------------------------------------------------------------------------------------------------------+
| jsonb_array_elements_text                                                                                        |
+------------------------------------------------------------------------------------------------------------------+
| CN=Pippo,DC=redaelli,DC=com |
+------------------------------------------------------------------------------------------------------------------+
 select a.mail 
from ldap_user a where dn  in ('CN=..');
+---------------------------+
| mail                      |
+---------------------------+
| pippo@example.com |
+---------------------------+
matteoredaelli commented 2 years ago

See also the thread on slack https://steampipe.slack.com/archives/C01UECB59A7/p1644008073746389

matteoredaelli commented 2 years ago

Hello I get the same error with

matteo@debian:~$ steampipe query
Welcome to Steampipe v0.13.3
For more information, type .help
> select sam_account_name
from
  ldap_group
where sam_account_name like 'Qliksense_%';
Error: unsupported Unicode escape sequence (SQLSTATE 22P05)

With sqlline & postgres jdbc driver I get

0: jdbc:postgresql://localhost:9193/steampipe> select sam_account_name
. . . . . . . . . . . . . . . . . . semicolon> from
. . . . . . . . . . . . . . . . . . semicolon>   ldap_group
. . . . . . . . . . . . . . . . . . semicolon> where sam_account_name like 'Qliksense_%'
. . . . . . . . . . . . . . . . . . semicolon> ;
Error: ERROR: unsupported Unicode escape sequence
  Detail: \u0000 cannot be converted to text.
  Where: JSON data, line 1: ...om"],"objectClass":["top","group"],"objectGUID":[... (state=22P05,code=0)

My database*.log is

******************************************************

        steampipe postgres fdw init

******************************************************
2022/03/30 19:40:56 [INFO] Log level WARN
2022-03-30 17:41:09.945 UTC [93175] ERROR:  unsupported Unicode escape sequence
2022-03-30 17:41:09.945 UTC [93175] DETAIL:  \u0000 cannot be converted to text.
2022-03-30 17:41:09.945 UTC [93175] CONTEXT:  JSON data, line 1: ...om"],"objectClass":["top","group"],"objectGUID":[...
2022-03-30 17:41:09.945 UTC [93175] STATEMENT:  select sam_account_name
    from
      ldap_group
    where sam_account_name like 'Qliksense_%';
2022-03-30 17:44:07.691 UTC [93175] ERROR:  unsupported Unicode escape sequence
2022-03-30 17:44:07.691 UTC [93175] DETAIL:  \u0000 cannot be converted to text.
2022-03-30 17:44:07.691 UTC [93175] CONTEXT:  JSON data, line 1: ...om"],"objectClass":["top","group"],"objectGUID":[...
2022-03-30 17:44:07.691 UTC [93175] STATEMENT:  select sam_account_name
    from
      ldap_group
    where sam_account_name like 'Qliksense_%';
2022-03-30 17:44:35.695 UTC [93175] LOG:  disconnection: session time: 0:04:03.204 user=steampipe database=steampipe host=127.0.0.1 port=47746
cbruno10 commented 2 years ago

@matteoredaelli Sorry for the long silence on this one. I'm not sure if a fix is required in this plugin and/or our Postgres FDW, but we do currently have a bug open in our FDW repo that may resolve this issue. I've commented in that issue and will follow-up further to see where the fix(es) are required. I'll echo any updates in that issue back in this one as well for tracking purposes.

cbruno10 commented 2 years ago

@matteoredaelli We released Steampipe v0.14.0 yesterday, which fixed some unicode escape sequence errors like the one you were seeing above. When you get a chance, can you please download the latest version of Steampipe and run the same queries? If you're still getting an error, feel free to re-open this issue and we'll have another look, thanks!

christianherweg0807 commented 2 years ago

I think the Problem is still there :

$ steampipe -v
steampipe version 0.14.6

Here an example with latest ldap plugin running against an Microsoft AD LDAP:

> select mail from ldap_user where department like 'MY-DE-PA-RT-MENT';
**Error: unsupported Unicode escape sequence (SQLSTATE 22P05)**
> select mail from ldap_user where department = 'MY-DE-PA-RT-MENT';
+-----------------------------+
| mail                        |
+-----------------------------+
| someone.somewhere@mail.de    |
cbruno10 commented 2 years ago

@christianherweg0807 Thanks for reporting there's still an issue, and sorry that it's still there! I'll re-open https://github.com/turbot/steampipe-postgres-fdw/issues/118 and add your comment in there, can you please follow-up in that issue with anymore information/questions, as that's the proper repo to fix it in I believe. Thanks!

cbruno10 commented 2 years ago

Accidentally re-opened and assigned the wrong person here, re-closing so we can continue discussion in https://github.com/turbot/steampipe-postgres-fdw/issues/118.