Jira (PDB-4628) Fields in group_by queries aren't properly quoted/escaped

10 views

Skip to first unread message

Nick Maludy (JIRA)

unread,

Jan 20, 2020, 9:05:37 AM1/20/20

to puppe...@googlegroups.com

Nick Maludy created an issue

PuppetDB /

PDB-4628

Fields in group_by queries aren't properly quoted/escaped

Issue Type:	Story
Assignee:	Unassigned
Created:	2020/01/20 6:04 AM
Priority:	Normal
Reporter:	Nick Maludy

I'm trying to write a PuppetDB query using "group by" semantics where i was trying to group by a fact value and think i found a bug where the "group by" fields aren't properly escaped / quoted.

AST:

    ['from', 'inventory',

     ['extract',

      [['function', 'count'], 'facts.wsus_target_group', 'certname'],

      ['=', 'facts.osfamily', 'windows'],

      ['group_by', 'facts.wsus_target_group', 'certname'],

],

PQL

"inventory[certname, facts.wsus_target_group] {facts.osfamily = 'windows' group by facts.wsus_target_group,certname }"

Trying to run these queries i get an error from the API:

<html>

<head>

<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>

<title>Error 500 Server Error</title>

</head>

<body><h2>HTTP ERROR 500</h2>

<p>Problem accessing /pdb/query/v4. Reason:

<pre>    Server Error</pre></p><h3>Caused by:</h3><pre>javax.servlet.ServletException: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table &quot;facts&quot;

  Position: 614

        at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:168)

        at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:753)

        at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:56)

        at com.puppetlabs.trapperkeeper.services.webserver.jetty9.utils.MDCRequestLogHandler.handle(MDCRequestLogHandler.java:36)

        at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:174)

        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)

        at org.eclipse.jetty.server.Server.handle(Server.java:505)

        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:370)

        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:267)

        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)

        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)

        at org.eclipse.jetty.io.ssl.SslConnection$DecryptedEndPoint.onFillable(SslConnection.java:427)

        at org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:321)

        at org.eclipse.jetty.io.ssl.SslConnection$2.succeeded(SslConnection.java:159)

        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)

        at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)

        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)

        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)

        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)

        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126)

        at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366)

        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:698)

        at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:804)

        at java.lang.Thread.run(Thread.java:748)

Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table &quot;facts&quot;

  Position: 614

        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)

        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)

        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)

        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)

        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)

        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows$fn__22073.invoke(jdbc.clj:297)

        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:814)

        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:741)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invokeStatic(jdbc.clj:289)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invoke(jdbc.clj:272)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invokeStatic(jdbc.clj:285)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invoke(jdbc.clj:272)

        at puppetlabs.puppetdb.query_eng$fn__30503$produce_streaming_body__30508$fn__30509$fn__30512$fn__30514$fn__30515.invoke(query_eng.clj:221)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn$fn__22266$fn__22267.invoke(jdbc.clj:514)

        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:771)

        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:741)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn$fn__22266.invoke(jdbc.clj:513)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249$fn__22250$fn__22251.invoke(jdbc.clj:485)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249$fn__22250.invoke(jdbc.clj:484)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249.invoke(jdbc.clj:475)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn.invokeStatic(jdbc.clj:511)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn.invoke(jdbc.clj:500)

        at puppetlabs.puppetdb.query_eng$fn__30503$produce_streaming_body__30508$fn__30509$fn__30512$fn__30514.invoke(query_eng.clj:220)

        at ring.util.io$piped_input_stream$fn__190.invoke(io.clj:28)

        at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)

        at clojure.lang.AFn.call(AFn.java:18)

        at java.util.concurrent.FutureTask.run(FutureTask.java:266)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

        ... 1 more

</pre>

<h3>Caused by:</h3><pre>org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table &quot;facts&quot;

  Position: 614

        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)

        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)

        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)

        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)

        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)

        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows$fn__22073.invoke(jdbc.clj:297)

        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:814)

        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:741)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invokeStatic(jdbc.clj:289)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invoke(jdbc.clj:272)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invokeStatic(jdbc.clj:285)

        at puppetlabs.puppetdb.jdbc$call_with_array_converted_query_rows.invoke(jdbc.clj:272)

        at puppetlabs.puppetdb.query_eng$fn__30503$produce_streaming_body__30508$fn__30509$fn__30512$fn__30514$fn__30515.invoke(query_eng.clj:221)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn$fn__22266$fn__22267.invoke(jdbc.clj:514)

        at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:771)

        at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:741)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn$fn__22266.invoke(jdbc.clj:513)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249$fn__22250$fn__22251.invoke(jdbc.clj:485)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249$fn__22250.invoke(jdbc.clj:484)

        at puppetlabs.puppetdb.jdbc$fn__22244$retry_sql_STAR___22249.invoke(jdbc.clj:475)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn.invokeStatic(jdbc.clj:511)

        at puppetlabs.puppetdb.jdbc$with_transacted_connection_fn.invoke(jdbc.clj:500)

        at puppetlabs.puppetdb.query_eng$fn__30503$produce_streaming_body__30508$fn__30509$fn__30512$fn__30514.invoke(query_eng.clj:220)

        at ring.util.io$piped_input_stream$fn__190.invoke(io.clj:28)

        at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)

        at clojure.lang.AFn.call(AFn.java:18)

        at java.util.concurrent.FutureTask.run(FutureTask.java:266)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

        at java.lang.Thread.run(Thread.java:748)

</pre></body>

</html>

Looking at the postgres logs i see the following error:

< 2020-01-20 08:57:40.569 EST > ERROR:  missing FROM-clause entry for table "facts" at character 614

< 2020-01-20 08:57:40.569 EST > STATEMENT:  WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT certnames.certname AS certname, (fs.stable||fs.volatile)->'wsus_target_group' AS "facts.wsus_target_group" FROM factsets fs LEFT JOIN environments ON fs.environment_id = environments.id LEFT JOIN producers ON fs.producer_id = producers.id LEFT JOIN certnames ON fs.certname = certnames.certname WHERE ((fs.stable||fs.volatile) @> $1 AND NOT ((certnames.certname) in (SELECT certname FROM  ( (SELECT inactive_nodes.certname AS certname FROM inactive_nodes) )  sub))) GROUP BY certnames.certname, facts.wsus_target_group

-------------------

I noticed that in the Postgres query that `facts` isn't actually the name of a table instead its the name of a field and so `facts.wsus_target_group` is parsed by Postgres thinking that `facts` is a table and we're trying to extract `wsus_target_group` field from that.

I was able to force inject some quotes in the AST query and got it to work:

    ['from', 'inventory',

     ['extract',

      [['function', 'count'], 'facts.wsus_target_group', 'certname'],

      ['=', 'facts.osfamily', 'windows'],

      ['group_by', '\"facts.wsus_target_group\"', 'certname'],

],

I'm not sure the exact right fix, but those extra quotes helped my query return without an error.

Add Comment

This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93)

Charlie Sharpsteen (Jira)

unread,

Sep 22, 2020, 5:13:03 PM9/22/20

to puppe...@googlegroups.com

Charlie Sharpsteen updated an issue

PuppetDB /

PDB-4628

Fields in group_by queries aren't properly quoted/escaped

Change By:	Charlie Sharpsteen
CS Priority:	Needs Priority
Team:	PuppetDB

Add Comment

This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935)

Charlie Sharpsteen (Jira)

unread,

Sep 22, 2020, 5:13:04 PM9/22/20

to puppe...@googlegroups.com

Charlie Sharpsteen updated an issue

PuppetDB /

PDB-4628

Fields in group_by queries aren't properly quoted/escaped

Change By:	Charlie Sharpsteen
Issue Type:	Story Bug

Add Comment

Jarret Lavallee (Jira)

unread,

Nov 5, 2020, 6:21:03 PM11/5/20

to puppe...@googlegroups.com

Jarret Lavallee updated an issue

PuppetDB /

PDB-4628

Fields in group_by queries aren't properly quoted/escaped

Change By:	Jarret Lavallee
CS Priority:	Needs Priority Reviewed

Add Comment

Zachary Kent (Jira)

unread,

Feb 17, 2021, 12:47:03 PM2/17/21

to puppe...@googlegroups.com

Zachary Kent updated an issue

PuppetDB /

PDB-4628

Fields in group_by queries aren't properly quoted/escaped

Change By:	Zachary Kent
Labels:	query-eng

Add Comment

Zachary Kent (Jira)

unread,

Feb 17, 2021, 12:49:02 PM2/17/21

to puppe...@googlegroups.com

Zachary Kent updated an issue

PuppetDB /

PDB-4628

Fields in group_by queries aren't properly quoted/escaped

Change By:	Zachary Kent
Labels:	query- eng engine-improvements

Add Comment

Austin Blatt (Jira)

unread,

Aug 23, 2021, 4:57:02 PM8/23/21

to puppe...@googlegroups.com

Austin Blatt updated an issue

PuppetDB /

PDB-4628

Fields in group_by queries aren't properly quoted/escaped

Change By:	Austin Blatt
Fix Version/s:	PDB 7.5.2
Fix Version/s:	PDB 6.18.2

Add Comment

This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97)

Reply all

Reply to author

Forward

0 new messages